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

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

Post by QW5uIGluIE » Wed, 06 Jun 2007 05:06:02

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:
FK HeadingID
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!"
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.
DoCmd.runSQL "Delete * FROM tblFailures_local"
End If

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