Creating License Numbers

[hughed00]

I have worked with SQL to deliver reports in the past, but have never had to create anything more than a simple data imput form.

Currently, I have been given the task of creating in Access2K a form which allows for data imput (got that part OK) and then creates a new EAN-13 license for the record.

The license, for those not familiar with EAN-13, consist of a purchased 7-digit license which is static and represents your organisation. The rest of the digits are up to you.

Our company uses a static 7-digit piece at the beginning, followed by 5 digits that should be unique (incremented each new license is my goal), followed by a check digit.

I assume I need a seperate table for the 5 digit part, somehow manipulate it, then bring it into the form prior to saving (I think I can figure out the modulus-10 check digit part).

So, what I am asking is for some direction as to how to get that 5 digit number and increment it correctly each time I create a new "license" in my form.

Thanks for any suggestions!!!

 

[Ziggurat]

Hi Hugh

It sounds like all you have to do is to create a key field for your record that holds the EAN-13 licence.
Then when you want to increment you find the largest value of the key and use the left most 12 characters.

You can find the largest value using an sql statement to find the max value for a field in a table.

Then increment the value of this number add you modulus 10 check digit and save it as the key for the new record.


If you need any more info let me know

*
***
*****
Ziggurat

 

[hughed00]

OKay,

Dummie time. The way the number breaks down is:

1234567 - 12345 - 1
Static - increment - mod10

What I have done since yesterday is create a field to allow the user to use a picklist lookup for the correct static license (we own about 10).

My problem (which I am sure is elemental) is how to start at 00000 and increment up each time I need to create a new record, then put that number in the second group.

Once that number is in, the mod10 will be calculated using the prior 12 numbers. This is really kinda' bogus, but cuts down on the number of end-user mistakes since we use this number for routing messages ( a little irrelevant info).

Thanks!