From: Dennis on 31 May 2010 01:36
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)
WHERE qryMemberRpt.MemNo > 0
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
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.