Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    1,487

    Question Unanswered: For Each Field In Fields method?

    Ok folks...everyone is familiar with the following code to copy data from one table to another table....

    set rs1 = db.openrecordset("select * from [table1] where [id] = " & me.id)
    set rs2= db.openrecordset("select * from table2]")
    rs2![field1] = rs1![field1]
    rs2![field2] = rs2![field2]
    .....
    .....
    .etc to field10
    rs2.Update

    BUT....can anyone pleeeeease inform me as to how we can perform the same task without the need to list all the fields in the tables. For example, is there a way to get the phrase below to do this:

    Dim RSTfld as Field
    For Each RSTfld In rs2.Fields
    .......
    ......
    Next RSTfld

    In other words, have the For/Next loop do the job for us.
    Any help is greatly appreciated.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: For Each Field In Fields method?

    Originally posted by CyberLynx
    Ok folks...everyone is familiar with the following code to copy data from one table to another table....

    set rs1 = db.openrecordset("select * from [table1] where [id] = " & me.id)
    set rs2= db.openrecordset("select * from table2]")
    rs2![field1] = rs1![field1]
    rs2![field2] = rs2![field2]
    .....
    .....
    .etc to field10
    rs2.Update

    BUT....can anyone pleeeeease inform me as to how we can perform the same task without the need to list all the fields in the tables. For example, is there a way to get the phrase below to do this:

    Dim RSTfld as Field
    For Each RSTfld In rs2.Fields
    .......
    ......
    Next RSTfld

    In other words, have the For/Next loop do the job for us.
    Any help is greatly appreciated.
    You can use the RecordSet in one of the following:

    Recordsets(0)
    Recordsets("Name")
    Recordsets![Name]

    The first option would suit your desire, if you make a loop you can go through all fields of the RecordSet

  3. #3
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Arrow a littel different appr

    HisRS.moveFirst
    Do Until HisRS.EOF
    For i = 0 To 9 ' say your table has 10 col
    debug.print HisRS(i).Value
    Next
    HisRS.Movenext
    Loop

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    My $0.02:

    *** Since you are copying from one table to another, and that's all the detail I have, I'm assuming the column names are the same ***

    Dim rstFrom as New Adodb.recordset
    Dim rstTo as New Adodb.recordset
    Dim fld as Field

    rstFrom.Open ("SELECT * FROM <YourSourceTableGoesHere>", currentproject.connection, adOpenStatic, adLockReadOnly, adCmdTableDirect

    rstTo.Open ("SELECT * FROM <YourDestTableGoesHere>", currentproject.connection, adOpenDynamic, adLockOptimistic

    Do While Not rstFrom.EOF
    rstTo.AddNew
    For each fld in rstFrom.Fields
    rstTo(fld.Name).Value = rstFrom(fld.Name).Value
    next Fld
    rstTo.Update
    rstFrom.MoveNext
    Loop

    ' close out your recordsets

    *** I only typed this in, I may have made a small typo ***
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Thank you all very much for your assistance. It's been a great help.


    $0.02 seems to go a long way in some places
    Last edited by CyberLynx; 11-21-03 at 02:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •