Copy record, add new, append data from excel

[chaostheory]

What i need to do is read a number in from excel, find that number. Copy that whole record, add new record, paste that data into the new record and add on new data to the new record. I was hoping it would be easy as ...rs.copy but i see now it's not.

I've got it opening the table, finding the record i need, but how do i easily copy that record. Even pulling the contents of that record into excel would be fine then i can just rs.addnew using what i pulled into excel + new data i have to add on to it.

1. How do i pull in the found record in whole without specifying each column header to be pulled in (in case more columns are added in the future) This whole project is being written so that future table changes dont mean future code changes.

So i need a command similiar to "eof" but for ...end of record or something. Start on first column, continue to pull data in until you run out of columns then stop.

Then i can write the rest of the code from there... What i need is marked in red in the code below.

'Check to make sure the process info doesnt exist in the genealogy list, if it does error, else add new
For x = 1 To rowCount
    If Range("A1").Offset(x, 0) <> "" Then
        theGenealogy = Range("A1").Offset(x, 0)
        rs.MoveFirst
        theVariable = Range("A1").Offset(x, 0).Comment.Text
        strSearchCrit = "[" & theVariable & "] = """ & theGenealogy & """"
        'MsgBox (strSearchCrit)
        rs.FindFirst strSearchCrit
        If rs.NoMatch = False Then
        [COLOR=red]'I need to copy the found record into excel here[/COLOR]
[COLOR=red]       'How can i say "pull the whole record without specifying which cells i want"[/COLOR]
           rs.AddNew
           [COLOR=black]'copy old data back into new record then append the new data element[/COLOR]
           rs.Fields(" & theVariable & ") = theProcess 'this is new data element
           rs.Update
         Else
           MsgBox "Genealogy '" & Range("A1").Offset(x, 0) & "' does not exist!!!", vbOKOnly + vbCritical, "Danger, Error, Problem, RUN FOR YOUR LIFE"
           Exit Sub
         End If
    End If
Next x

 

[chergh]

To go through each field in the record the following should work.

for i = 0 to rs.fields.count -1
   something = rs.fields(i).value
next i

 

[chaostheory]

Thank you for the response, i do believe that is exactly what i needed.