Printing duplicates records based on number value entered into field 1

[stevo1961]

Hi,
I have a Main form (Jobs) and subform (Delivery Docket) 1:M that runs the following code behind a print button. The code essentially takes the line record or all line records from the subform (Delivery Dockets Table) and appends this to a temp table called DeliveryDocketTemp Table. Now depending on the the number entered in field name "LabelCount" it will duplicate the line record or lines of records based on this value. Codes works fine if you only run this code for 1 line of record on the subform. But if I try to run the code to append all subsequent lines on the subform the number of records appended in the temp table don't add up, except when the number entered in the "LabelCount" field are even numbers. Example, if I enter 1 in "LabelCount" field of the first line and 2 in "LabelCount" field of the 2nd line it appends line record of line 1 three times and line 2 three times in the temp table. But if I enter 2 in "LabelCount" fields of both lines no problem at all it will append both lines of records twice. Seems to only work if the number entered in the "LabelCount" fields in all lines are entered same.

DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteDeliveryTemp", acNormal, acEdit
For I = 1 To Forms!Jobs![Delivery Dockets].Form!LabelCount
DoCmd.OpenQuery "AppendDeliveryTemp", acNormal, acEdit
Next
DoCmd.SetWarnings True
stDocName = "Labels"
DoCmd.OpenForm stDocName

Perhaps, there is another way to write this code so that it duplicates or appends the line of records based on the numerical value entered in the "LabelCount" field. Any assistance would be greatly appreciated.

 

[dhookom]

Do you really need duplicate records or only multiple copies of the same record? I used a table of numbers [tblNums] with a numeric field [Num]. Add numbers 1 through the maximum number you will ever need. Add this table to your report's records source query with no join lines. Then set the criteria under the [Num] field to

<=[LabelCount]

This will produce multiple copies of each record.

Duane
Hook'D on Access
MS Access MVP

 

[stevo1961]

Hi Duane,

Worked like a charm...appreciate your reply
Thanks very much.....