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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|