By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Lookup records in existing query and display on a form

P: 12
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):

Expand|Select|Wrap|Line Numbers
  1.  Private Sub CmdUpdate_Click()
  2.        Me.lblCustomerStat.Caption = DLookup("Customer Status", "Query.[Asset Status Query]", "Query.[Asset Status Query].ID = Me.txtCustomerID")
  3.  
  4. End Sub
  5.  
which did not work.... could someone help me please
Jan 9 '12 #1

✓ answered by NeoPa

Try this slightly changed one instead :
Expand|Select|Wrap|Line Numbers
  1. Me.lblCustomerStat.Caption = DLookup(Expr:="[Customer Status]", _
  2.                                      Domain:="[Asset Status Query]", _
  3.                                      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).

Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
The reason your code doesn't work is that you have your syntax wrong. The correct command would be

Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 9 '12 #2

P: 12
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!!
Jan 10 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
Try this slightly changed one instead :
Expand|Select|Wrap|Line Numbers
  1. Me.lblCustomerStat.Caption = DLookup(Expr:="[Customer Status]", _
  2.                                      Domain:="[Asset Status Query]", _
  3.                                      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).
Jan 11 '12 #4

P: 12
Thank you NeoPa!! it is working now~~
Jan 11 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 11 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.