PC Review

Thread Tools Rate Thread

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

Posts: n/a
      31st May 2010

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.



Reply With Quote

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
ESET goes further and further down hill Kulin Anti-Virus 0 5th Mar 2012 11:19 AM
Allen Browne's Has the Rcd been Printed - Taking it further Dennis Microsoft Access Queries 0 31st May 2010 06:36 AM
Further query for Allen Browne =?Utf-8?B?Q1c=?= Microsoft Access 3 7th Jan 2007 12:09 PM
Security problem?? just set up RCD =?Utf-8?B?QmlsbA==?= Windows XP Work Remotely 1 6th Feb 2005 11:33 AM
RCD port Lynn Windows XP Networking 1 17th Jan 2004 10:30 AM




All times are GMT +1. The time now is 11:21 PM.