No duplicate data in a form field

[Gmoz]

I have a database for tracking jobs, They are tracked by a unique number (350 tag #). In the form for logging in the job I am trying to get a message to come up if the 350 tag # that is entered into the field is a duplicate. I would like this to hapen when the user tries to tab out of that field. Any help is appreciated. Thanks

 

[BillNorton]

I think you'll find your answer here: http://www.access-programmers.co.uk/ubb/Forum1/HTML/003799.html

Bill Norton
Austin, TX

 

[Gmoz]

I have tried some code but cant get it to work, I am sure I just have syntax errors.My Table Is Main Data, and the primarykey is 350. Here is the code I used.

Private Sub Ctl350_AfterUpdate()
afterupdate of primarykey
Dim db As database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.openrecordset("All Data", dbopendynaset)
rst.findfirst "[350]='" & Me!primarykey & "'"
If rst.nomatch = False Then
MsgBox "Primary Key already exists!"
Me!primarykey.SetFocus
End If
rst.Close
End Sub

I get a compile error on the very first line
Please help. Thanks

 

[charityg]

If your primaryKey field is a numeric datatype then use:

rst.findfirst "[350]=" & Me!primarykey

and remove he line
afterupdate of primarykey
this was refering to the afterupdate event of the primarykey field, not actual code.

Sorry for the mistake
hope it works

 

[Gmoz]

This is what I have now,I do not get any errors but it still does not work. I guess I will try to go at it another way. Thanks for your help.
Frank


Private Sub Ctl350_AfterUpdate()
Dim db As database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.openrecordset("All Data", dbopendynaset)
rst.findfirst "[350]=" & Me!primarykey
If rst.nomatch = False Then
MsgBox "Primary Key already exists!"
Me!primarykey.SetFocus
End If
rst.Close
End Sub