"Collating" query results

[EricTheRed]

[I wasn't quite sure where this belonged in the forums, so I tossed it into General. Sorry if it seems a bit out of place!]

I have a query that takes two parameters, both specifying IDs to limit the search. The recordset returned by the query has only one text field: strCategoryName. I would like to be able to take all the results of the query, and create a single string out of them. For example:
If the query returned...
A
B
C
I would like to have "A, B, C" for display purposes. Is there any simple way to do this? I could see creating the query manually and looping over the results...

Dim str As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
...
Set rs = db.OpenRecordset("...")
...
While Not rs.EOF
   str = str & rs![strCategoryName] & ", "
Wend

Me.lblDisplayMe.Caption = str

...but this doesn't strike me as the best solution. Ideas?

- Eric

 

[WayneRyan]

Eric,

Running list of comma-separated children ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;141624

Wayne

 

[EricTheRed]

Thanks for the link. Unfortunately, it deals only with creating a list for a report. I need to display this information on a form. Unless I've missed something in the article, I can't see a way to apply the technique to a form. So I'm back to square one, I suppose. Any other ideas?

- Eric

 

[WayneRyan]

Eric,

I see what you mean, but the OnDetail print event is pretty
much what you would do in the OnCurrent event of a form,
but either way, you'd be writing code.

We tried.

Wayne

 

[EricTheRed]

Ah well. Thanks anyhow.

A quick related question: if I have a query that accepts two parameters, is there any way to specify them when I perform the query in my code?

- Eric

 

[WayneRyan]

Eric,

There should be some examples of "querydef" and "parameters"
on this forum.

Wayne

 

[EricTheRed]

Great. Thanks again. (I'll finish this thing if it kills me!)

 

[WayneRyan]

Eric,

The query def parameter search is tougher than it looks:

"qdf.parameter" is what it took. See Pat's syntax.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52360&highlight=qdf.parameter

Wayne