Hi, Hope all is well. Just a question if anyone has time. I have a piece of code i was using to check if a record existed in a table and seemed to be OK . I had thought of using it in another entry form , the ID on this new form having letters and numbers instead of just an integer. The code below works ok with an integer input and the field in the table as a number
When i tried changing the variable to Dim RPSno As String and the table field to text. I get errors.
if i enter E4567 i get RTE 2471 . The expression you entered as a query paramater produced this error E4567
and if i enter 9999 i get the error RTE 3464 Data type mismatch in criteria expression.
Confused as to why if i change to string i get errors from the Dlookup as i thought it wouldn't matter what the lookup was comparing as long as the table field matched the comparison?
Any explantion would be appreciated as always
Code:
If Len(Me.ResID.Value & vbNullString) > 0 Then
Dim RPSno As Long
RPSno = Nz(DLookup("[ResID]", "tblOp", "[ResID]= " & Me.ResID), 0)
If RPSno <> 0 Then
MsgBox (RPSno)
MsgBox ("number DOES exist in database")
Cancel = True
Else
MsgBox ("Number Does NOT exist in the Database")
Dim strSQL As String
strSQL = "INSERT INTO tblOp (Surname,FirstName,ResID) Values(text2,text4,ResID)"
DoCmd.RunSQL strSQL
Me.Text2.Value = ""
Me.Text4.Value = ""
Me.ResID.Value = ""
End If
End IfWhen i tried changing the variable to Dim RPSno As String and the table field to text. I get errors.
if i enter E4567 i get RTE 2471 . The expression you entered as a query paramater produced this error E4567
and if i enter 9999 i get the error RTE 3464 Data type mismatch in criteria expression.
Confused as to why if i change to string i get errors from the Dlookup as i thought it wouldn't matter what the lookup was comparing as long as the table field matched the comparison?
Any explantion would be appreciated as always
