SimpleDLookup - "So I thought"

[OldCityCat]

I just trying to do a simple lookup in my UsedbookingNumber table to check if a numbers has been used.
This code is runs after LostFocus of the BookingNoticeBookingNo field.

DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = _
" & Forms![frmAsiaBooking]![BookingNoticeBookingNo])

I have tried many variations and get errors like:
"Type Mismatch" ,
"The expression you entered as a query parameter produced this error: WLK533741"
Which happens to be first number in my used number table.
and Runtime error 3075. Syntax error (missing operator) "[UsedBookingNum] = "
I have verified all fields are data type "Text"

These are some tests from my Immediate window:
? DLookup("[UsedBookingNum]", "UsedBookingNumber")
WLK533741 - First number in used number table

?Forms![frmAsiaBooking]![BookingNoticeBookingNo]
WLK533748 - Number from active form

Thank you in advance for any and all suggestions

OldCityCat

[RuralGuy]

Try:

DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = _
'" & Forms![frmAsiaBooking]![BookingNoticeBookingNo] & "'")

Here's a link I always reference: http://www.mvps.org/access/general/gen0018.htm

[OldCityCat]

RuralGuy,
Thanks for your response,
This was one of my variations on the code, funny thing when I run it in the immediate window is it returns the booking number the on my open form.

? DLookup("[UsedBookingNum]", "UsedBookingNumber", "UsedBookingNum] = '" _ 
& Forms![frmAsiaBooking]![BookingNoticeBookingNo] & "'")
WLK533741

But when I run it from the form I get a "Run Time Error 13 Type mismatch".
I'm at a loss so, In case I'm missing something here my complete sub:

Private Sub BookingNoticeBookingNo_LostFocus()
 If DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" _ 
& Forms![frmAsiaBooking]!BookingNoticeBookingNo & "'") Then
        DoCmd.CancelEvent
MsgBox "This Number has already been used!", vbExclamation, "Error"
Me![cboBookingNoticeShipLine].SetFocus
    SendKeys "{TAB}", 0.25
        End If

End Sub

Thanks
OldCityCat

[RuralGuy]

It sounds like the field is not normalized so you are data sensitive. try:
If DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" _
& Nz(Forms![frmAsiaBooking]!BookingNoticeBookingNo, "") & "'") Then

[OldCityCat]

So far so good - I will continue my testing and let you know.
Thanks again

OldCityCat

[RuralGuy]

Glad to help. You may want to mark this thread as Solved and start a new one later.

[OldCityCat]

Well after further test I'm still getting a mismatch error,
This happens when adding a new record, and using a booking number that I know is in the UseBookingNumber table.
Obviously I should get "This Number has already been used!" message.
The code works correctly when adding a new booking number.
I'm not sure where to go from here.

Thanks
OldCityCat

[RuralGuy]

DLookup() returns a value and your code is not looking for it. Try:
[code]If Len(DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" & Nz(Forms![frmAsiaBooking]!BookingNoticeBookingNo, "") & "'")) > 0 Then

[OldCityCat]

What I don't understand is why the code works correctly in the immediate window.

[Code]
? DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" & _
Nz(Forms![frmAsiaBooking]!BookingNoticeBookingNo, "") & "'")
WLK3643601
? DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" & _
Nz(Forms![frmAsiaBooking]!BookingNoticeBookingNo, "") & "'")
WLK533741
? DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" & _
Nz(Forms![frmAsiaBooking]!BookingNoticeBookingNo, "") & "'")
WLK534729
[Code]
These are samples of the immediate window results, all three booking numbers are from
thee different records that were the current record on the form at the time I ran the code in the immediate window.

[Code]
? If DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" & _ Nz(Forms![frmAsiaBooking]!BookingNoticeBookingNo, "") & "'") Then
DoCmd.CancelEvent
MsgBox "This Number has already been used!", vbExclamation, "Error"
[Code]
When I ran this from the immediate window it returned my MsgBox text.

Why would running it from my form cause the Type Mismatch?

OldCityCat

[RuralGuy]

The "?" in the immediate window is displaying the returned value. Try the immediate window without the "?" and see what happens.

[OldCityCat]

I get
"Compile Error"
Expected:=

from
DLookup("[UsedBookingNum]", "UsedBookingNumber", "[UsedBookingNum] = '" _
& Nz(Forms![frmAsiaBooking]!BookingNoticeBookingNo, "") & "'")

[RuralGuy]

Exactly! Did my suggestion in Post #8 work for you?

[OldCityCat]

Yes
Sorry,
I should have tried it before sending my last posts.

Please explain how this DLookup code works.

Thanks

[RuralGuy]

DLookup() returns either the value you are looking for or a Null if not located. To test for a return you need code that can handle either and Len() fills the bill nicely. Does that explain it for you?

[OldCityCat]

Sorry but I not getting it!
In my testing (see below) DLookup should have found a match ,but still returned an error?
where does Null come into play?
So LEN looks for any string grater then 0?

type mismatch error,
when adding a new record, and using a booking number that I know is in the UsedBookingNumber table.