VBA code to copy record and then delete it (1 Viewer)

[comicwizard]

I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.

what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.

I cheeted by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.

'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
DoCmd.GoToControl Screen.PreviousControl.Name
Err.Clear
If (Not .Form.NewRecord) Then
DoCmd.RunCommand acCmdDeleteRecord
End If
If (.Form.NewRecord And Not .Form.Dirty) Then
Beep
End If
If (.Form.NewRecord And .Form.Dirty) Then
DoCmd.RunCommand acCmdUndo
End If
If (.MacroError <> 0) Then
Beep
MsgBox .MacroError.Description, vbOKOnly, ""
End If
End With

Master_tbl_sub_fm_Exit:
Exit Function
Master_tbl_sub_fm_Err:
MsgBox Error$
Resume Master_tbl_sub_fm_Exit
End Function

 

[TJPoorman]

You're going to be a lot better off just running some SQL scripts. You could do something like this:

CurrentDB.Execute("INSERT INTO tblHistory SELECT * FROM tblMain WHERE ID=" & Me.ID)
CurrentDB.Execute("DELETE * FROM tblMain WHERE ID=" & Me.ID)

 

[comicwizard]

thank you for the suggestion. I will use this route instead. but would I put the code in the sub form or in the main form?

 

[TJPoorman]

You would put it on your button.

 

[MarkK]

You don't need to move records in a database. Add a date field to each record, then write a qryHistory (as opposed to your tblHistory) that only selects "old" records. Write a qryCurrent to only select current records, again, based on the date field in each record.

Store your data raw, with all same-structured records in the same table. Introduce new fields in that table to make distinctions between records. Write queries that return records based on criteria in the data. If you need to move data from table to table, you have a design flaw.

 

[comicwizard]

thanks TJ.

I guess I have been in front of the pc too long. when I put this in after click:

CurrentDb.Execute ("INSERT INTO historical_tbl SELECT * FROM master_tbl WHERE networkID=" & Me.NetworkID)
CurrentDb.Execute ("DELETE * FROM master_tbl WHERE networkID=" & Me.NetworkID)

I recieve a RUN-Time Error / Too few parameters. Expected 1.

the button is on the sub form, the filter combo box is on the main form.

 

[TJPoorman]

Is networkID your PrimaryKey field? What type is it?

 

[comicwizard]

It is a text field.

 

[TJPoorman]

Then you need this instead:

CurrentDb.Execute ("INSERT INTO historical_tbl SELECT * FROM master_tbl WHERE networkID='" & Me.NetworkID & "'")
CurrentDb.Execute ("DELETE * FROM master_tbl WHERE networkID='" & Me.NetworkID & "'")