checking if record exist.. why won't this work?

[tmar89]

Why would the following code say data type mismatch in criteria expression?

Private Sub StudentID_AfterUpdate()
On Error GoTo Err_Handler


If Not IsNull(DLookup("[StudentID]", "tblStudent", "[StudentID]= " & Me!StudentID)) Then
MsgBox "Student Record already exists. Please go to Edit Student Info", vbOKOnly
End If

Err_Handler:
MsgBox Err.Description

End Sub

 

[dcx693]

Is StudentID a numeric field in your data table, or is it text?

 

[tmar89]

text, and i tried Str(Me!StudentID) also

 

[dcx693]

Aha. That's the whole problem. Try substituting this:
If Not IsNull(DLookup("[StudentID]", "tblStudent", "[StudentID]= '" & Me!StudentID & "'")) Then

When you do a search on text, you must delimit it using quotation marks. When you've already got qutotation marks around the field names and you need to specify a " character, you can just use a single ' character like I have above.

 

[tmar89]

sweet! I did a search before and found a few examples but I just didnt understand why I needed the " ' " with the string. didnt make sense but it works now. I must have had the syntax wrong before. Thanks!!!

 

[Rich]

Why not use a combo box?

 

[tmar89]

this is for adding new records. Ideally, I dont ever want the error to occur but there may be times when someone enters a duplicate ID. This just makes sense. How would I use a combo box?