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:
PK Heading ID
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!"
If IsNull(Me.cboCopyToList) Then
DisplayMessage "You must specify a list to copy to!"
Dim intToListID As Integer
Dim intFromListID As Integer
Dim intOldHeadingID As Integer
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 & """"
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.
DoCmd.runSQL "Delete * FROM tblFailures_local"
MsgBox Err.Number & Err.Description