# Create a list from 2 numbers

I need to create a list based on 2 numbers

1st Number = 10301(BatchID)

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.

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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
``````
0
Information Systems Development ManagerAuthor Commented:
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
Owner, Developing Solutions LLCCommented:
Miriam,

don't you need to reset the value of  the variable 'i' after exiting the inner loop?
0
Information Systems Development ManagerAuthor Commented:
I have created the form and your suggestion is working but needs some editing

When i execute I am getting the following:

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
10302      206021

I need 13,000 records like above
0
Commented:
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
``````
0
Commented:
<< 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
Commented:
<<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
Information Systems Development ManagerAuthor Commented:
Getting there, that is now working and creating the correct sequence

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

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
Commented:
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
``````
0
Commented:
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
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
If that doesn't help, post the code exactly as you have implemented it in your database.
0
Information Systems Development ManagerAuthor Commented:
Dont know what you changed here (if anything). I removed the auto number this side and all worked fine.

Thanks
0
Commented:
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.