A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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

Thread Tools Display Modes
Old May 31st, 2010, 06:36 AM posted to microsoft.public.access.queries,microsoft.public.access.reports
external usenet poster
Posts: 1,222
Default 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.




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

All times are GMT +1. The time now is 03:57 AM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.