# Create an ascending numberic value.

Hi,
I receive voucher data with around 200,000 - 1,000,000 records and need to build tables for processing. The only data I receive is a random ascending voucher or coupon Id Like so....RDP000333555. Based on how many of these vouchers will be placed in a sample kit...5 or 10 etc..I need to build a control number. The example is based on 55,000 records with 5 per kit with equals 11,000 kits. This example is the first kit.

Vouchers:            Voucher ID                    Kit Control Number
(Individual vouchers)    (numer on the outside of the kit)
RDP000000001     000001A                       000001
RDP000000009     000001B                       000001
RDP000000128     000001C                       000001
RDP000002223     000001D                       000001
RDP000003698     000001E                       000001

Some of these jobs run together and I need to start the numbers at a different point instead of always at 000001. Also I need these padded with "0" to 6 Positions.

What I would like to have is fill out a form with the amount of vouchers per kit and the starting number and have the code generate and attach the numbers to the vouchers.
Records to process: 200,000
Vouchers Per Kit: 5
Starting Number: 000001

Ken
LVL 2
###### Who is Participating?

x

Commented:

replace the codes with this

``````Private Sub Command6_Click()
If IsNull(Me.txtRecords) Or IsNull(Me.txtVouchersPerKit) _
Or IsNull(Me.txtStartingNumber) Then
MsgBox "Fill out all the textboxes"
Exit Sub
End If
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim strVouchers, strKit, sql, j, x, i
Set rs1 = CurrentDb.OpenRecordset("tblVoucherKit")
x = Me.txtStartingNumber
sql = "select top " & CLng(Me.txtRecords) & " [vouchers] from tblvoucher order by vouchers"
Set rs = CurrentDb.OpenRecordset(sql)
j = 0
rs.MoveFirst
For i = 1 To CLng(Me.txtRecords)
j = j + 1
rs1!vouchers = rs!vouchers
rs1![Voucher ID] = Format(x, "000000") & Chr(64 + j)
rs1![Kit Control Number] = Format(x, "000000")
rs1.Update
If j = CLng(Me.txtVouchersPerKit) Then
j = 0: x = CLng(x) + 1
End If
If Not rs.EOF Then
rs.MoveNext
Else
Exit For
End If
Next

rs.Close
rs1.Close
End Sub
``````
0

Commented:
>and have the code generate and attach the numbers to the vouchers.<  meaning?
0

Programming ManagerAuthor Commented:
Hi,
Thanks for the reply.

I need to somehow using vba to create the voucher id and kit control based on the parameters....5 vouchers for one kit starting with the number at wherever I need to start. The table unfortunately does not have the suffiect number to help in this process.

Vouchers:            Voucher ID                    Kit Control Number
(Individual vouchers)    (numer on the outside of the kit)
RDP000000001     000001A                       000001
RDP000000009     000001B                       000001
RDP000000128     000001C                       000001
RDP000002223     000001D                       000001
RDP000003698     000001E                       000001
RDP000004444     000002A                      000002
RDP000005555     000002B                      000002
ETC....

Thanks again

Ken
0

Commented:
see this sample db

db1.mdb
0

Commented:
Saw nothing about creating VoucherID in the question.  Are you saying you receive a batch of Vouchers (RDPno's) and instructions to batch in groups of n (5 to  whatever), where the batch numbers from 100,000 to 1,000,000 records or so and you need to 'package' the groups with a VoucherID and a KitControlNo?  ie.

Vouchers
=======
VoucherNo

selects into:

BatchVouchers
==========
BVID - autonumber
VoucherNo  -  foreign key from Vouchers
VoucherID - generated value starting at "000001" & consecutive letter from A up to the kit size
KitControlNo - generated value starting at "000001" and incrementing for each batch.

??
0

Commented:
Nice work Rey.  I thought each kit contained 5, or whatever the number voucher numbers, and we had to supply the VoucherID and the KitNo?

0

Programming ManagerAuthor Commented:
Sorry If I am being vague....

capricorn1, that is almost what I need.... nice job....The voucher table is what I start with, in your example 100 vouchers. I do not need the vouchers to repeat just add what you did for the voucher id and the kit control number.
Vouchers                    Voucher Id              Kit Control Number
RDP000000001            000001A                 000001
RDP000000002            000001B                 000001
RDP000000003            000001C                 000001
RDP000000004            000001D                 000001
RDP000000005            000001E                 000001
RDP000000006            000002A                000002
RDP000000007            000002B                000002
RDP000000008            000002C                000002
RDP000000009            000002D                000002
RDP000000010            000002E                000002

The voucher table began with 100 records the result table has 500 records, the result should only have the original 100 records with the other information laid in.

Thanks

Ken
0

Commented:
what did you do to have 500 records?
0

Programming ManagerAuthor Commented:
Hi,

When I ran the program:

Records to process 100 ( the voucher table had 100 records)
voucher per kit 5
starting number 1

the tblvoucherkit had 500 records and the vouchers were repeating for each kit. The vouchers should not change just add the voucher id and kit control to the existing vouchers for 100 records. Try the program using the spec I entered.

Thanks again

Ken
0

Commented:
because you are using the same vouchers...

in real time processing of your vouchers (  around 200,000 - 1,000,000 records )
what do you do with the vouchers after you run a single process of

Records to process: 200,000
Vouchers Per Kit: 5
Starting Number: 000001

also, where do you store and retrieve the (  around 200,000 - 1,000,000 records ) ?
0

Programming ManagerAuthor Commented:
Hi,
Thanks again.

In real time processing I received between 10,000 and 1,000,000 records each are individual voucher numbers. I then, depending on the project have to create manifest files based on our fulfillment.
Ok one example: I received 55,000 vouchers and the job consists of 5 individual vouchers per kit. The customer only wants to create 10,000 kits or use 50,000 vouchers. What I do is for every 5 different vouchers, because these are handed out to a doctor's 5 different patients, I create one voucher id and kit control number for tracking of the rep or physician. I do not need to create extra or duplicate vouchers I need to assign duplicate voucher id and kit control numbers to the existing voucher numbers. The second part works but the duplicating of the vouchers is not. Please see and example table.

Thanks again

Ken

ps. I need to explain this better sorry.
sample.JPG
0

Programming ManagerAuthor Commented:
capricorn1

Everything works perfect...Thank-you so very much.....

Ken

0

Programming ManagerAuthor Commented:
This will save alot of time, especially since I am reprogramming the whole project. Thanks Again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.