By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Recordsets & Append query

P: n/a

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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
-----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.


Nov 12 '05 #2

P: n/a
Thanks for that...I've tried your code today with sucess! The table
structure here is poor, I know, but will have to stay 'as is' at least for
now. The client operates in a v. volatile environmernt & it'd be a bugger to
normalise these 2 tables properly.

Thanks again!

"MGFoster" <me@privacy.com> wrote in message
news:U4******************@newsread1.news.pas.earth link.net...
-----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.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.