DLookup + Runtime Error 2001 "You cancelled Previous Operation

[swk003]

I am currently getting Runtime Error 2001 "You cancelled Previous Operation, when trying to run below DLookup(). I have checked all table field speelings etc. [AbbotBoxNumber] is Text datatype and this I believe is where I am getting the problem as have tested the procedure with number datatypes and it works fine. Can somebody point me the right way as to the correct syntax?
thanks SWK003

Private Sub txt_AbbotBoxNumber_BeforeUpdate(Cancel As Integer)

Dim FoundDuplicateABN As String
Dim Criteria3 As String
Dim Msg8 As String, Style8 As Integer, Title8 As String
Dim Msg9 As String, Style9 As Integer, Title9 As String
Static subCalls3 As Integer


Criteria3 = "[AbbotBoxNumber] =" & Me!txt_AbbotBoxNumber
FoundDuplicateABN = DLookup("[AbbotBoxNumber]", "tbl_Validation_PTB_data", Criteria3)'this is where i get the error message
If Not IsNull(FoundDuplicateABN) Then
subCalls3 = subCalls3 + 1
Msg8 = "Duplicate Abbot Box Number'" & Me!txt_AbbotBoxNumber & "'"
Style8 = vbInformation + vbOKOnly
Title8 = "Abbot Box Number '" & Me!txt_AbbotBoxNumber & "' Already in system! . . ."
MsgBox Msg8, Style8, Title8


If subCalls3 >= 2 Then
Msg9 = "Duplicate Abbot Box Number '" & Me!txt_AbbotBoxNumber & "'"
Style9 = vbInformation + vbOKOnly
Title9 = "Duplicate Abbot Box Number '" & Me!txt_AbbotBoxNumber & "' added to Duplicate AbbotBoxNumberErrorlog"
MsgBox Msg9, Style9, Title9

CurrentDb.Execute "INSERT INTO tbl_Errorlog_DuplicateAbbotBoxNumber " & "(AbbotBoxNumber) VALUES (" & Me.txt_AbbotBoxNumber & ");"

Cancel = True
Me!txt_AbbotBoxNumber.Undo


subCalls3 = 0
Else
Cancel = True
'instead of setting focus (Me.txtDateVisible.SetFocus) we use below argument
Me!txt_AbbotBoxNumber.Undo
End If
Else
subCalls3 = 0


End If



End Sub

 

[PHV]

Replace this:
Criteria3 = "[AbbotBoxNumber] =" & Me!txt_AbbotBoxNumber
By this:
Criteria3 = "[AbbotBoxNumber][tt]='"[/tt] & Me!txt_AbbotBoxNumber & [tt]"'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swk003]

Hi PH

This now picks up the the FoundDuplicateABN if Me!txt_AbbotBoxNumber matches a record in the table, thanks. However if I insert an BoxNumber into Me!txt_AbbotBoxNumber that does not match a record in [AbbotBoxNumber], then i get a runtime error 94 - invalid use of null. The idea is to loop through if the boxnumber has not already been used!! Can you help? Again this works fine when I have used it for number datatypes.
Thanks SWK003

 

[PHV]

Only a variant may hold Null value.
Either replace this:
Dim FoundDuplicateABN As String
By this:
Dim FoundDuplicateABN As Variant

Or this:
FoundDuplicateABN = DLookup("[AbbotBoxNumber]", "tbl_Validation_PTB_data", Criteria3)'this is where i get the error message
If Not IsNull(FoundDuplicateABN) Then
By this:
If Not IsNull(DLookup("[AbbotBoxNumber]", "tbl_Validation_PTB_data", Criteria3)) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swk003]

Thanks PH you are a star. Both options work fine.

By the way txt_AbbotBoxNumber does not clear. Here's the code sample:

Cancel = True
Me!txt_AbbotBoxNumber.Undo
subCalls3 = 0

Is there a problem with my syntax or should I be tweaking the control properties??

Thanks again

SWK003