none
Table access in VBA - access whole record as an entity RRS feed

  • Question

  • I have in a database 2 tables which are identical in layout.  Each record in either table has 80 fields however. My problem is that I wish to find a record on Table "A" and write it to Table "B" in its entirety.  I know I can do it by using "AddNew", but then I have to move each field individually and this is rather tiresome. 

    Please advise me of any work around, and I apologise if there is a standard common solution or definition of which I am not aware.

    Many thanks

     
    Thursday, March 7, 2019 2:04 PM

Answers

  • Thanks Gustav, Your code will do exactly what I need to do if I use it as a called module.

    Thank you very much!

    • Marked as answer by Roger Everest Friday, March 8, 2019 7:33 AM
    Friday, March 8, 2019 7:33 AM

All replies

  • You can use a loop - as in this slightly different example:

    Public Sub CopyRecords()
     
      Dim rstSource   As DAO.Recordset
      Dim rstInsert   As DAO.Recordset
      Dim fld         As DAO.Field
      Dim strSQL      As String
      Dim lngLoop     As Long
      Dim lngCount    As Long

      strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _

                    "DEFx" & "' Order by Total"

      Set rstSource = CurrentDb.OpenRecordset(strSQL)
      Set rstInsert = ' your other recordset
      With rstSource
        lngCount = .RecordCount
        For lngLoop = 1 To lngCount
          With rstInsert
            .AddNew
              For Each fld In rstSource.Fields
                With fld
                  If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                  ElseIf .Name = "Updated" Then
                    ' Insert some other value.
                    rstInsert.Fields(.Name).Value = Now
                  ElseIf .Name = "Field to be nulled" Then
                    rstInsert.Fields(.Name).Value = Null
                  Else
                    ' Copy field content.
                    rstInsert.Fields(.Name).Value = .Value
                  End If
                End With
              Next
            .Update
          End With
          .MoveNext
        Next
        rstInsert.Close
        .Close
      End With
     
      Set rstInsert = Nothing
      Set rstSource = Nothing
     
    End Sub


    Gustav Brock

    Thursday, March 7, 2019 3:34 PM
  • Thanks for the suggestion, but unfortunately I can only decide which records I need to transfer from within a module I have coded for some other more complex tasks, which is why I was wondering if there is any way within VBA I can do it.
    Thursday, March 7, 2019 4:55 PM
  • Not sure what you mean. You stated to have two tables, wish to copy one record to the other table using VBA, and my VBA code does that. Of course, it will need some adoption to fit your scenario of which we have zero details, thus it must be left to you.

    Gustav Brock

    Thursday, March 7, 2019 8:46 PM
  • … which is why I was wondering if there is any way within VBA I can do it.

    Hi Roger,

    Within VBA you can do almost anything. I went to one extreme of it: with a couple of generalized forms, some definition tables and for the rest "some" code, I build more than 100 different applications.

    So, when you have the rules that you want to apply, yes, you can do it.

    Imb.

    Thursday, March 7, 2019 9:27 PM
  • Thanks Gustav, Your code will do exactly what I need to do if I use it as a called module.

    Thank you very much!

    • Marked as answer by Roger Everest Friday, March 8, 2019 7:33 AM
    Friday, March 8, 2019 7:33 AM
  • You are welcome! Have a nice weekend.

    Gustav Brock

    Friday, March 8, 2019 7:38 AM