Creating Serial Numbers

[bestfc01]

Hi There,

Please could someone advise if this is possible.

I have the following fields,
'Serial Number' (Primary Key from my table)
'Build Date' (MMYY)
'Sales Area'
'Serial Qty' (unbound text box in a form)

If the user types the following into the form with the below fields

'Serial Number' 12345
'Build Date' 0112
'Sales Area' A
'Serial Qty' 1

within my table it creates a record as:
Serial Number Build Date Sales Area
12345---------- 0112 -------A

But if the user types the following into the form with the below fields

'Serial Number' 12345
'Build Date' 0112
'Sales Area' A
'Serial Qty' 3

within my table it creates a records as:
Serial Number Build Date Sales Area
12345/1--------0112 -------A
12345/2------- 0112 --------A
12345/3 -------0112 --------A

Im just trying to save my users some time, ensure access does this for them. I no some people will save use the autonumber function, but our serial numbers are no gernerated this way, they are gernerated from the build order and we dont just sell items we require a serial number for. Its also important, if the serial qty is 1 it doesnt add /1

Thanks In Advanced.

 

[namliam]

I would probably still make it two columns the 12345 and the 1,2,3 part, then in any output or display insert the slash to it. Perhaps even normalize it out to have the Builddate and sales area to a seperate table to that of the 1,2,3 part which may (or may not) have particular data to it.

It should be fairly easy to create the records inside the database using a piece of code...
Something along the lines of:

dim rst as dao.recordset
set rst = currentdb.openrecordset("YourTable")
rst.addnew
rst!Yourfield= me.ThisValue
rst.update

Should get you started....

Edit:
noticed a bit more detailed code in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=253867

 

[bestfc01]

I would probably still make it two columns the 12345 and the 1,2,3 part, then in any output or display insert the slash to it. Perhaps even normalize it out to have the Builddate and sales area to a seperate table to that of the 1,2,3 part which may (or may not) have particular data to it.

It should be fairly easy to create the records inside the database using a piece of code...
Something along the lines of:

dim rst as dao.recordset
set rst = currentdb.openrecordset("YourTable")
rst.addnew
rst!Yourfield= me.ThisValue
rst.update

Should get you started....

Edit:
noticed a bit more detailed code in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=253867

Hi there,

Thanks for you help, unfortantly, we need the serial number field to be the major link, this is due to creating build documents for each serial number, which has subforms attached. I have tried your method previously, but when pulling up results on a specific serial number i.e. 12345/6 it would bring all the information up that contains 12345.

If its not possible its not the end of the world, it was just a though that could be a useful feature, as were going to have multiple tables at some point relating from this ie production qa packing warranty etc.

 

[namliam]

You can use more than one field to be the "major" link between your different tables/forms.

Another (hidden) alternative is "promoted" by some people... in the discussion of natural vs database keys...
While your natural key of serial number 12345/6 is your "business" key, the thing we work with in the real world....
Inside the database each of those unique business keys gets an autonumber key value that you use for linking tables and forms....