Private Sub txtStationRunNumber_BeforeUpdate(Cancel As Integer)
Dim rst As DAO.Recordset
Dim strStationRunNumber As String
Dim strCriteria As String
Dim lngBookmark As Long

I am using the following code to check if a value has already been entered. If so, I want to clear only that field. However, I get the error mismatch message and two fields get cleared out. All help is appreciated.

If Nz(Me.StationRunNumber, "") <> "" Then
strStationRunNumber = Me.StationRunNumber

Set rst = Me.RecordsetClone

strCriteria = "[StationRunNumber]='" & strStationRunNumber & "'"
lngBookmark = Nz(DLookup("StationRunNumber", "tblIncidents", strCriteria), 0)
If lngBookmark > 0 Then
Me.Undo
MsgBox "The Station Run Number of " & strStationRunNumber & " already exists in the database." & vbCrLf & vbCrLf & _
"Please re-enter the Station Run Number", vbInformation, "Duplicate Station Run Number " & strStationRunNumber
End If
rst.Close
Set rst = Nothing
End If
End Sub

I should also mention that the StationRunNumber is a numeric field.

If StationRunNumber is a numeric then it should not be enclosed in single quotes here:

strCriteria = "[StationRunNumber]='" & strStationRunNumber & "'"

Just so I understand, it should be like this?

strCriteria = "['StationRunNumber']='" & strStationRunNumber & "'"

Is that the only place I need to change it?

THanks.

No ... I said it should NOT be enclosed ...

strCriteria = "[StationRunNumber]=" & strStationRunNumber

Thank you for your help.

NP. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)