Dlookup not working

[Djblois]

I put in this dlookup as the Data Source for a TB in a form.

=Dlookup("SHIPTO","WMS_PKO_HD","REF =" & [cobOrderNum])

The syntax looks correct but it keeps returning #Error. Also I am sure that the Order number is in there also.

 

[boblarson]

=Dlookup("[SHIPTO]","WMS_PKO_HD","[REF]=" & Me.cboOrderNum)

is your combo really named cobOrderNum or is it cboOrderNum?

Also, you don't necessarily need the brackets where I put them but it helps to let you know you are talking about a field name.

 

[Djblois]

When I exit the field it keeps putting the brackets around the me and my combobox's name. Is that an issue? This is what I have now:

=Dlookup("SHIPTO","WMS_PKO_HD","REF=" & [Me].[cboOrderNum])

and it still isn't working. I tried without the square brackets around the fields.

Also would an SQL where statement work better?

 

[boblarson]

Actually keep the square brackets around the fields but is your REF field an actual number or is it text in the table?

 

[boblarson]

And I forgot you can't really use ME so remove that part. And perhaps use the full qualified form name:

[Forms]![YourFormName]![cboOrderNum]

 

[KenHigg]

I didn't know you could use a domain function to populate a rowsource...

Edit: My bad - I didn't read things correctly - I think Bob caught it in post #5

 

[boblarson]

I didn't know you could use a domain function to populate a rowsource...

Not a row source, but a control source.

 

[Djblois]

Actually Bob it is saved as text. Does that change things?

 

[boblarson]

Actually Bob it is saved as text. Does that change things?

Yep, because you need to encapsulate with quotes:

=Dlookup("[SHIPTO]","WMS_PKO_HD","[REF]=" & Chr(34) & [cboOrderNum] & chr(34))

 

[Djblois]

Bob that is working, just wondering if there is a quicker way to do the same thing because their is a lag time?

 

[boblarson]

Can you include it in the underlying query of the form?

 

[Djblois]

I haven't put two tables in one form yet. I added the table to the Query and switched the Control source over to those fields directly. Here is my issue:

1) cobOrderNum control source is from Orders_Table.Order# but its row Source is from WMS_PK0_HD.Ref table
2) tbCustomer and tbStorer control sources are from WMS_PKO_HD. However, the Order# and the Ref fields should match. if they match then it should return the data I am looking to populate these fields. That is why as you can see I was using a dlookup at first and it worked (of course after your help) They are locked so the user can't change the data in these two.