Autonumbering with initial format

[IanMilly]

Hi,

I am pretty new to Access and learning alot all the time. I am trying to create automatically a serial numbers for certain products.

the serial number is initially dependant upon the type of electriconic Cards used in an product. ie, type 1 = A ,type 2 = B ,type 3 = C

Next in the serial number should be the month and year. Following this is a 8 digit number which should be auto-generated.

So product 1, in June 2004 should look like this

SerialNum = A060412345678

The fields i am using in are SerialNum, Date, CardType in Table1

The serial number should be consequentive after the initial type and date coding . Can anyone help explain how this can be done?

Thank you in advance

Ian

 

[suepowell]

Hi

I have done something like this, and I think the easiest way is to do what you have done and store the 3 parts of the number in separate fields and just concatinate them when you want to show the whole number

datasource for the serailnumber field would be CardType & Date & format("00000000",SerialNum) and the serialnumber as a whole would not be stored in the table.

The format is to make sure the serialnum is displayed as 8 digits

As to how to get the next number, if gaps in the numbering sequence do not matter, use an autonumber for the serialnum field, however if a record is aborted the number will be lost and a gap will be left in the sequence. If this is not acceptable, then I create my own numbering sequence.

I have a table just containing 1 number lets say 1
In my application I have a button to issue the next number, because in some cases an already issued number needs to be used, so you need to decide if the next number will be issued as soon as a new record is started, or if you want the user to actively issue the next number.

On the appropriate event open a recordset for the number stored in the table, add 1 to it and save the recordset. I assign the vale from the table to the serialnum field on my form, either before or after adding the 1, depends how you want to do it.

if you would like some sample code for how to open and update the recordset, let me know what version of access you are using, as the code is different for 97 and 2000.

Hope this helps

Sue

 

[IanMilly]

Hi

I have done something like this, and I think the easiest way is to do what you have done and store the 3 parts of the number in separate fields and just concatinate them when you want to show the whole number

datasource for the serailnumber field would be CardType & Date & format("00000000",SerialNum) and the serialnumber as a whole would not be stored in the table.

The format is to make sure the serialnum is displayed as 8 digits

As to how to get the next number, if gaps in the numbering sequence do not matter, use an autonumber for the serialnum field, however if a record is aborted the number will be lost and a gap will be left in the sequence. If this is not acceptable, then I create my own numbering sequence.

I have a table just containing 1 number lets say 1
In my application I have a button to issue the next number, because in some cases an already issued number needs to be used, so you need to decide if the next number will be issued as soon as a new record is started, or if you want the user to actively issue the next number.

On the appropriate event open a recordset for the number stored in the table, add 1 to it and save the recordset. I assign the vale from the table to the serialnum field on my form, either before or after adding the 1, depends how you want to do it.

if you would like some sample code for how to open and update the recordset, let me know what version of access you are using, as the code is different for 97 and 2000.

Hope this helps

Sue

Hi Sue,

thanks for the help. I am using access 2000 (V9.0). I would very much appreciate some sample code for this. I think that is is best approach that you have suggested.

Thanks again

Ian

 

[suepowell]

Here is a sample database

Hope it all makes sense

Sue

 

[IanMilly]

thanks very much Sue

That is much appreciated Sue . Just the type of thing I was after.

Thanks again

Ian