Cannot get a DLookUp to work

[fedebrin]

The objective is to populate the "OrderDetails" form / table with data from the "Services" & "Items" table.

I am able get data from the "Items" table with the below event:

Private Sub ItemID_AfterUpdate()
Me![Brand] = DLookup("Brand", "Items", "[ItemID] = " & Nz(Me.ItemID, 0))
End Sub

I can get the "Order Details" populated with the "Brand" with no issue.


When I try to obtain the price by entering the "ServiceNameID", I get the syntax error.

This is the event:

Private Sub ServiceNameID_AfterUpdate()
Me![Price] = DLookup("Price", "Services", "[ServiceNameID] = " & Nz(Me.ServiceNameID, 0))
End Sub

The format of the fields are all the same so not sure why it is not working.

See relationship file for the structure of the DataBase

thanks!

 

[pbaldy]

I'd use this if appropriate:

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

Is ServiceNameID a numeric field, or text?

 

[fedebrin]

Hi Paul,

It is Short Text.

 

[pbaldy]

Then you need delimiters:

http://theaccessweb.com/general/gen0018.htm

But I'd still use the other method, as it involves fewer calls to the data.

 

[fedebrin]

Thanks, Paul, yes I see your point... basically I do not need to populate all the other fields on to the "OrderDetails"

 

[pbaldy]

I would probably populate price, since it changes over time and you need to save the price each transaction used. Brand and color don't sound like things that I'd save in the details table, since presumably they don't change.

 

[fedebrin]

I still cannot get the delimeter right.

WOuld it be something like this?

Private Sub ServiceNameID_AfterUpdate()
Me![Price] = DLookup("Price", "Services", "[ServiceNameID] = 'Me![ServiceNameID]'")
End Sub

 

[pbaldy]

No, more like:

Me![Price] = DLookup("Price", "Services", "[ServiceNameID] = '" & Me![ServiceNameID] & "'")

 

[pbaldy]

And if it wasn't clear, the link included a method to populate price from the item dropdown, which would avoid the second trip to the data the DLookup() is making. Not a major issue, but it can help speed up apps.