Qry records to string values

[danielc]

Hi,
I'm new to this forum and I'll apreciate your help.
What I want to do it's to make a string from several query records.

ex:
query

FIELD1 FIELD2
VAL1-1 VAL2-1
VAL1-2 VAL2-2
VAL1-3 VAL2-3

I need FIELD2 as "VAL2-1, VAL2-2, VAL2-3"

Thanks for your help.

Dny

 

[WayneRyan]

dny,

You'll need to use a recordset to do this. This is a rough idea. You could
even make it a public function & return the results into a query based on
each parent record.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strResult As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from ChildTable Where ...")
strResult = ""

While Not rst.EOF And Not rst.BOF
   strResult = strResult & rst!SomeField & ", "
   rst.MoveNext
   Wend

If Len(strResult) > 0 Then 
   strResult = Mid(strResult, 1, Len(strResult) - 2)
End If

Wayne

 

[modest]

To use a DAO object, you must reference it. In VBA window goto Tools -> References... Microsoft Data Access Objects link

 

[modest]

Dim db        As DAO.Database
Dim rs        As DAO.Recordset
Dim strResult As String

strResult = ""
Set db = CurrentDb
Set rs = db.OpenRecordset("TABLE/QUERY NAME",dbOpenDynaset)

rs.MoveLast

Do Until rs.BOF
    strResult = strResult & rs.Fields("Your Field Name").Value & ", "
    rs.MovePrevious
Loop

If strResult <> "" Then 
   strResult = Left$(strResult, Len(strResult) - 2)
End If

This is pretty much the same thing, just tweaked a little

 

[danielc]

Hi,

Thanks.
I receive a Error 3061 at code line
Set rs = db.OpenRecordset("sel cmd", dbOpenDynaset)

Dny

 

[WayneRyan]

Dny,

Set rs = db.OpenRecordset("sel cmd", dbOpenDynaset)

What is "sel cmd"?

I'm guessing that it's a textbox on your form. If that's the case, change
the reference to:

Set rs = db.OpenRecordset(Me.[sel cmd], dbOpenDynaset)

It can't be a VBA string; those darned spaces mess everything up!

Need more info.

Wayne