Concatenate and write to a new table

[gillianleec]

I have a dataset that I import and then need to concatenate. The dataset comes in the form of two record sets that then need to be parsed into one record. There is no connecting id, the only thing that I can do is grab them in two record groups and then concatenate that into one. I have written the code below that is doing what I want but the results are showing only the last group. Can anyone help me figure out what I am missing that will enable me to grab each set and write to a table.

table DocErr
Fields: err

Function concatenate(p As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim textlist As String
Dim row1 As String
Dim row2 As String

Set db = CurrentDb
Set rs = db.OpenRecordset(p)

With rs
rs.MoveFirst

Do While rs.EOF = False
row1 = rs.Fields(0)
rs.MoveNext
row2 = rs.Fields(0)

textlist = row1 + row2
concatenate = textlist

rs.MoveNext

Loop
End With

End Function


I then use this in a query
"SELECT concatenate("Select err from docerr") AS List INTO Doctest FROM DOCErr"

This results in a table where there are the same number of records that there were in the original table (there should be half as many) and the field has the same thing in it for each record which is the result of the last two records from the original table.

Table Docerr
Record 1 Docname: Schmoe, Joe
Record 2 DocID: 12345
Record 3 Docname: Andrews, William
Record 4 DocID: 45678
etc.

Results desired
Record 1 Docname: Schmoe, Joe DocID: 12345
Record 2 Docname: Andrews, William DocID: 45678

Results received
Record 1 Docname: Andrews, William DocID: 45678
Record 2 Docname: Andrews, William DocID: 45678
Record 3 Docname: Andrews, William DocID: 45678
Record 4 Docname: Andrews, William DocID: 45678


Sorry for the long post. I hope this is enough detail

Gillian

 

[MajP]

The function is called for each record in the table. Unfortunately each time you call the function you start with the very first record i.e. "rs.movefirst".

I would just do the whole thing in DAO/ADO. Loop through your records and write to DOCTest.
untested

Public Sub fixErrors(p As String)
  Dim rs As DAO.Recordset
  Dim rsDocTest As DAO.Recordset
  Dim textlist As String
  Dim row1 As String
  Dim row2 As String

  Set rs = CurrentDb.OpenRecordset(p)
  Set rsDocTest = CurrentDb.OpenRecordset("Doctest", dbopendynase)
  Do While Not rs.EOF
   rsDocTest.AddNew
     row1 = rs.fields(0)
     rs.MoveNext
     row2 = rs.fields(0)
     rsDocTest.fields("List") = row1 & row2
   rsDocTest.Update
     rs.MoveNext
 Loop
End Sub