Omit the single quotes delimiter characters. Number data types don't need
them, only text. Test for Not IsNull to find if a match already exists:
Dim strCriteria As String
strCriteria = "StuID = " & Me.StuID
If Not IsNull(DLookup("StuID", "tblStudents", strCriteria)) Then
<and so on>
Be sure to index the column uniquely in the table design, and in the above
code cancel the update operation with:
Cancel = True
if the ID already exists.
Note that if the application is in a multi-user environment the above code
will fail if two users are adding a record simultaneously and enter the same
ID number. However, the index violation will cause a data error when the
user attempts to save the record. This can be handled in the form's Error
event procedure.
For ID numbers a text data type is generally better, in which case you would
need the quotes delimiters. ID numbers are not really numbers in the true
sense, but often some form of encoding system, and sometimes structured, as
are Zip codes and ISBN numbers for instance. Unless the 'number' has some
ordinal or cardinal significance, I'd suggest changing the data type to text.
Ken Sheridan
Stafford, England
Ripper said:
I am attempting to use this code to DLookup a field before it is updated.
However, I cannot seem to get my syntax correct around the "[StuID]='" &
Me.StuID &'" area. StuID is set as a number field in tblStudents.
If ((DLookup("[StuID]", "[tblStudents]", "[StuID] ='" & Me.StuID & '")))
Then
MsgBox "This Student ID number is already used. Please Check ID or look
in History From.", vbOKOnly, "Student ID Already Exists"
End If