ERROR CHECKING

[bdavey]

I am trying to see if a customer ID is already in the table before adding a new one...

This is what I have so far...
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Customer Data&quot
If Me![CustAcctID] = rst![CustAcctID] Then
MsgBox "This Customer already exists in the database", vbCritical
Else
rst.AddNew
rst![CustAcctID] = Me![CustAcctID]
rst.Update

Any suggestions on what I am doing wrong?

Thank you!

 

[tomschafer]

The record set will only return one row at a time, so you must scroll through it OR use the seek function of a recordset. Even easier I would use the DCount domain function. i.e.

If DCount("*", "Customer Data", "CustAcctID=" & Me![CustAcctID]) > 0 Then
MsgBox "This Customer already exists in the database", vbCritical
Else:
CurrentDb.Execute ("insert into Customer Data select " & Me![CustAcctID])
End If

You may have to check some of the syntax a bit, but hopefully you get the gist of it. Good Luck!

 

[jazzz]

I would handle it a little different, I just answered a similiar question concerning a combo box just a week or so ago. Run some SQL Before update on the object to check if the number is in there and decide what to do based on the results. If you search by my name you should find my code.