Share via

Sequential numbering and number of printed copies based on an access field.

Anonymous
2023-08-09T23:31:27+00:00

Hi,

I have created a report. The report will be used to generate a label or tag for labelling our product. These are a custom size to a custom printer.

I need the report to do the following functions.

  1. Print multiple pages of the same based on the value of a field. For example if the field value is ten then there will be 10 labels or tags produced.
  2. each page has 2 numbers on it that need to run sequentially. The first is a serial number. My source data has a serial number in it that might look like C23.12345, so the first page would need to say this and the second page would need to say C23.12346 etc. Also each page has a number for the box that it will be attached to. this would be 1 on the first page and then 2 on the second etc. There is also a source field for this and at times there will be a varied starting number of say 10 and we need 5 more of the tags printed. so these would sequentially go 10, 11, 12, 13, 14.

I am a complete access novice and any guidance would be muchly appreciated.

Kind Regards

Jodi

Microsoft 365 and Office | Access | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

14 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2023-08-10T01:50:17+00:00

    For the first requirement you need to use a loop. Something like:

    For i = 1 to fieldname

    DoCmd.OutputObject etc.

    Next i

    Alternatively, you could write 10 (or however many) records to a temporary table. This would work if you need each label to have a serial number.

    For the sequential numbering, you will need to separate the parts of serial number. To be able to create a serial number you will need a numeric value. You can check out my blog on Sequential Numbering.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-08-14T05:35:47+00:00

    Hi

    Sorry for the delay in my response. I have not looked at this over the weekend.

    I did not yet create a TblNums.

    I am a complete novice when it comes to this so wasn't sure how to proceed. Do I just create a table called TblNums? what fields do I need to have in this table?

    I have attached the 2 types of Labels I am looking to Create. The second screenshot - Horizontal label is our shed label that we label all of our boxes with. The Vertical label has the same 2 fields - taken from a different data source at present, but they are a slightly different application.

    On the Shed Label you will see that there is a number at the top - Currently 41. From My data source I need MS Access to pick up this number from a Field called StartLabel. It then needs to increment this number by 1 for each label that I print.

    At the bottom of this label there is a Reference Number. This number for the current year has "S23." at the start of it which I can make a fixed field and adjust for each year. But the number following it currently saying 123 should pick up from a field in my data source called StartRef and then increment by 1 for each time it is printed. I would like this field to always be five charactors long with leading zero's. So the current example would read as "00123".

    Customer Label

    Shed Label

    0 comments
  3. Duane Hookom 26,820 Reputation points Volunteer Moderator
    2023-08-11T03:55:42+00:00

    I provided information on the query composition that had a reference to “a field where the number required”.

    If you provide some actual field names, we can provide more detailed help. Did you create tblNums?

    0 comments
  4. Anonymous
    2023-08-11T01:48:09+00:00

    Thank you for your reply.

    I already have a field where the number required is showing. Do I need to reference this field?

    0 comments
  5. Duane Hookom 26,820 Reputation points Volunteer Moderator
    2023-08-10T04:17:59+00:00

    I create the multiple labels with the help of a table of numbers [tblNums] and a single numeric field [Num] with values 1 through 100 or more if needed.

    Add tblNums to your report’s recordsource and set the criteria under the Num field to <=[your quantity field here]

    This will create your multiple copies and provide a numeric value to add to the sequence.

    0 comments