Concatenate Two Records

[RobSol]

I have two records in a table in two seperate fields

Table 1
Record
Field 1 123456
Field 2 888888

I am trying to put them into a second table in one field to look like this

Table 2 Record
Field 1 123456,888888

Can this be done in vba?

 

[Daniel Pineault]

Something like the following should work.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rstUpdate As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Table 1")
Set rstUpdate = db.OpenRecordset("Table 2", dbOpenDynaset, dbAppendOnly)
With rst
If .RecordCount > 0 Then
.MoveFirst
Do While Not .EOF
rstUpdate.AddNew
rstUpdate![Field1] = ![Field1] & "," & ![Field2]
.MoveNext
Loop
End If
End With

rst.Close
rstUpdate.Close
Set rst = Nothing
Set rstUpdate = Nothing
Set db = Nothing

 

[John W. Vinson]

I have two records in a table in two seperate fields

Table 1
Record
Field 1 123456
Field 2 888888

I am trying to put them into a second table in one field to look like this

Table 2 Record
Field 1 123456,888888

Can this be done in vba?

Yes, it can. Should it? I would say absolutely NO. It will make the data
harder to search, impossible to sort, harder to manage in many ways! If you
just want to print or display the concatenation, just do so in a query; see
the sample code at

http://www.mvps.org/access/modules/mdl0004.htm

If you have a really really good reason to do so you can use the same function
in an Update query... but it's still a Bad Thing To Do.

John W. Vinson [MVP]