Using DLookup to Show Address of Company selected from Combo Box 1

[Boham]

I am creating an orders form inwhich the user will select a company from a combo box that is linked to the Customer Table. I have attempted to display the "Address" of that company in a Text Box below the company name on the form using DLookup to no avail. My current code gives me an address however it is always the address of the first company in the table

My current code consists of...

Private Sub CustomerID_AfterUpdate()
Me!Address = DLookup("[ShipAddress]", "Customers", "[CustomerID]= CustomerID")
End Sub

Where...
Address = The name of the field in the form where I want the address displayed
ShipAddress = The name of the field in the Customers table
Customers = The name of the table
CustomerID = The Company ID (Displayed in my Form as the Company Name)
Customer ID = The Value stored in my Form

I have successfully used the DLookup function elsewhere in a subform but I just can not seem to get this one to work. Any help in solving or troubleshooting would be appreciated.

 

[zevw]

I would create a combo box based on a query that has several field customer id, address etc. all the fields that you want to display. set the width of those cfolumns to 0 length.

In the the AfterUpdate I would write

Me.Address = Me.[ComboBoxName].Column(1)

ecah field based on the column of the query it is in.

Please note in Combo Boxes the first column is 0 which is most probably the Customer ID.

I think this will work!!!

 

[Boham]

Thank you for your response zevw... I played with your way a little but could not get it to initially work. I'm sure if I played with it as much as I did with the DLookup I could get it to work however; changing my original code as such seems to do the trick

Me!Address = DLookup("[ShipAddress]", "Customers", "[CustomerID] =" & CustomerID)

I don't know if it is more or less efficient than the query but it works

Thanks again!