Copy current record to another cloned tabel in same DB (1 Viewer)

Rx_

Nothing In Moderation
Local time
Yesterday, 22:44
Joined
Oct 22, 2009
Messages
2,803
Currently maintaining a database that allows the user to delete the current record. It is a single table in this case.
My preference is never entirely delete things.

I am thinking that making a copy of the table with the name MyTable_Deleted And, right before deleting the current record, copy the record and stick it into MyTable_Deleted.

Was thinking of adding a new field to mark as deleted, but that would lead to all the associated reports to update and re-test.

Just wondering if anyone has some "best ideas" or code to point me to?
Would rather learn from others good deeds. Any good considerations would be greatly appreciated.
 

Rx_

Nothing In Moderation
Local time
Yesterday, 22:44
Joined
Oct 22, 2009
Messages
2,803
After searching this site, there are several good suggestions. Below is a modified copy of one solution
The insert query get the current records primary key from a linked text box. Mine is hidden on the form and is an autocounter in the table.
My hope is to build a more generic function that passes in the existing table name and the cloned (table_Deleted) name and leave out the text box. The commented code is an idea to take the current record and get the first field's value that way.

Is this something worth looking into, or should I just use my hidden text box?


Code:
dim sAppendSql as String
dim CurrentRecordPrimaryKey as long
'dim CurrentRecordPrimaryKey as dao.recordset
CurrentRecordPrimaryKey = [Forms]![YourFormNameHere]![YourTextBoxNameHere] ' Uses the forms hidden text box for autocounter
'       Alternative ?                            
' Set CurrentRecordPrimaryKey = Me.Recordset   ' Or is it Me.CurrentRecord  ?                         
'       CurrentRecordPrimaryKey = CurrentRecordPrimaryKey.Fields(0).Value  ' gets primary key from current record
 
sAppendSql = "INSERT INTO tblTheRecords_Deleted.* " & _
                   "SELECT tblTheRecords.* FROM tblTheRecords " & _
                   "WHERE tblTheRecords.int_AutoCounter = " & _
                    CurrentRecordPrimaryKey     ' formally me.PrimaryKeyField  
 
currentdb.execute sInsertSql
 
'Take the table tblTheRecords - copy & rename it as tblTheRecords_Deleted
' The first field is an autocounter - in tblTheRecords_Deleted change autocounter to a number
' The primary key for the current record is in a hidden text box
 

PNGBill

Win10 Office Pro 2016
Local time
Today, 17:44
Joined
Jul 15, 2008
Messages
2,271
What if you made a query of the table and added one field - HideRecord.

The query would display all records where HideRecord is Null.

The form that allows a deletion would use the query and rather then delete would check the HideRecord field so the next occurrence would not show this record.

If the purpose of Deleting a record was to add it again then you may have a problem.
 

Rx_

Nothing In Moderation
Local time
Yesterday, 22:44
Joined
Oct 22, 2009
Messages
2,803
This is a solution, won't say it is the best in the world.
Would enjoy any comments.

Added this function right before a confirm delete record routine.
The function lets me pass in a table name and returns a true false

In this example, the archive table is a copy of the table's structure. The first field of the _archive table had the primary key turned off to make testing easier. It also had a field added at the end with a default value of Now() for time stamping.

If your tables had different field names, or if you wanted a case statement where the value in one field gets changed as it moves to the archive table, this allows for relatively good documentation.

I created a function (not shown) that printed each field name with the data type and comments in the debug window. Then all I had to do was copy and paste the results after each me.Recordset! <paste field name here> So, in ten minutes, the 25 fields were ready and worked first time.

One improvement might be to wrap everything into a transaction of some type.

Code:
Function CopyCurrentRecordToArchive(ArchiveTableName As String) As Boolean
'   For current record - copies field by field to archive record - pick individual fields you want to copy
'   Use - righ before a Delete of the current record - run this to create a copy of the record in a Table_Archive
'       Note: this is useful when some process is run on the Me.recordset!Fieldname side - e.g. change "Approved" to "Cancel"
'   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()
'   for Table_Archive, create a query that selects all records
'   eg SELECT tblAPD_Fed_ST_CO_Archive.* FROM tblAPD_Fed_ST_CO_Archive;
'  not used -  example if outside databaes SELECT * FROM a ForeignTable IN "D:\Databases\ForeignDatabase.mdb";
'  Save the text in a query (SQLArchiveTable).
'  Using VBA code identify the fields - they should be the same name on each side since the table structure was copied
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
On Error GoTo Error_CopyCurrentRecordToArchive
  SQLArchiveTable = "SELECT " & ArchiveTableName & ".* FROM tblAPD_Fed_ST_CO_Archive;"
'  Open the _archive table
  Set dbArchive = CurrentDb()
  Set rstArchiveTable = dbArchive.OpenRecordset(SQLArchiveTable, dbOpenDynaset)
'   Add a new record to the archived table, move the fields from the current record on the form to the table_archive and save the row
  rstArchiveTable.AddNew
  rstArchiveTable![intID_APD_FedStCo] = Me.Recordset!intID_APD_FedStCo
  rstArchiveTable![Dt_APD_Sub] = Me.Recordset!Dt_APD_Sub        'Date/Time     from tblAPD_Fed_ST_CO_Archive.Dt_APD_Sub  Validation Rule: >#1/1/1940#
  rstArchiveTable!Dt_APD_Apv = Me.Recordset!Dt_APD_Apv          'Date/Time     from tblAPD_Fed_ST_CO_Archive.Dt_APD_Apv  Validation Rule: >#1/1/1940#
  rstArchiveTable!Dt_ApprovalCancled = Me.Recordset!Dt_ApprovalCancled  'Date/Time     from tblAPD_Fed_ST_CO_Archive.Dt_ApprovalCancled        Validation Rule: >#1/1/1940#
  rstArchiveTable!Dt_APD_Exp = Me.Recordset!Dt_APD_Exp         'Date/Time     'from tblAPD_Fed_ST_CO_Archive.Dt_APD_Exp  Validation Rule: >#1/1/1940#
  rstArchiveTable.Update
'     Close the archive database
  rstArchiveTable.Close
  Set rstArchiveTable = Nothing
  Set dbArchive = Nothing
  CopyCurrentRecordToArchive = True
Exit Function
     Error_CopyCurrentRecordToArchive:
Err.Clear
  CopyCurrentRecordToArchive = False
End Function
 

Rx_

Nothing In Moderation
Local time
Yesterday, 22:44
Joined
Oct 22, 2009
Messages
2,803
Buddy Code for the Function Above:
This code creates all the code between the AddNew and Update for the example above. Change the table name and run in the immediate window.
The Immediate Window will generate the results.
Being too old to remember things, my preference is to add comments.
So, the generated code even includes comments for the field rules.
I plan to re-use the code above in about 20 tables.
From there, it is somewhat easy to modify specific fields for the many business rules the customer throws at me on a daily basis.

Basically -
- Create a copy of your table as table_Archive
- Copy the funciton into your form
- Call the funciton before your delete record process
- Use this code (update your table name) to generate all the code that can be pasted between the AddNew and Update statement in the function shown above.


Code:
Function ShowFieldsRS(strTable)
          'Purpose:   Run in Immendiate window - writes code for the recordset with some comments for date fields rules
          'Usage:    in immediate window - change the table name and run:  Call ShowFieldsRS("tblAPD_Fed_ST_CO_Archive")
          ' Paste the results in the code example above
          Dim rs As dao.Recordset
          Dim fld As dao.Field
          Dim strSQL As String
 
10        strSQL = "SELECT " & strTable & ".* FROM " & strTable & " WHERE (False);"
20        Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
30        For Each fld In rs.Fields
40            If FieldTypeName(fld) = "Date/Time" Then
50                Debug.Print "rstArchiveTable!" & fld.Name, "= Me.Recordset!" & fld.Name, "   '" & FieldTypeName(fld), "from " & fld.SourceTable & "." & fld.SourceField, "Validation Rule: " & fld.ValidationRule
              Else
              Debug.Print "rstArchiveTable!" & fld.Name, "= Me.Recordset!" & fld.Name
60            End If
70        Next
80        rs.Close
90        Set rs = Nothing
End Function
 
Last edited:

bluegno

Registered User.
Local time
Yesterday, 21:44
Joined
Nov 30, 2005
Messages
27
The INSERT INTO solution seems to work well... *if* performance isn't an issue. I was copying each field value individually, and tried replacing it with this more generic solution. The functionality is correct, but unfortunately, my 30 minute task now takes over 24 hours to run.
 

Users who are viewing this thread

Top Bottom