DLookup with If Else and error messages (1 Viewer)

Looking for help with DLookup function VBA using If Else conditions. I have a form for adding new carriers to an existing carrier table. Because I know these users :banghead:, I would like for it to:

1. If the ID No. field is left blank - show an error message in an unbound text box.
2. If the ID entered in the ID No. Field already exists in the table - display an error message in an unbound text box and display the the carrier ID and Name associated with it.

I have it set on the got focus of the field immediately after the ID No. Field. I've searched and search and tried a number of ways, i know it's probably a syntax issue. Any help is much appreciated.

Here is what I have so far:

If IsNull(Me.AddCarrierNo) Then
Me.ErrMesgBoxTxt = "Please enter a Carrier/SI No."

Else

If DLookup("[CarrierNo]", "ExamCarrier", "[CarrierNo] = " & Me.AddCarrierNo) = True Then

Me.ErrMesgBoxTxt = "Carrier/SI Number already exists. Please verify Carrier information."
Me.ErrMesgBoxTxt.Requery
Me.ErrMesgBoxID = DLookup("[CarrierNo]", "[ExamCarrier]", "[CarrierNo] = " & Me.AddCarrierNo)
Me.ErrMesgBoxID.Requery
Me.ErrMsgBoxName = DLookup("[CarrierName]", "[ExamCarrier]", "[CarrierNo] = " & Me.AddCarrierNo)
Me.ErrMsgBoxName.Requery


End If
End If

Hi. Not sure if you really need an If/Then statement for this. You should be able to display everything in one Textbox. For example:
(untested)
Hope it helps...

Hey theDBguy, thank you for your help again. It appears when I try to enter information for a new carrier (one not currently in the table) a new Carrier ID No. is giving me the "Please enter Carrier No" error. I wanted that error to only show if the user left the filed blank.

Tiger6115 said:

Hey theDBguy, thank you for your help again. It appears when I try to enter information for a new carrier (one not currently in the table) a new Carrier ID No. is giving me the "Please enter Carrier No" error. I wanted that error to only show if the user left the filed blank.

Click to expand...

Hi. For that case, you should be able to add an IIf() statement to check if it's blank or not first.

I'm sorry, I can't wrap my brain around where the IIF statement would go. I've tried adding it around the code you provided me, i've tried it around the Nz(Me.AddCarrierNo), 0) and I get errors either way.

Tiger6115 said:

I'm sorry, I can't wrap my brain around where the IIF statement would go. I've tried adding it around the code you provided me, i've tried it around the Nz(Me.AddCarrierNo), 0) and I get errors either way.

Click to expand...

Let's see. Try this and let us know what happens.

=Nz(DLookup("'Carrier already exists: ' & [CarrierName]","ExamCarrier","[CarrierNo]=" & Nz(Me.AddCarrierNo,0)),IIf(IsNull([CarrierNo]),"Please enter a Carrier No",""))

That worked perfectly. As always your help/guidance is so very much appreciated. Thank you!!

Tiger6115 said:

That worked perfectly. As always your help/guidance is so very much appreciated. Thank you!!

Click to expand...

Hi. You're welcome. Glad to hear it worked for you. Good luck with your project.