By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,836 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Produce Consecutive Numbers

P: 17
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.

Jan 19 '09 #1
Share this Question
Share on Google+
2 Replies

P: 234
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_count_Click()
  2. Dim counter
  3. Dim MaxNum As Integer
  5. If DMax("[Number]", "NumberTable") = 0 Or IsNull(DMax("[Number]", "NumberTable")) = True Then
  6.   MaxNum = 1
  8.     For counter = MaxNum To (MaxNum + 1000)
  9.         txt_tempnum = counter
  10.         DoCmd.GoToRecord , , acNewRec
  11.     Next counter
  13.     txt_tempnum = Nothing
  15. Else
  17. MaxNum = DMax("[Number]", "NumberTable")
  18.     For counter = MaxNum To (MaxNum + 1000)
  19.         txt_tempnum = counter
  20.         DoCmd.GoToRecord , , acNewRec
  21.     Next counter
  23.     txt_tempnum = Nothing
  24. End If
  26. End Sub
When you click the command button, it will count/add 1000 to the greatest value in your NumbersTable

Jan 19 '09 #2

Expert Mod 15k+
P: 31,489
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 :
    Expand|Select|Wrap|Line Numbers
    1. DELETE
    3. FROM [Label]
    5. WHERE [LabelNum]>1000
  2. Next populate the correct numbers into the [Label] table :
    Expand|Select|Wrap|Line Numbers
    1. INSERT INTO [Label] ([LabelNum])
    3. SELECT [LabelNum]+Forms('YourForm').txt_TempNum AS [NewLabelNum]
    5. FROM [Label]
    7. WHERE [LabelNum]<=1000
  3. Run your report directly off the [Label] table. Ensure you invoke it with a filter where :
    Expand|Select|Wrap|Line Numbers
    1. [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.
Jan 20 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.