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

Display query resultS in text box

P: 74
(Yes... i capitalized the S on purpose)
I didnt know before today that dlookup only found the first result. I am actually trying to display the results of a query in a report, but there are multiple records being returned... (sometimes as many as 500). I know there is probably another way of acheiving this, but i was hoping to be able to use something like dlookup, with a text box with "can grow: yes" , so that it would only be as big as it needed to be, i tried using a listbox, but they cant grow :(

If anybody knows how to complete this, please let me know.
May 13 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,287
List boxes can't grow, but they can scroll. I'm not sure that using a text box for this is the best solution, but it's certainly possible. If you want to get data from a bunch of results, you can put them in a recordset and step through it. Here's an example.

Expand|Select|Wrap|Line Numbers
  1.     Dim records As Object 
  2.     Dim db As Object 
  3.     Dim strText as String
  5.     Set db = CurrentDb() 
  6.     Set records = db.openrecordset("query1") 
  8.     strText = ""
  9.     While Not records.EOF
  10.         strText = strText & records!fieldName & vbCrLf
  11. 'vbCrLf is an endline, " " for a space
  12.         records.movenext 
  13.     Wend 
  15.     txtBox1 = strText
  17.     records.Close 
  18.     Set records = Nothing 
  19.     Set db = Nothing 
  20. Exit Sub
Just don't forget the records.MoveNext!
May 13 '09 #2

Expert Mod 15k+
P: 31,709
May I suggest that, unless you have compelling reasons to do it this way, you take a more standard and manageable approach and design the report (or even a subreport if the main report is already bound) to use that particular data source.
May 13 '09 #3

P: 74
Thanks for the advice. I ended up putting a subreport.... much easier, and looks great. Dont know why i didnt do it in the first place.
May 15 '09 #4

Expert Mod 15k+
P: 31,709
I expect that's simply about experience.

I still struggle to think of the best way to do things in new areas that I've not had much previous experience in.
May 15 '09 #5

Post your reply

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