OfficeFrustration (
-   Setting Up & Running Reports (
-   -   Allen Browne's Has the Rcd been Printed - Taking it further (

Dennis May 31st, 2010 06:36 AM

Allen Browne's Has the Rcd been Printed - Taking it further

I'm using Allen Browne's code from Has the Rcd been Printed and all of that
is working great. I'm now trying to implement the Taking it Further - Track
each time a record is printed. (This has been cross posted to queries. I
only did this because there is only one day left on this forum.)

I am trying to write an SQL statement that will Copy the keys from member
table to member print audit table and set a value in the member print audit

Here is SQL like statement that I want to do:

INSERT INTO tblBatchMember (AcctNo, SET BatchID = 999)
SELECT qryMemberRpt.MemNo
WHERE qryMemberRpt.MemNo 0
FROM qryMemberRpt;

I tried to do the above, but the Query Builder would not let me do it. What
is the proper way to write this statement.

- This SQL will be run from inside a report.
- tblBatchMember’s primary key is an autoassigned number by Access.
- qryMemberRpt is a query over the tblMember.

What I want the SQL statement to do is:

1. Select all members from the qryMemberRpt with AcctNo 0 (in production
that will be other criteria)
2. Set the tblBatchMember.AcctNo = qryMemberRpt.MemNo
3. Set the tblBatchmember.BatchId = an previously generated batch number
4. Write the results to the tblBatchMember table.

The only way I can see doing this is with three queries (from within my VBA

1. UPDATE tblMember SET BatchID = " & lngBatchID & " WHERE BatchID Is Null
2. INSERT INTO tblBatchMember ( AcctNo, BatchId )
SELECT tblMember.MemNo, tblember.BatchId
FROM tblMember;
3. UPDATE tblMember SET BatchID = ‘’ WHERE BatchID = “ & lngBatchID

Can I set a field to Null with Query or do I set it to ""?
If I can only set a field to"", then I can change the initial WHERE to WHERE
Nz(BatchId,"") = ""

The last query is needed because next time I run the report, I want the
member audit trail to be updated again;

Can anyone suggest a better or more efficient SQL statement? I’m going to
use this as a model for all of the other report that I have to apply this to.
So I would like to do it right the first time.

Once again, thanks to EVERY ONE who has been so kind to me. I stumbled on
this group short after learning how to spell A-C-C-E-S-S. The people on
this forum have helped me SO MUCH. Thanks again. Hopefully I will see you
on the other forums.

God bless.



All times are GMT +1. The time now is 09:05 AM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.