Need to Move Records - Column to Single

[Therber]

I'm assuming I need VBA to accomplish moving the results of a query as follows:

Field1 Field2
ABC 123
CBA 321

To the following:

Field1 Field2 Field3 Field4
ABC 123 CBA 321

I need to move the results of the query into a single record for import into another program (which will only receive one record). I could export to Excel and rearrange, but I'd like to stay in Access. Does anyone know of any example VBA code for this type of situation?
Thanks

 

[nickjar2]

Will it always be

Field1 Field2
ABC 123
CBA 321

or could it also be

Field1 Field2
ABC 123
CBA 321
DEF 567
FGR 121 etc etc

Nick

 

[nickjar2]

If the answer to the above Q is Yes it will always be like:
Field1 Field2
ABC 123
CBA 321

then try the following. Create new table with 4 fields eg tblExport. Create a new module and paste the following into it. Hit the F5 key.

Function createExportFile()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim iCount As Integer
Dim sDelete As String

Set db = CurrentDb
Set rs1 = db.OpenRecordset("queryName&quot
' populate recordset
rs1.MoveLast
rs1.MoveFirst

' Empty any records from tblExport from previous runs
sDelete = "Delete * from tblExport"
db.Execute sDelete

' Open tblExport
Set rs2 = db.OpenRecordset("tblExport", dbOpenDynaset)

rs2.AddNew
rs2.Fields(0) = rs1.Fields(0)
rs2.Fields(1) = rs1.Fields(1)
rs1.MoveNext
rs2.Fields(2) = rs1.Fields(0)
rs2.Fields(3) = rs1.Fields(1)
rs2.Update

rs1.Close
rs2.Close

Set db = Nothing

End Function

Hope this works,

Nick