Loop concatenation problem

[Dianne]

I need to take records in a table, loop through the records and concatenate
the records into one field with a comma separating them. My loop works, but
the concatenating is not working, it loops through and only puts the last
record into the field, which is an unbound textbox on a form. Please help.
-Dianne

 

[fredg]

I need to take records in a table, loop through the records and concatenate
the records into one field with a comma separating them. My loop works, but
the concatenating is not working, it loops through and only puts the last
record into the field, which is an unbound textbox on a form. Please help.
-Dianne

Crystal balls never work on Wednesdays.

Forms and Reports have controls, not fields.
You can display multiple values in a control.

Copy and paste your exact code into a reply message and include an
example of the current data and the way you wish the new data to
appear.

 

[Gary Brown]

Your loop should have something like this in it...

strMyAnswer = ""
do while .t.
strMyAnswer = strMyAnswer & "," & myField.value
next record
end do

 

[Clifford Bass]

Hi Dianne and Gary

I would suggest modifying that to something like:

Dim strMyAnswer As String
Dim rstMyData As DAO.Recordset

strMyAnswer = ""
With rstMyData
Set rstMyData = CurrentDb.OpenRecordset("tblMyTable", dbOpenTable)
Do While Not .EOF
If strMyAnswer <> "" Then
strMyAnswer = strMyAnswer & ", "
End If
strMyAnswer = strMyAnswer & !MyField.Value
.MoveNext
Loop
.Close
End With

Clifford Bass

 

[John W. Vinson]

I need to take records in a table, loop through the records and concatenate
the records into one field with a comma separating them. My loop works, but
the concatenating is not working, it loops through and only puts the last
record into the field, which is an unbound textbox on a form. Please help.
-Dianne

Correct the error in your code.

For help doing so... please post the code.

 

[Dianne]

Thanks for all the responses. The loop code I am using is this:

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryVBQIDList", dbOpenDynaset)
With rst
rst.MoveFirst
Do Until .EOF
Me.txtVBQIDList = .Fields("VBQ User ID") & ","
.MoveNext
Loop
End With
rst.Close
Set dbs = Nothing

I need to take a column of data that looks like this:

VBQ User ID
ACOAN01
ADAMA01
AHLSU01
AJJDE01
ALDNI01
ALFMA01
ALLJA01
AMBLY01
ATAMI01

Where each value is a separate row. I need to concatenate all the rows into
one row with the records separated by a comma, to look like this:

VBQ User ID
ACOAN01,ADAMA01,AHLSU01,AJJDE01,ALDNI01,ALFMA01,ALLJA01,AMBLY01,ATAMI01

I have tried all the examples listed and none have worked.

Sincerely,
Dianne

 

[John Spencer]

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Dim strResult as String

Set rst = dbs.OpenRecordset("qryVBQIDList", dbOpenDynaset)
With rst
rst.MoveFirst
Do Until .EOF
strResult = strResult & ", " & .Fields("VBQ User ID")
'>> The problem line>> Me.txtVBQIDList = .Fields("VBQ User ID") & ","
.MoveNext
Loop
End With
Me.txtVBQIDList = Mid(strResult,3)
rst.Close
Set dbs = Nothing

The problem with your code was that you were setting (and overwriting the
previous value) the value of Me.txtVBQIDList to the value of the field each
time. So whichever value was last was the value you got returned.

In other words, you missed the concatenation part of the operation

You could have written the line as
Me.txtVBQIDList = Me.txtVBQIDList & .Fields("VBQ User ID") & ","
which should work although you will end up with a trailing comma.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dad3353]

Dianne...
May I suggest that the problem lies in this line...
Me.txtVBQIDList = .Fields("VBQ User ID") & ","
.... where Me.txtVBQIDList takes on a new value at each iteration.
If you wish to cumulate, try...
Me.txtVBQIDList = Me.txtVBQIDList & .Fields("VBQ User ID") & ","
....which will add at the end of the existing data the new info.
The last iteration will leave a trailing ','; hope that's not important?

 

[fredg]

Dianne...
May I suggest that the problem lies in this line...
Me.txtVBQIDList = .Fields("VBQ User ID") & ","
... where Me.txtVBQIDList takes on a new value at each iteration.
If you wish to cumulate, try...
Me.txtVBQIDList = Me.txtVBQIDList & .Fields("VBQ User ID") & ","
...which will add at the end of the existing data the new info.
The last iteration will leave a trailing ','; hope that's not important?

The way to get rid of that final trailing comma is to remove it after
the code exits the loop:

...... your code ...
Loop
End With
Me.txtVBQIDList = Left(txtVBQIDList,Len(txtVBQIDList)-1)
rst.Close
Set dbs = Nothing

Dianne,
You will avoid reference conflict between the DAO and the ADO
libraries if you specify the library when you Dim your variables, i.e.
Dim dbs As DAO.Database, rst As DAO.Recordset

 

[Dianne]

Thank you so much. It is working beautifully.