Prev: Resource For Help With Access
Next: Subreport Keeps Repeating Itself
From: Dennis on 31 May 2010 01:36
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

 | 
Pages: 1
Prev: Resource For Help With Access
Next: Subreport Keeps Repeating Itself