1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access: Multiple copies of pages within a Report

Discussion in 'Business Applications' started by Alchemi, Sep 13, 2018.

Advertisement
  1. Alchemi

    Alchemi Thread Starter

    Joined:
    Sep 13, 2018
    Messages:
    6
    First Name:
    Chris
    Hello,

    This thread touched on what I'd like to do but went into a different direction:
    https://forums.techguy.org/threads/...nt-multiple-times.1212146/page-2#post-9529585

    I am so close on completing my system except for one nagging problem. I would like to generate labels for our shipping boxes. If Customer wants one of Product A, I can do that. If Customer wants ten of Product A, I could make ten copies. If Customer wants ten of Product A and ten of Product B, the report (shipping label) is all ready to go as two pages, and I'd make ten copies. The problem is, let's say Customer wants ten of Product A and 5 of Product B. I haven't been able to figure this out yet.

    The number of labels I would need to create is in the query used to generate the report, field "Quantity." The shipping form calls up the report, which pulls the data from the query. As an example, I've got a report that is complete for each of the seven products to be shipped, but the report shows only one label per product, as I can't figure out where to apply the "Quantity" field to replicate things.

    I'm not a VBA programmer but I feel I understand enough to where I could figure out where things should go. From the earlier thread the notion that there's a way to apply a counter drawing from the query gives me hope in solving my problem. I feel like I have a six pack of ideas and I just need the thingy to hold the whole thing together. Help is much appreciated, thanks in advance.
     
  2. Alchemi

    Alchemi Thread Starter

    Joined:
    Sep 13, 2018
    Messages:
    6
    First Name:
    Chris
    OK, found some code that's helped me out. I've re-written the Print command from an embedded macro to an event procedure in VBA. Cribbing from other sources, I've been able to decipher where I can print the first page a couple of times. So, now my problem is this:

    I think what I need is a loop, but like I said, I'm a very novice coder. I would like the loop to read the first line of the query, use "Quantity" as the value for (Copies) in the DoCmd.PrintOut command line, and then loop back to the next line of the query, and continue to loop until all query lines are read.

    I believe I need a DoWhile loop. Like I said earlier: lot of ideas, just need some help sticking them together.
     
  3. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    97
    First Name:
    Jack
    Hello Alchemi,

    If using the Cartesian Product approach will not work for you, then OBP can probably VBA something up! Do you have a database with sample data that we could use to evaluate?

    Thanks
     
  4. Alchemi

    Alchemi Thread Starter

    Joined:
    Sep 13, 2018
    Messages:
    6
    First Name:
    Chris
    Thanks!

    I had to clear out some company confidential stuff but otherwise the database with the shipping label program is here. Client list and product list is redone with an example customer and product. Hopefully it's self-explanatory once you try to ship something and make a label.

    Cartesian Product: I took a look at your reference, seems to be a lot like Excel's sumproduct with which I am more familiar, like a cross-array relationship. Not saying it won't work, I've never tried that in Access.
     

    Attached Files:

  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    I would have thought that the code that I provided in the post you referenced would do what you want.
    Another method would be to create a temporary Print Table, so the table would have 10 product A records added to it and 5 B Records added to it.
    A Report based on a query of the table would then print out the Labels for each record and then run a delete query to clear the table ready for a new run.

    ps Chris, can I suggest that you take a look at "Tabbed Forms" to bring your forms together.
     
    Last edited: Sep 14, 2018
  6. Alchemi

    Alchemi Thread Starter

    Joined:
    Sep 13, 2018
    Messages:
    6
    First Name:
    Chris
    Hello OBP and thank you for looking at my problem. I've been reviewing what you did for the other thread, and because it went off into subreports I got confused as to what code I should utilize.

    I'm on the 7/25/18 post and found the code at the Form_Form1 under the Preview Report button. It appears you set bookmarks in the table and at each line you have it execute a print command based upon the value set in Printqty. Do I have this right?

    I've got five buttons (portals to me) on my Main Page and one is a Close. I'm pretty close to the point where if I do have to add another button I do plan on tabbing the forms into a clearer picture. I see a Shipping portal, an Admin portal, and a Quality portal in the future.
     
  7. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    97
    First Name:
    Jack
    Hello Alchemi,

    In the event that OBP’s code has not sorted you out, I put together an example of the Cartesian Product that I referenced (see attached), which may give you the desired results, or hopefully point you in the desired direction. Here are the steps that I implemented.
    1. Created a stand-alone table named [PrintLabel]… Important! No linking to this table!
    2. Created only ONE field named [PrintCount].
    3. Pre-defined the [PrintCount] field with entries of 1 – 20. Important! You will need to increase the number of numeric entries to handle the number of expected labels to be printed.
    4. Removed the Auto-Number field and made the [PrintCount] field the Primary Key.
    5. Added the new table named [PrintLabel] into your query named [Order_tbl Query]… Important! No linking to this table within the query!
    6. Added the new [PrintCount] field to the to the query named [Order_tbl Query] with a condition statement of “<=[Quantity]” referencing your quantity field.

    Good luck!
     

    Attached Files:

  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    Yes you have read the code correct, so in your case it would probably be easier to use the Print Table method.
    Let me know which way you want to try.
     
  9. Alchemi

    Alchemi Thread Starter

    Joined:
    Sep 13, 2018
    Messages:
    6
    First Name:
    Chris
    I appreciate all the work folks have done to help me, really do

    OBP, I think the code you have would be absolutely perfect if only I understood how bookmarks work. I'm going to try to use this on the side as a learning tool. So as always, I've got something to learn. "Komerex tel khesterex" as they say...

    In the meantime, I'm trying out Chawbacon's solution. It works as intended, generating the number of labels as requested in the order table. There's just one baffling problem: If the total number of labels exceeds 10, it stops at 10 total. It doesn't matter if the 11th label is partially printed from it's group or if it signals a new label within the order, it just stops. If the total is less than 10, all's well. I'm beta testing by making pdfs, haven't tried to actually make a label yet, so that's an unknown.

    Going back over your notes, you mentioned about the expected number of labels being more than 20, but I'm hitting the roadblock at 10. Weird.

    I'm going to go back in to the [Print Label] table and increase it to 100. I'll report back what happens.
     
  10. Alchemi

    Alchemi Thread Starter

    Joined:
    Sep 13, 2018
    Messages:
    6
    First Name:
    Chris
    Well, that was...instructive. Changed the printer from pdf writer to an actual printer and the whole thing printed out exactly as planned. Luckily I had the printer paused when I requested 100 labels...

    I'll have to designate a printer for the label command but other than that it looks like my problem is resolved, thank you very much! Please allow me to continue testing this for about a week before I log the thread as solved.
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,667
    Well done.
    If you mean the LastBookmarked in the recordset, it just means the last record accessed in the recordset.
     
  12. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    97
    First Name:
    Jack
    Glad to hear that you are making forward progress.

    OBP's coding solution should work fine also. OBP creates VBA solutions that cannot be accomplished within the native Access environment and I tip my hat to OBP accordingly. The route that you choose should really be focused on which solution you are more comfortable supporting down the road when future modifications are required. ;)

    Good luck with either avenue.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1216469

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice