Appending a Record Via VB 1

[jt463]

What syntax would I need to add text to an existing record?

If I have the following records on a table1:

Color
Red
Blue

and I want to create a table2 with a field called 'AllRecords' that would look like this:

AllRecords
Red, Blue

I would use the following code to create the table2 record:

Set db = CurrentDb
Set rst = db.OpenRecordset("select Color from table1")

Set db2 = CurrentDb
Set rst2 = db2.OpenRecordset("select * from table2")

rst.MoveFirst

rst2.AddNew
rst2!AllRecords = rst!Color
rst2.Update

rst.MoveNext

rst2.[b]???[/b]

That is the point I get stuck. I want to edit the rst2!AllRecords by adding ", " and then the next record from rst!Color.

Any help would be greatly appreciated!

 

[snyperx3]

I guess you could do

rst2.Edit
rst2![AllRecords] = rst2![AllRecords] & ", " & rst![Color]
rst2.Update

-Pete

 

[formerTexan]

I won't ask why, but here is your answer

Dim strColor as string
Set db = CurrentDb
Set rst = db.OpenRecordset("select Color from table1")

Set db2 = CurrentDb
If not rst.bof and not rst.EOF then
rst.MoveFirst
Do While Not rs.EOF
strColor = strColor & "," & rst!Color & vbnullstring
rst.movenext
Loop
End If
Set rst2 = db2.OpenRecordset("select * from table2")
strColor = Left$(strColor,Len(strColor)-1) ' drop the ending ","
rst2!AllRecords = strColor
rst2.Update

'clean up recordsets

Cheers,
Bill

 

[jt463]

Thanks for the suggestions. Since it was less complex, I used snyperx3's suggestion, and it worked!

Thanks again for your time.