What I don't know how to do is get separate messages for each match.
I threw this together. Pretty sure it works. TRY THIS ON A COPY OF YOUR DB!!
I don't use spaces in object names, so check names in the code!!
You need to add the code to append remaining records the the main table.
Code:
Public Sub CompareTables()
'
'this code loops through a recordset and deletes
'duplicate records from the tempTable
'
'
Dim r As DAO.Recordset
Dim sSQL As String
Dim Msg As String, Title As String, DeleteMsg As String
Dim Style, Response
'message box stuff
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Duplicate record found"
Msg = "Are you sure you want to delete this record?"
'duplicates query
sSQL = "SELECT TempTable.UID, TempTable.Car, TempTable.Owner"
sSQL = sSQL & " FROM MainTable INNER JOIN TempTable ON MainTable.UID = TempTable.UID;"
Set r = CurrentDb.OpenRecordset(sSQL)
If Not r.BOF And Not r.EOF Then
r.MoveLast
' MsgBox r.RecordCount
r.MoveFirst
Do While Not r.EOF
'message for each record
DeleteMsg = ""
DeleteMsg = Msg & vbNewLine & vbNewLine & "duplicate data exists for UID = " & r!UID
'ask to delete the record
Response = MsgBox(DeleteMsg, Style, Title)
If Response = vbYes Then
'delete record
CurrentDb.Execute "DELETE FROM TempTable WHERE UID = '" & r!UID & "'", dbFailOnError
End If
r.MoveNext
Loop
End If
r.Close
Set r = Nothing
'code to append remaining records in tempTable to MainTable goes here
End Sub
What do you think?