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

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

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

Edited 7 Years Ago by vb5prgrmr: n/a

Comments
great help indeed..thanx
This question has already been answered. Start a new discussion instead.