Lookup values & copy record or move to new record if doesn't exist

[Ann in CA]

I am trying to write the code behind a button which will perform a
"batch-copy" type function and send all "errors" to a local table.

These are the three tables:
tblLists
PK ListID
ListModel
tblListHeadingLink
PK LHLinkID
FK ListID
FK HeadingID
tblHeadings
PK Heading ID
HeadingNumber
HeadingModel
HeadingInfo & more

All PKs are AutoIDs.

I have a form with two combo boxes, cboCopyFromList, and cboCopyToList.
Usually the ListModel for these two lists will be different.
When the user presses the "submit" button, it will look up all records in
tblListHeadingLink that exist with the ListID being equal to cboCopyFromList.
(This will usually be 50-100 records) Then it will look up the records in
tblHeading and get the HeadingNumber that matches the PK HeadingID. Then it
will look up the records from tblHeadings where HeadingNumber matches those
from the recordset we just specified, but that exist for HeadingModel equal
to ListModel of the ListID from cboCopyToList, and append the
HeadingID-ListID record to tblListHeadingLink. For those records that don't
exist in tblHeadings, they will be moved to tblFailures_Local so the user can
see what wasn't able to be copied.
Any clue where I can even start this?


Private Sub btnSubmit_Click()
On Error GoTo ErrorProc
If IsNull(Me.cboCopyFromList) Then
DisplayMessage "You must specify a list to copy from!"
Exit Sub
End If
If IsNull(Me.cboCopyToList) Then
DisplayMessage "You must specify a list to copy to!"
Exit Sub
End If

Dim intToListID As Integer
Dim intFromListID As Integer
Dim intOldHeadingID As Integer
Dim intNewHeadingID
Dim blnFailure as Boolean
intToListID = Me.cboCopyToList
intFromListID = Me.cboCopyFromList
blnFailure = False

strCommand = "INSERT INTO tblListHeadingLink (ListID, HeadingID,
HeadingStatus, HeadingUpdate) SELECT " & intToListID & ", " & intNewHeadingID
& ", " & """Pending Addition""" & ", " & Date & " FROM tblListHeadingLink
WHERE [ListID] = """ & intFromListID & """"

DoCmd.RunSQL strCommand

If blnFailure = False Then
If Msgbox ("Some records were unable to be copied. Do you want to see those
now?", vbYesNo, "Oops") = vbYes Then
DoCmd.openform "frmFailures_local" ' Form has "confirm" button to delete
from local table when pressed.
Else
DoCmd.runSQL "Delete * FROM tblFailures_local"
End If

ExitProc:
Exit Sub
ErrorProc:
MsgBox Err.Number & Err.Description
Resume ExitProc
End Sub