Recordset string concatenation

[AccessWillKill]

Hello,

I'm trying to concatenate together all the ID fields from a table. However for some reason, my recordests are only picking up a single record meaning the loop cannot concatenate.

i might be missing something simple for all i know

my code :

 Dim dbs As Database
 Dim rst As DAO.Recordset
 Dim Counter As Integer
 Dim getID As String
 Dim ConcatID As String
 
 
' set up a updateable recordset of your table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("TmpTblQA", dbOpenDynaset)
' find number of records in your recordset
Counter = rst.RecordCount
' loop until run out of records
 While Counter & gt
With rst
 ConcatID = ![ContactID].Value
     getID = ConcatID & ", " & getID
        Debug.Print getID
        Debug.Print ConcatID
End With
' moves to next record
rst.MoveNext
' one less record to go
Counter = Counter - 1
' start loop again
Wend
 
 
End Function

 

[JANR]

Try something like this:

Function testit()
Dim rs As DAO.Recordset
Dim GetID As String
Set rs = CurrentDb.OpenRecordset("TmpTblQA", dbOpenDynaset)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
        GetID = GetID & rs!ContactID & ", "
        rs.MoveNext
    Loop
End If
GetID = Left(GetID, Len(GetID) - 2)
Debug.Print GetID
Set rs = Nothing
End Function

JR

 

[AccessWillKill]

That worked a treat. i don't completely understand what the problem was though, the loop i created worked fine however it struggled to get the amount of records to actually loop through.

when i was debugging it the first time i ran through it only picked up one record however when i stepped back it picked up the correct amount.

Works now anyway though. Thanks

 

[latex88]

Try something like this:

Function testit()
Dim rs As DAO.Recordset
Dim GetID As String
Set rs = CurrentDb.OpenRecordset("TmpTblQA", dbOpenDynaset)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
        GetID = GetID & rs!ContactID & ", "
        rs.MoveNext
    Loop
End If
GetID = Left(GetID, Len(GetID) - 2)
Debug.Print GetID
Set rs = Nothing
End Function

JR

JR,

What would the code be if you were to concatenate two fields from the same table together?

 

[MarkK]

What would the code be if you were to concatenate two fields from the same table together?

That is much easier ...

Sub ConcatFields1And2InTblYourTable()
    CurrentDb.Execute _
        "UPDATE tblYourTable " & _
        "SET Field1 = Field1 & Field2 ", dbFailOnError
End Sub