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

Discussion in 'Microsoft Access Reports' started by Dennis, May 31, 2010.

  1. Dennis

    Dennis Guest

    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
     
    Dennis, May 31, 2010
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Pishoy

    Reply : Allen Browne

    Pishoy, Aug 27, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    142
    Allen Browne
    Aug 28, 2003
  2. Allen Browne

    Allen Browne's Access Tips - change of address

    Allen Browne, Oct 21, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    167
    Bruce M. Thompson
    Oct 21, 2003
  3. Annelie

    Allen Brown I need you -Combo Box Instructions

    Annelie, Oct 29, 2003, in forum: Microsoft Access Reports
    Replies:
    4
    Views:
    240
    Annelie
    Oct 29, 2003
  4. GREGORY HICKMOTT

    has this been printed

    GREGORY HICKMOTT, Apr 22, 2005, in forum: Microsoft Access Reports
    Replies:
    2
    Views:
    156
    fredg
    Apr 22, 2005
  5. Michael
    Replies:
    6
    Views:
    183
    Michael
    Jul 9, 2005
Loading...

Share This Page