Dlookup trouble

[RufussMcGee]

My form "frmNewContact" has a VBA code on open.

Dim varX As Variant
varX = DLookup("[ID]", "[tblCustomer List]", "[Bill-To Name] =" _
& Forms![frmNewContact]!txtMYCustomer)


In the table "tblCustomer List" ID is the primary key field and "Bill-to Name" is the text field.

This is the error I am getting...

Syntax error (missing operator) in query expression '[Bill-To Name]=Lane Supply

The company name is spelled right, I am at a lost of the error.
Thanks

 

[djj55]

You need the quotes around the variable (Forms![frmNewContact]!txtMYCustomer)

"[Bill-To Name] =""" _
& Forms![frmNewContact]!txtMYCustomer) & """

Not tested

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[RufussMcGee]

Sorry no go now it says...

Syntax error in string in query expression '[Bill-To Name]="Lane Supply'.

 

[djj55]

Try & Forms![frmNewContact]!txtMYCustomer) & chr(34)

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[RufussMcGee]

Again Nope I must have something written wrong.

 

[RufussMcGee]

ok this works somewhat the only problem is does not matter what company I pick the value is always 1. What I need to get is the ID (Primary key, autonumber) from said table.

txtLCD_ID.Value = DLookup("ID", "tblCustomerList", (Chr(34) & "[Bill-to Name] = " & "Forms!frmAlphabet!txtMYCustomer" & Chr(34)))

 

[MajP]

how about:

txtLCD_ID.Value = DLookup("ID", "tblCustomerList", "[Bill-to Name] = '" & Forms!frmAlphabet!txtMYCustomer & "'" )

 

[MajP]

Those are single quotes

='" & Forms!frmAlphabet!txtMYCustomer & "'

 

[TheAceMan1]

How are ya xhat . . .\

[blue]MajP[/blue] has hit on an area that can cause problems if some your names include a single quote (known as an apostrophe) as in [blue]O'Connell[/blue]. To correct this the code changes to:

[blue]txtLCD_ID = DLookup("ID", "tblCustomerList", "[Bill-to Name] = '" & Replace(Forms!frmAlphabet!txtMYCustomer,"[red][b]'[/b][/red]","[red][b]''[/b][/red]") & "'")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Actually I was not even considering that issue. The original recommendations were just incorrect.

This does not work because of the double quotes
"[Bill-To Name] =""" _
& Forms![frmNewContact]!txtMYCustomer) & """

And the next recommendation was wrong
Try & Forms![frmNewContact]!txtMYCustomer) & chr(34)
And the interpretation was wrong
(Chr(34) & "[Bill-to Name] = " & "Forms!frmAlphabet!txtMYCustomer" & Chr(34)))

 

[TheAceMan1]

MajP . . .

Understood!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]