Let me suggest getting rid of the temp table.
Take the table you are using - copy it name it the same <table>_Archive - remove the unique ID, then in front add an autocounter primary key. At the end of this _Archive table - add a DateTransaction field.
By using the Make Table query - and adding all the fields of your orginal table - the result will be the same data types. That will save some time. Then in design mode 1. remove the old primary key, 2. Add a new autocounter in front, 3. add the DateTransaction field with a default of NOW
For a new customer (dealer whatever) check - do they currently exist.
Yes - copy all fields from your existing record to be updated to the <Table>_Archive
Now, the orginal record is still intact - you have just copied it to the archive.
Note a Function was used to do this - the function returns a Pass / Fail (true/false) that the archive was completed successfully.
Now - use an Update Query to modify the existing record in place. A function could provide a Pass/Fail
If it is not unique (i.e. a New customer) - make an Append query in a function - add the new dealer to the last row. The Append Query function might also return a Pass/Fail (true/false)
See attachment -
The following code is shortened to give you the concept of archiving a record before Updating any field.
Function XactSHLBHLToArchiveTable(ArchiveTableName As String, UserAction As String) As Boolean
' Copy Production table structure only rename TableName_Archive, Turn primary key off on first field add a last field with the default value of Now()
Dim dbArchive As DAO.Database
Dim rstArchiveTable As DAO.Recordset
Dim SQLArchiveTable As String ' Archive table is an exact copy of the table - with an additional time stamp field at the end plus Action taken
10 On Error GoTo Error_XactSHLBHLToArchiveTable
20 SQLArchiveTable = "SELECT " & ArchiveTableName & ".* FROM Wells_SHLBHL_Archive;"
30 Set dbArchive = CurrentDb()
40 Set rstArchiveTable = dbArchive.OpenRecordset(SQLArchiveTable, dbOpenDynaset, dbSeeChanges)
' ------------ Wrote code to genereate the following code ------------ Public Function ShowFieldsRSModify(strTable)- writes this code
' for generated code the ID_shlbhl needs to have the _archive removed
60 rstArchiveTable!ID_SHLBHL = Me.Recordset!ID_SHLBHL
70 rstArchiveTable!ID_SHLBHL = Me.Recordset!ID_SHLBHL
80 rstArchiveTable!ID_Wells = Me.Recordset!ID_Wells
90 rstArchiveTable!Lot_SHLBHL = Me.Recordset!Lot_SHLBHL
' you get the idea Keeping field names in the Archive make it simple
280 rstArchiveTable!Well_Name = [Forms]![Wells_2]![Well_Name] ' Changed code to obtain from form since no field exist in recordset
300 rstArchiveTable!UserName = Environ("username") ' OPTIOINAL get user name from function and substitute since no field exist in recordset
' Close the archive database
330 Set rstArchiveTable = Nothing
340 Set dbArchive = Nothing
350 Call LogUsage("Wells_SHLBHL Transact Log Current Record", "XactSHLBHLToArchiveTable function", "Success - Logged current record")
360 XactSHLBHLToArchiveTable = True
370 Exit Function
390 XactSHLBHLToArchiveTable = False ' ERROR function returns False
400 Call LogUsage("Wells_SHLBHL Transact Log Current Record", "XactSHLBHLToArchiveTable function", "Failure - Logged record")
'400 is a transaction log that records all error information
In review: You basically clone the table to table_Archive - adding fields autocount in front - and date created in back
If the Table Record needs Updated - first copy all to the Table_archive, recive a Pass/Fail - then run Update on the Table record receiving a Pass/fail
If this is a new record - use Append to add it.
My systems transact all records and record all users actions as there is plenty of Quality Assurance and auditing task.
These kind of Archive tables can make a huge difference in troubleshooting.