Cannot get a DLookUp to work

[fedebrin]

I have a form that updates some records by pulling fata from 2 other tables.

The first event worked with no issue pulling the brand from the "Items" table (It looks for the "ItemID" to match the records:

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


Now, to pull the "Price" from the "Services" table it is giving me trouble. This one references the "ServiceNameID" to look up the price.




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

Any help is welcome!

 

[Joe4]

What kind of trouble is it giving you exactly?
If error messages, what are they?
If unexpected results, what are they?

Also, it would be helpful to know the structure and field names of your "Services" table.

 

[fedebrin]

Hello,

Getting a syntax error (missing operator) in query expression '[ServiceNameID] =

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.

thanks!

 

[Joe4]

Make sure that there are no extra spaces at the beginning of end of your field names.
If there are not any, please list the Names and Data Types of all the Fields in your Services table for us to see.

 

[fedebrin]

Thanks, there are no spaces, all fields have the same name and format

 

[xenou]

Is there always only one price for a servicenameID? What is the datatype of ServiceNameID? Can you give an example of a specific ServiceNameID and the values in the table [Services]?

Also beware that specific syntax error messages are not always accurate. The error could be somewhere else and this is just where the compiler finally gets stuck. Comment out the three lines for the sub ServiceNameID_AfterUpdate() and see if it compiles without it.

 

[Joe4]

all fields have the same name and format

It is impossible for multiple fields to have the same name.

I will ask a 3rd time, please list the actual field names and data format for this table.
Without that knowledge, short of having access to the database, I don't know what more I can do for you.