dlookup help

[jeremypaule]

I have a query that outputs to a text box. There are multiple records in the query but it is only outputting one record. My code is as follows

Private Sub Command30_Click()

CUSTOMERS.Value = DLookup("[Account] & ' ' & '(' & [VAR] & ')' ", "Customer Variance")

End Sub

How could I make ALL the records show up in the textbox one underneath the other? Or separated by a comma?

 

[boblarson]

First of all, a DLookup is used for only returning ONE (1) value.

Second, I think a list box would probably be better than a text box for displaying the records, as you can set the rowsource to be a query and select the fields you want to display.

 

[CraigDolphin]

Dlookup only ever returns the first value that meets the conditions you specify.

So, you could use a custom function (vba) to build a string that concatenates all your query results (separated by commas) or use a form/subform in continuous view, which uses your query as its recordsource.

[edit] N'mind....boblarson replied before I sent

 

[jeremypaule]

Thanks for the reply.

I tried to do it with a list box and link it to a query and it worked, Although I would rather not link the list to a query because it'll take forever for my form to load because my query contains data that has thousands and thousands of records.

Also, the query is not called until a button is pushed since the query has critera based on my combo boxes. Is there some kind of sample you could give me to make a function that would work?

thanks again!!!