Lookup records in existing query and display on a form

[Annabelle Lee]

Hey,
I am currently working on a form which requires user to enter customer ID and then display corresponding records with that customer ID, ex. customer status, amount spent etc. I did some research and realized there's a function called "DLookup" can do the work for me but just confused how to use it correctly.

I setted up an update button. After entering customer ID in a seperate text box and press the update button, the corresponding records would show up in other text boxes. And the code I used is in the following (an example for one text box):

 Private Sub CmdUpdate_Click()
       Me.lblCustomerStat.Caption = DLookup("Customer Status", "Query.[Asset Status Query]", "Query.[Asset Status Query].ID = Me.txtCustomerID")
 
End Sub
 

which did not work.... could someone help me please

[Seth Schrock]

The reason your code doesn't work is that you have your syntax wrong. The correct command would be

Me.lblCustomerStat.Caption = DLookup("Customer Status", "Asset Status Query", "ID =" & txtCustomerID)

Chances are however there is a better way of doing things. DLookup() is slow and the information can be gained through a query. Also, a query can return multiple records while DLookup can only return one. You mentioned that you were wanting to display corresponding records (plural) so I thought you might want to know that part. I'm not exactly sure what you are trying to do, but I'm going to hazard a guess and say that it would be better to create a query that has all the fields you need and then set the criteria to be the Customer ID in the textbox of on the form. You could then make the form do a requery every time you updated the Customer ID. Just base a subform on the query that you create and have the subform go below the Customer ID textbox.

[Annabelle Lee]

Hey Seth,
Really appreciate for your reply! and I have tried the Dlookup code you provided. It showed a syntax error saying "missing operator in query expression 'Customer Status'". I have checked back and forth but have no idea why it happened...isn't it just the field name of the record that I am looking for?
Also, u mentioned create subform to requery every time you update the customer ID. What I am trying to do here is enter the customer ID and have several fields in the bottom like Customer Status, Customer Address etc and have them show the corresponding records of the very ID I input earier. Usually there would be just one record in each field. Is this still applies to what you suggested about building a subform? If it is how would you do it step by step?
Really appreaciate your help again!!

[NeoPa]

Try this slightly changed one instead :

Me.lblCustomerStat.Caption = DLookup(Expr:="[Customer Status]", _
                                     Domain:="[Asset Status Query]", _
                                     Criteria:="[ID] =" & Me.txtCustomerID)

Named parameters aren't necessary, but they're clearer when the line is so long it should be split (I split after eighty characters but any reasonable number that allows the code to be read without horizontal scrolling is fine).

[Annabelle Lee]

Thank you NeoPa!! it is working now~~

[NeoPa]

Pleased to hear it Annabelle :-)

Names with embedded spaces usually need the brackets [] to indicate where the end of the name is. Otherwise it treats the space as the end.