Problem with special characters in DLOOKUP expression

[valhallaone]

Greetings,
I have an asset database I am designing to manage our computer inventory and assets.

I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...

My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -

Run-time error '3075':
Syntax error in date in query expression 'productID=EN371UA#ABA'.

My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.

Any ideas on how to avoid this?

Here is my expression -

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo])
End Sub

Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.

Any help would be greatly appreciated.

-Jim
Seattle, WA

 

[pbaldy]

Try the text syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[valhallaone]

Thanks for the quick reply. By text syntax do you mean the string syntax?

I am a little new to VB scripting for Access. Thank you for your patience.

Jim

 

[pbaldy]

Sorry, yes.

 

[valhallaone]

I updated my code using the following example -

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

Here is my code -

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "ProductList", "ProductID= 'ProductIDCombo'")
End Sub

I am no longer getting an error, but my Make field is not populating. Does the table have to set with certain properties?

Thank you again!

Jim

 

[pbaldy]

Hard to say without seeing it, but is this appropriate to what you're trying to do?

http://www.baldyweb.com/Autofill.htm

Also, is any code running? The db needs to be in a trusted location or you have to have explicitly enabled it.

 

[TJPoorman]

I updated my code using the following example -

DLookup("FieldName" , "TableName" , "Criteria= 'string'")

Here is my code -

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "ProductList", "ProductID= 'ProductIDCombo'")
End Sub

I am no longer getting an error, but my Make field is not populating. Does the table have to set with certain properties?

Thank you again!

Jim

Try the following:

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "ProductList", "ProductID= '" & me.ProductIDCombo & "'")
End Sub

 

[pbaldy]

Try the following:

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "ProductList", "ProductID= '" & me.ProductIDCombo & "'")
End Sub

That was certainly a "duh" moment on my part.

 

[TJPoorman]

That was certainly a "duh" moment on my part.

Could be worse.... could be 3000 lines of code looking for the elusive comma

 

[valhallaone]

The second option where the autofill information is saved to my table is precisely what I'm trying to do.

I setup the View Source my Combo box

SELECT [ProductID], [Make], [Model], [AssetType] FROM ProductList;

Then I setup the After Update event on my ProductIDCombo box to populate the Make and Model fields.

Private Sub ProductIDCombo_AfterUpdate()
Me.Make = Me.ProductIDCombo.Column(2)
Me.Model = Me.ProductIDCombo.Column(3)
End Sub

After I select the Product ID in the drop down box, the make and model field aren't populating still.

 

[pbaldy]

Make sure the column count of the combo is 4, and note the Column property is zero based, so you'd want 1 & 2.

 

[TJPoorman]

The second option where the autofill information is saved to my table is precisely what I'm trying to do.

I setup the View Source my Combo box

SELECT [ProductID], [Make], [Model], [AssetType] FROM ProductList;

Then I setup the After Update event on my ProductIDCombo box to populate the Make and Model fields.

Private Sub ProductIDCombo_AfterUpdate()
Me.Make = Me.ProductIDCombo.Column(2)
Me.Model = Me.ProductIDCombo.Column(3)
End Sub

After I select the Product ID in the drop down box, the make and model field aren't populating still.

As part of normalizing your database to reduce redundency, if you do not need to store the values for make and model, but just view them, you could just set the control source of the text boxes to show those values.
ex.:
make control source = "=Me.ProductIDCombo.Column(2)"
model control source = "=Me.ProductIDCombo.Column(3)"

 

[valhallaone]

Thank you TJ! That did it!

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "ProductList", "ProductID= '" & me.ProductIDCombo & "'")
Model = DLookup("Model", "ProductList", "ProductID= '" & me.ProductIDCombo & "'")
AssetType = DLookup("AssetType", "ProductList", "ProductID= '" & me.ProductIDCombo & "'")
End Sub

Big thanks to PBaldy as well. I really appreciate it fellas.

Jim