CODEWITHSUNDEEP
ms-accessvbarecordset
arm
arm

Reputation: 49

How to copy a recordset from one table and add to another table?

I have two tables and I have a form linking to one of them. I want to check a value and if it is true, add the record the other table by using VBA. Can anyone help me, please?

This is my code, but it does not work:

Dim rec1 As DAO.Recordset
Dim rec2 As DAO.Recordset

Set rec1 = CurrentDb.OpenRecordset("TotalTPAq")
Set rec2 = CurrentDb.OpenRecordset("Visi")

rec1.MoveFirst
Do Until rec1.EOF

    If rec1!Date = PlanDate.Value Then ' planDate is a text box
        rec2.AddNew
        rec2![Planing Date History] = PlanDate.Value
        rec2.Update
        rec2.Close
    End If
    rec1.MoveNext
Loop
rec1.Close

Set rec2 = Nothing
Set rec1 = Nothing

DoCmd.Close

Upvotes: 2

Views: 27079

Answers (1)

Katana24
Katana24

Reputation: 8959

This should provide a start for you: 

'Run query to fill table
Private Sub btnRnQry_Click()

    'No value entered
    If IsNull(Me.txtEntry) Or Me.txtEntry = "" Then
        MsgBox ("Is null or empty")
    Else
        'Assign value to variable
        Dim entry As String
        entry = Me.txtEntry

        Dim sql As String
        sql = "INSERT INTO tableTwo ([First Name],Surname,[Phone Number] )" & _
              "SELECT * " & _
              "FROM tableOne " & _
              "WHERE [First Name] = '" & entry & "';"

        'Run the SQL
        DoCmd.RunSQL sql

    End If

End Sub

Upvotes: 3

Related Questions