473,320 Members | 2,158 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Jan 19 '09 #1
2 6984
MindBender77
234 100+
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
  4.  
  5. If DMax("[Number]", "NumberTable") = 0 Or IsNull(DMax("[Number]", "NumberTable")) = True Then
  6.   MaxNum = 1
  7.  
  8.     For counter = MaxNum To (MaxNum + 1000)
  9.         txt_tempnum = counter
  10.         DoCmd.GoToRecord , , acNewRec
  11.     Next counter
  12.  
  13.     txt_tempnum = Nothing
  14.  
  15. Else
  16.  
  17. MaxNum = DMax("[Number]", "NumberTable")
  18.     For counter = MaxNum To (MaxNum + 1000)
  19.         txt_tempnum = counter
  20.         DoCmd.GoToRecord , , acNewRec
  21.     Next counter
  22.  
  23.     txt_tempnum = Nothing
  24. End If
  25.  
  26. End Sub
  27.  
When you click the command button, it will count/add 1000 to the greatest value in your NumbersTable

HTH,
Bender
Jan 19 '09 #2
NeoPa
32,556 Expert Mod 16PB
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
    2.  
    3. FROM [Label]
    4.  
    5. WHERE [LabelNum]>1000
  2. Next populate the correct numbers into the [Label] table :
    Expand|Select|Wrap|Line Numbers
    1. INSERT INTO [Label] ([LabelNum])
    2.  
    3. SELECT [LabelNum]+Forms('YourForm').txt_TempNum AS [NewLabelNum]
    4.  
    5. FROM [Label]
    6.  
    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

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

Similar topics

8
by: Adam | last post by:
Hi, I am trying to mark consective numbers in a data set and get a count as to how many consecutive numbers exist in the a given line of data. Here is an example line: 3, 5, 7, 9, 10, 13,...
10
by: ChrisD | last post by:
I'm trying extract a count of consecutive numbers, or "unbroken" years in this case, at any particular given time. For example (simplified): CREATE TABLE #Customers ( CustNo INT, YearNo...
2
by: Iwilfix | last post by:
I am completly new to Access. I need a field to automaticly enter invoice numbers that run consecutive. For example records starting with an invoice # 1276, would autmaticly go to #1277 and #1278...
0
by: Dennis Ruppert | last post by:
Greetings This should be easy, but I am stuck! I have a table that I import from another program. There are 25 fields, but I only need to use 3 of them for what I need to do. After I import...
9
by: boliches | last post by:
I have a seperate table to generate consecutive numbers. Using "Dmax" to find the largest number to increment . My problem is that I want the number to begin at 1000 at the start of each month,...
11
by: xctide | last post by:
This project will give you more experience on nested for loops and user-defined functions. The original project came from an IT company’s programming test where they were asking for the most...
7
by: Sharkie | last post by:
I need a regular expression which will evaluate to false if number of consecutive characters (non-whitespace) exceeds certain number (10 in this example). For example, I have this function: ...
8
by: help2008 | last post by:
Hi I have been doing this working on an assignment for the last week and have stumbled across a part which I cant get my head around. I was hoping that someone could explain what I am missing. I...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.