Produce Consecutive Numbers

[bwesenberg]

I am not sure how to explain this so I will try to the best of my ability.

I need to be able to produce 1000 labels from access that are based on consecutive numbers that Access will produce.

Here are some of the criteria they are looking for:

• Need to be able to produce labels on demand – generating at least 1,000 labels at a time.
• The program will know what the last number used was so when they print the next batch of labels it will automatically know.

Here is what I have so far:

• A table called NumberTable with one field called Number set at a Number Data Type.
• I have a query called AddNumberQuery that is based off the table. It has the Number field in it and a calculated field which is Number +1.

What I think I need to do is create come kind of Loop to run that query over and over 1000 times and append that data to a table.
My problem is – is that I am not the greatest at code and could use some assistance.

If you have an easier way to do this I would welcome any suggestions. Once I have the data created I can create the report on my own.

Thanks
bwesenberg

[MindBender77]

The easiest way to accomplish this is to create a form with its recordsource linked to your table (NumberTable).

On this form add a single textbox and a command button. Naming the textbox (txt_tempnum) and the command button (cmd_count). Next, change the controlsource of the textbox to the Number field of your table. Finally, add this line of code to the OnClick event of your command button:

Private Sub cmd_count_Click()
Dim counter
Dim MaxNum As Integer
 
If DMax("[Number]", "NumberTable") = 0 Or IsNull(DMax("[Number]", "NumberTable")) = True Then
  MaxNum = 1
 
    For counter = MaxNum To (MaxNum + 1000)
        txt_tempnum = counter
        DoCmd.GoToRecord , , acNewRec
    Next counter
 
    txt_tempnum = Nothing
 
Else
 
MaxNum = DMax("[Number]", "NumberTable")
    For counter = MaxNum To (MaxNum + 1000)
        txt_tempnum = counter
        DoCmd.GoToRecord , , acNewRec
    Next counter
 
    txt_tempnum = Nothing
End If
 
End Sub
 

When you click the command button, it will count/add 1000 to the greatest value in your NumbersTable

HTH,
Bender

[NeoPa]

I would start by renaming the field [Number] in your [NumberTable] table to something like [MaxNum]. Using a reserved word is always risky. It's possible to avoid problems with it, but why make life more difficult than it need be?

I would consider then creating a separate table (named [Label] possibly) which would only contain the first thousand records plus the data required for a particular label run (At least a numeric field [LabelNum]. Should be PK). I'll assume for now that this is a single-user process and that it will never be run twice (or more) at the same time.

Ensure that this table is populated with the first 1,000 records (0 < LabelNum <= 1,000). This can be done manually or however you like. It need only be run once ever.

Using the form already described by MindBender (Thx MB), put code behind cmd_Count that will accomplish the following :

1. Start off by clearing down any previous run of the process :
   

   DELETE
    
   FROM [Label]
    
   WHERE [LabelNum]>1000

2. Next populate the correct numbers into the [Label] table :
   

   INSERT INTO [Label] ([LabelNum])
    
   SELECT [LabelNum]+Forms('YourForm').txt_TempNum AS [NewLabelNum]
    
   FROM [Label]
    
   WHERE [LabelNum]<=1000

3. Run your report directly off the [Label] table. Ensure you invoke it with a filter where :
   

   [LabelNum]>=Forms('YourForm').txt_TempNum

4. When you're happy the labels have printed off correctly update the value in [NumberTable] (Can be done via the form if preferred). If done directly to the table ensure the form is '.Requery'ed.
5. Rerun the DELETE query above and return control back to the form.