Moving records between tables

  • Thread starter Thread starter Gareth Reynolds
  • Start date Start date
G

Gareth Reynolds

I have a database that handles client data, with two
tables; active and dormant.
I have an editor form which allows the user to edit
records in the active table. It also has 'add record'
and 'delete record' option buttons. However, I would like
to be able to add a button that will ship the highlighted
record from the active table to the dormant table (for
when we no longer act for that client).
I would be grateful if somebody could provide me with the
VB code for such an operation (I assume it's easy, but I'm
not too good with VB yet!).

Thanks in advance.
 
Why move the records to a different table? Just add a field such as
"DormantDate" Exclude any records with an entry from appearing in your
active forms and reports.

Rick


I have a database that handles client data, with two
tables; active and dormant.
I have an editor form which allows the user to edit
records in the active table. It also has 'add record'
and 'delete record' option buttons. However, I would like
to be able to add a button that will ship the highlighted
record from the active table to the dormant table (for
when we no longer act for that client).
I would be grateful if somebody could provide me with the
VB code for such an operation (I assume it's easy, but I'm
not too good with VB yet!).

Thanks in advance.
 
Here's another take on how to do this.


Public Sub MoveRecords(strCriteria As String, strTableNameSource As String,
strTableNameDest As String)
' Copies records from the source table to destination table
'then deletes the records in the source
' tables must have identical fields
' strCriteria is the logical statement used to
' select which records to move
' e.g. "[InvoiceNumber] = 1234"
On Error GoTo Err_MoveRecords

Dim dbs As ADODB.Connection, strSQL As String
Set dbs = CurrentProject.Connection
strSQL = "INSERT INTO [" & strTableNameDest & "] SELECT * FROM [" &
strTableNameSource & "] WHERE " & strCriteria
dbs.Execute strSQL ' run the append query
strSQL = "DELETE * FROM [" & strTableNameSource & "] WHERE " &
strCriteria
dbs.Execute strSQL ' run the delete query

Set dbs = Nothing ' disconnect the database

Exit_MoveRecords:
Exit Sub

Err_MoveRecords:
MsgBox Err.Description, vbExclamation, "Move failed: Error " &
Err.Number
Set dbs = Nothing
Resume Exit_MoveRecords
End Sub
 
Back
Top