Trouble with my Lookup Code

[Guest]

Hi Everyone.

I’m adding a cost lookup to my db, based on the example in the Tradewinds
database. I am trying to add a “CostPerFoot†value to the form
automatically, based on the “MaterialType†entered by the user. Both of
these fields are maintained in the “MaterialCostTableâ€. Here is what my
‘after update’
looks like on the “Material Type†field of the entry form:


Private Sub MaterialType_AfterUpdate()
On Error GoTo Err_MaterialType_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "MaterialType = " & Me!MaterialType

' Look up product's unit price and assign it to CostPerFoot control.
Me!CostPerFoot = DLookup("CostPerFoot", "MaterialCostTable", strFilter)

Exit_MaterialType_AfterUpdate:
Exit Sub

Err_MaterialType_AfterUpdate:
MsgBox Err.Description
Resume Exit_MaterialType_AfterUpdate

End Sub

When the user enters a material type (say 022-4.00.065), they get the error
“Syntax error in number in query expression ‘MaterialType = 022-4.00.065’.

What am I doing wrong?

Thanks in advance for any assistance.

Best Regards,
Karen

 

['69 Camaro]

Hi, Karen.

When the user enters a material type (say 022-4.00.065), they get the
error
"Syntax error in number in query expression 'MaterialType = 022-4.00.065'.

What am I doing wrong?

You are treating 022-4.00.065 as if it were a number, instead of a string
literal. It's not delimited by single quotes in your code, as strings must
be. Try:

strFilter = "MaterialType = '" & Me!MaterialType.Value & "'"

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Yeah! That worked perfectly. Thank you so much Gunny!