Dlookup Criteria - Syntax Error (missing operator)

[BillieBurgess]

Hello,
I found 10 or 8 other posts but could still not figure out what I am doing wrong. So if someone would be so kind as to look at my criteria and help me figure out what is missing I would sincerely appreciate it.

I have Access 2002. I am the writer of this programs so all errors are mine.
I need to auto populate a serial number into a bound control in a form. I was using a Combo box with the query such that only the correct s/n comes up. But then you have to select it and that got old fast. So by searching this site I came across Lookup. It sounds perfect, but it just will not work.
Okay there is one table and two Criteria

My Code is as follows:

Private Sub Serial_number_GotFocus()
On Error GoTo Err_Serial_number_GotFocus

Dim txt As TextBox
Set txt = Forms![Entry Trip DM]![Subform Trip Reimbursements].Form![Serial number]
txtvalue = DLookup("[Serial#]", "[Dish Defective unit]", "[Dealer ID]='" & [Forms]![Entry Trip DM]![Dealer ID] & " AND [Dish RA #]='" & [Forms]![Entry Trip DM]![Subform Trip Reimbursements].[Form]![RA #].[Value] & "'&quot

Err_Serial_number_GotFocus:
MsgBox Err.Description
Exit Sub

End Sub

Thank you very much for your Help

 

[tatum]

I dont know if you just cut and paste this code or not but for

"[Dealer ID]='" & [Forms]![Entry Trip DM]![Dealer ID] & "

try putting another single quote after the double quote to enclose the string.

good luck

Earl

 

[BillieBurgess]

It goes into Debug - syntax error - and the entire line is in red font. I know it has to be a misplaced or missing quotation mark or apostrophe but I cannot figure out where.

 

[JaneC]

Have you tried doing as Tatum suggested ? i.e. additional single quote before the AND [Dish RA ~] ??

txtvalue = DLookup("[Serial#]", "[Dish Defective unit]", "[Dealer ID]='" & [Forms]![Entry Trip DM]![Dealer ID] & "' AND [Dish RA #]='" & [Forms]![Entry Trip DM]![Subform Trip Reimbursements].[Form]![RA #].[Value] & "'&quot

 

[BillieBurgess]

I tried the quotation mark but looking at your example is that an extra quotation or apostrophe?

 

[BillieBurgess]

I tried to copy and part your example but it went into debug
Compile error: syntax Error

 

[BillieBurgess]

Okay,
I copied and pasted wrong - really sorry
So now, I do not go onto debug I just get an empty dialog box that waits for me to click okay. I know that this ra # is supposed to pull up a serial number, because I have checked.
I cannot figure out what I am doing wrong.

The Code Now looks like this:

Private Sub Serial_number_GotFocus()
On Error GoTo Err_Serial_number_GotFocus

Dim txt As TextBox
Set txt = Forms![Entry Trip DM]![Subform Trip Reimbursements].Form![Serial number]
txtvalue = DLookup("[Serial#]", "[Dish Defective unit]", "[Dealer ID]='" _
& [Forms]![Entry Trip DM]![Dealer ID] & "' AND [Dish RA #]='" _
& [Forms]![Entry Trip DM]![Subform Trip Reimbursements].[Form]![RA #].[Value] & "'&quot

Err_Serial_number_GotFocus:
MsgBox Err.Description
Exit Sub

End Sub

 

[BillieBurgess]

Found my error it was stupid.
should have been txt.value = dlookup .....
And I had to take out the on error event

Thanks to everyone sorry about the time consumed

 

[JaneC]

Glad you got it sorted !