Display query resultS in text box

[kstevens]

(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.

[ChipR]

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.

    Dim records As Object 
    Dim db As Object 
    Dim strText as String
 
    Set db = CurrentDb() 
    Set records = db.openrecordset("query1") 
 
    strText = ""
    While Not records.EOF
        strText = strText & records!fieldName & vbCrLf
'vbCrLf is an endline, " " for a space
        records.movenext 
    Wend 
 
    txtBox1 = strText
 
    records.Close 
    Set records = Nothing 
    Set db = Nothing 
Exit Sub

Just don't forget the records.MoveNext!

[NeoPa]

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.

[kstevens]

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.

[NeoPa]

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.