Results 1 to 9 of 9
  1. #1
    sessionone is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17

    Number of records in a report of set length

    Hi all,



    I don't know where to even start with this one.

    I have a growing database. I need to be able to print reports of 30 pages. Each report should consist of 448 records.

    I added a checkbox to the table to indicate that a record has already been included into report and printed out. So, I would like to be able to check whether the record was printed, and, if not, include it into the next report. Ideally, the check should be done after a new record is added to the db, and, when the number of records reaches 448 since last printed report, I would like to have a message box saying "You can now print a new report", and when the reported is printed, checkboxes next to the records should be set to "yes" to reset counting.

    I hope this is clear.

    Thank you for your assistance.

    SessionOne

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you can use DCount() with a criteria on the checkbox field to count the unprinted records. You can use an update query to set the checkbox field after printing. This gets...messier...if you have multiple users adding records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    sessionone is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Hi,

    Ok, I am trying to think of a scenario when this gets messier.

    So far I have managed to sum false checkboxes and print report based on that. Now I am trying to find a way to mark the records, which are in the report, as true. Perhaps this is where the problems may begin: lets say number of records reaches 448, but there is no time to print the report, so the records keep adding. How can I set the 448 records, which have been printed, to true? I mean, if there are 500 records with checkboxes marked false, how can I reset the counter once the oldest 488 records have been printed.

    If this helps, my test db has four fields CustNo, CustName, CustSurname, Printed (Yes/No).

    Thank you for your input.

  4. #4
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can use the TOP predicate in the SQL query;

    SELECT TOP 488 * FROM YOUR TABLE .
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    @paul - no don't - I'm guessing

    And going off line - no broadband at home at the moment!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    sessionone is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Minty,

    You can use the TOP predicate in the SQL query;

    SELECT TOP 488 * FROM YOUR TABLE .
    THANK YOU! This seems to work, at least on the test db.

    Now, how can I mark the printed records as printed. At the moment I have a button on the form that does all of the sorting, but I can't figure out how to make the checkbox true next to the records that have been printed...

    Thanks again for your help.

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Assuming you have a unique ID field (CustNo ? ) if you try this
    Code:
    SELECT TOP 488 * FROM YourTable WHERE Printed = False ORDER BY CUSTNO Asc
    Then Run an UPDATE query based on the exact same criteria
    Code:
    UPDATE  YourTable SET Printed = True WHERE CustNo IN(SELECT TOP 488 CustNo FROM YourTable WHERE Printed = False ORDER BY CUSTNO Asc )
    Obviously this assumes you CustNo's won't change or be updated in between running the two queries.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    sessionone is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    17
    Minty,

    THANK YOU!!!! Works as expected .

    Thanks again.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-21-2015, 09:11 AM
  2. Restricting ID number length
    By allaccess in forum Database Design
    Replies: 7
    Last Post: 12-06-2013, 03:17 PM
  3. Limit number of records in report
    By aksnell in forum Reports
    Replies: 3
    Last Post: 12-05-2011, 02:31 PM
  4. Minimizing number length in queries
    By Madchemist in forum Access
    Replies: 2
    Last Post: 08-24-2011, 09:44 AM
  5. Convert number to fixed length text field
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-02-2010, 07:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums