-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You may wish to re-think your solution - having 2 tables of the same
data is redundant, which is a no-no in relational database design.
But, to specifically answer your question:
You should use the Form_AfterUpdate event procedure to run the append
query - 'cuz the form's AfterUpdate event only runs after a record has
been saved. Here's how I usually do it (VBA example - untested):
' Declaration section
Dim m_lngRecordID As Long
- -----
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!CheckType='A' or Me!CheckType='B' or Me!CheckType='C' Then
' Remember the current record's unique ID
m_lngRecordID = Me!CustomerID
Else
m_lngRecordID = 0
End IF
' ... other code, as required ...
End Sub
Private Sub Form_AfterUpdate()
If m_lngID <> 0 Then
' Get the ID of the last saved record,
' modify the append query & execute it.
Const QRY_APPEND = "qryAppendCustomerSales"
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(QRY_APPEND)
qd.Parameters("myID") = m_lngRecordID
qd.Execute dbFailOnError
On Error Resume Next
Set qd = Nothing
Set db = Nothing
End If
End Sub
The append query "qryAppendCustomerSales" looks like this:
PARAMETERS myID Long;
INSERT INTO tblSalesHistory (ID, SalesDate, SalesAmount)
SELECT ID, SalesDate, SalesAmt
FROM tblCurrentSales
WHERE ID = myID
HTH,
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBP/DNtoechKqOuFEgEQJs4gCfdtTvy0vIUtH4oHhwhXPxJSqgqssA oPgT
IICDtLYF0qoNaJXVsmBdkKrz
=HGBK
-----END PGP SIGNATURE-----
Paul Wagstaff wrote:
Hi there
I have 2 tables: tblAccuracy & tblClearance
Users add new records to tblAccuracy using frmRegister. Under specific
conditions I need to append the current record from frmRegister into
tblClearance. I was thinking of placing the code on the form's BeforeUpdate
event so that it will fire whether the user closes the form or attempts to
create another record.
i.e.
if field 'CheckType='A' or 'B' or 'C' then
open the tblClearance recordset
check if there's an ID number equal to the ID of the currently displayed
record on frmRegistration
if not, prompt the user to run an action query to append the current record
into tblClearance
Close the recordset
I've tried saved queries thinking of speed, but keep getting errors. I want
to do this efficiently so instead of messing around and maybe (..?!)
stumbling on a solution, can anyone tell me the correct way to approach
this. I'm confident I'd make an inefficient job of it, if left to my own
devices.
Many TIA,
Paul.
