Error checking for an existing record

[Israelsimba]

i am trying to make sure that one record is not entered twice into the same database but the code i am using is giving me an error

Set ri = deSub.conn.Execute("SELECT* FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")
If ri.EOF = True Then

ri.AddNew
ri.Fields("firstname") = txtfname.Text
ri.Fields("surname") = txtsurname.Text
ri.Fields("numberofcopies") = x * Y
ri.Fields("copiesout") = txtcopies.Text
ri.Fields("copiesremaining") = x * Y
ri.Update 
Else......

the error i am getting is

current recordset does not support updating. This might be a limitation of the provider or the selected locktype lock type

but i am using adlockoptimistic as shown below which suports updating...

With ri
.ActiveConnection = deSub.conn
.LockType = adLockOptimistic
.Source = "select * from individualsubs"
.Open
End With

[Israelsimba]

since i found roblems with adding a new record after checking if it was existant i then tried a another method but its giving me an error. the code is

deSub.conn.Execute ("INSERT INTO individualsubs VALUES (clientnumber = " & txtcopies.Text & ",  surname = '" & txtsurname.Text & "', firstname = '" & txtfname.Text & "', nowdate = '" & DTPicker2.Value & "', numberofcopies = " & x * Y & ", copiesout = " & txtcopies.Text & ", copiesremaining = " & x * Y & ")")

one or more reqired fields is missing in the requested ordinal
what could be wrong with my code here
thatnx

[vb5prgrmr]

Okay, in your first post you set the lock type with the second code snippet but I don't see it in the first code snippet, which means if you have not explicitly set it, it defaults back to its default settings, which might be your problem.

Now, in your second post, you have formatted the insert statement incorrectly. Presently it looks more like an update statement but that is neither here nor there or the answer to your problem. So, a properly formatted insert statement looks like this...

strSQL = "INSERT INTO tablename(Numberfieldname1, Stringfieldname2) VALUES(" & VariableContainingNumberValueForFieldName1 & ",'" & VariableContainingStringValueForFieldName2 & "')"

Good Luck

Edit: to see correctly toggle to plain text