Single Click Transfer Of Record Between Tables

[Frank Parish]

I have a front end form that lets a user put in their information (name, phone #,etc) and when they submit the form it gives them a queue number. At this point this information is recorded in it's own table. I have then set up a form that would allow me to approve them to the actual queue. Essentially I could look at a record and approve it by clicking a button and have the information cut from the first table (Student_Signup ) and recorded (pasted) into the actual queue table (3d_Queue). I tried using an onclick event to follow a snippet like this but am having trouble having it work:

Private Sub Command43_Click()
 
Set db = CurrentDb
Set rec = db.OpenRecordset("Student_signup")
Set rec2 = db.OpenRecordset("3d_Queue")

You can then check, add and edit data using the proper calls
i.e.

rec2.addnew
rec2("ID") = "Done"
rec2.update 

End Sub

Obviously I'm new to this and trying to teach myself so anything would help.

[Stephen Parker]

Here is some basic air code you can customize to accomplish your task.

'NOTE: The following code requires a reference to "Microsoft DAO Object Library"

Dim strSQL As String
Dim daoDBS As DAO.database
Dim daoREC As DAO.Recordset

Set daoDBS = CodeDb
strSQL = "SELECT ColumnA, ColumnB, ColumnC, FROM TableA WHERE Recordset = MySearchCriteria"
Set daoREC = daoDBS.OpenRecordset(strSQL)
If Not (daoREC.BOF And daoREC.EOF) Then
    daoREC.MoveFirst
    While Not daoREC.EOF
        If daoREC("ColumnA").Value = "Whatever I am looking for" Then
            strSQL = _
                "INSERT INTO TableB (ColumnA, ColumnB, ColumnC) VALUES(" & vbCrLf & _
                    adoREC("ColumnA").Value & "," & vbCrLf & _
                    adoREC("ColumnB").Value & "," & vbCrLf & _
                    adoREC("ColumnC").Value & ")"
            CodeDb.Execute strSQL, dbSeeChanges
            CodeDb.Execute "DELETE TableA.* FROM TableA WHERE Recordset = MySearchCriteria", dbSeeChanges
        End If
        daoREC.MoveNext
    Wend
End If

daoREC.Close
daoDBS.Close

strSQL = ""
Set daoREC = Nothing
Set daoDBS = Nothing