Help with DLookup

J

Janet

I have a form with a textbox called "txtLoan Number". In the After Update
event for this textbox, I want it to look in a table and see if that loan
number already exists. The table I want it to look into is "Import Table"
and the field to look up in that table is "Account_Number". If the loan
number already exists in the table, I want a msg box to say so. I am
wondering why the following code does not work.

Private Sub txtLoan_Number_AfterUpdate()
If DLookup("[txtLoan Number]", "[Import Table]", "[Account_Number]=" &
[txtLoan Number]) Then
MsgBox "Already Exists"

Thank you for your help.
End If
 
O

Ofer Cohen

Use the before update event instead, so the record wont be saved

Try this

If DCount("*", "[Import Table]", "[Account_Number]=" & [txtLoan
Number])>0 Then
MsgBox "Already Exists"
Cancel = True ' to avoid exiting the field
End If
====================
If the LoanNumber is a text field try

If DCount("*", "[Import Table]", "[Account_Number]='" & [txtLoan Number] &
"'")>0
==========================
To use the DlookUp, it should be

If Not IsNull(DLookup("[txtLoan Number]", "[Import Table]",
"[Account_Number]=" & [txtLoan Number])) Then
 
J

Janet

Thank you very much. It works.

Ofer Cohen said:
Use the before update event instead, so the record wont be saved

Try this

If DCount("*", "[Import Table]", "[Account_Number]=" & [txtLoan
Number])>0 Then
MsgBox "Already Exists"
Cancel = True ' to avoid exiting the field
End If
====================
If the LoanNumber is a text field try

If DCount("*", "[Import Table]", "[Account_Number]='" & [txtLoan Number] &
"'")>0
==========================
To use the DlookUp, it should be

If Not IsNull(DLookup("[txtLoan Number]", "[Import Table]",
"[Account_Number]=" & [txtLoan Number])) Then

--
Good Luck
BS"D


Janet said:
I have a form with a textbox called "txtLoan Number". In the After Update
event for this textbox, I want it to look in a table and see if that loan
number already exists. The table I want it to look into is "Import Table"
and the field to look up in that table is "Account_Number". If the loan
number already exists in the table, I want a msg box to say so. I am
wondering why the following code does not work.

Private Sub txtLoan_Number_AfterUpdate()
If DLookup("[txtLoan Number]", "[Import Table]", "[Account_Number]=" &
[txtLoan Number]) Then
MsgBox "Already Exists"

Thank you for your help.
End If
Click to expand...
Click to expand...
 
Top