Solved

Create a list from 2 numbers

Posted on 2013-11-11
13
227 Views
Last Modified: 2013-11-11
I need to create a list based on 2 numbers

1st Number = 10301(BatchID)
2nd Number = 206001 (BadgeNo)

I want to create a table with the above 2 columns,

10301 will be added 20 times with 206001 incrementing until it reaches 206020

It will then start again with 10302 added 20 times with 206021 incrementing until it reaches 206040 and so on...

10303 - 206041
10303 - ..........
10303 -206060

Below is an example how the 1st two would look like.


BatchID  BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020

10302      206021
10302      206022
10302      206023
10302      206024
10302      206025
10302      206026
10302      206027
10302      206028
10302      206029
10302      206030
10302      206031
10302      206032
10302      206033
10302      206034
10302      206035
10302      206036
10302      206037
10302      206038
10302      206039
10302      206040
0
Comment
Question by:Brogrim
  • 8
  • 4
13 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39638397
You haven't specified how high you want to increment the first number (you have shown two increments), but this is the general approach.  Change the "For j = 2" to go as many times as you need the first number incremented.

Dim lng1, lng2 as long
Dim i, j as integer

lng1 = 10301 
lng2 = 206001

For j = i to 2

For i = i to 20 
    currentDB.execute "INSERT INTO YourTable (Field1, Field2) VALUES (" & lng1 & ", " & lng2 & ")", dbfailonerror
     lng2 = lng2 + 1
Next

lng1 = lng1 + 1

Next

Open in new window

0
 

Author Comment

by:Brogrim
ID: 39638407
Ist number is incremented by 1

how do I execute this, do i create a form and add this code to a command button
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39638413
Miriam,

don't you need to reset the value of  the variable 'i' after exiting the inner loop?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Brogrim
ID: 39638419
I have created the form and your suggestion is working but needs some editing

When i execute I am getting the following:

BatchID      BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020
10301      206021

I want it to stop at :
10301      206020

and then continue on
BatchID      BadgeNo
10302      206021

I need 13,000 records like above
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638437
Ok - then try this (I mistakenly used i as my starting increment instead of 1)

Dim lng1, lng2 as long
Dim i, j as integer

lng1 = 10301 
lng2 = 206001

For j = 1 to 2

For i = 1 to 19 
    currentDB.execute "INSERT INTO YourTable (Field1, Field2) VALUES (" & lng1 & ", " & lng2 & ")", dbfailonerror
     lng2 = lng2 + 1
Next

lng1 = lng1 + 1

Next

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638445
<< I need 13,000 records like above >>

Still just using two groups of the first number for testing.  You can change the 2 in "For j= 1 to 2" to however many groups you need after verifying that the groups are what you need.

Also, delete all the records before testing this a second time so that you're starting with an empty table.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638450
<<don't you need to reset the value of  the variable 'i' after exiting the inner loop?>>

Dale, no.. he needs to continue numbering where he left off for each subsequent group of numbers (they dont get restarted)

EDIT
Ahh... I see what you mean.  That i = i to 20 was a typo.  should have been i = 1 to 20 (I corrected that above)
0
 

Author Comment

by:Brogrim
ID: 39638580
Getting there, that is now working and creating the correct sequence

BatchID      BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020

You can change the 2 in "For j= 1 to 2" to however many groups you need after verifying that the groups are what you need.

the above is making 2 lists but both with the same BatchID and BadgeNo's

BatchID      BadgeNo
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020
10301      206001
10301      206002
10301      206003
10301      206004
10301      206005
10301      206006
10301      206007
10301      206008
10301      206009
10301      206010
10301      206011
10301      206012
10301      206013
10301      206014
10301      206015
10301      206016
10301      206017
10301      206018
10301      206019
10301      206020
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638650
Did you delete all the records in the table before testing it again?

I'm getting the correct results...

 10301         206001 
 10301         206002 
 10301         206003 
 10301         206004 
 10301         206005 
 10301         206006 
 10301         206007 
 10301         206008 
 10301         206009 
 10301         206010 
 10301         206011 
 10301         206012 
 10301         206013 
 10301         206014 
 10301         206015 
 10301         206016 
 10301         206017 
 10301         206018 
 10301         206019 
 10301         206020 

 10302         206021 
 10302         206022 
 10302         206023 
 10302         206024 
 10302         206025 
 10302         206026 
 10302         206027 
 10302         206028 
 10302         206029 
 10302         206030 
 10302         206031 
 10302         206032 
 10302         206033 
 10302         206034 
 10302         206035 
 10302         206036 
 10302         206037 
 10302         206038 

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39638656
Try this - it deletes the records in the table before creating a new list:


Dim lng1, lng2 as long
Dim i, j as integer

CurrentDB.Execute "DELETE * FROM YourTable", dbFailOnError

lng1 = 10301 
lng2 = 206001

For j = 1 to 2

For i = 1 to 19 
    currentDB.execute "INSERT INTO YourTable (Field1, Field2) VALUES (" & lng1 & ", " & lng2 & ")", dbfailonerror
     lng2 = lng2 + 1
Next

lng1 = lng1 + 1

Next

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638674
If that doesn't help, post the code exactly as you have implemented it in your database.
0
 

Author Closing Comment

by:Brogrim
ID: 39638744
Dont know what you changed here (if anything). I removed the auto number this side and all worked fine.

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39638765
Glad to help out.  The only thing I added was the Delete to clear the table before adding the records.  I'm just guessing that you had duplicates from separate trials of the code.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now