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

Hi,

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
table.


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
code):

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.


Thanks,


Dennis