Table Design???

[dgilman]

OK here's the deal. We have 36 craft. I am keeping track of Deviation
from specs and have to create a serial number every time a deviatin i
authorized. The makeup of the number is five digits that never chang
dash four alphnumberic digits that will not change dash three digit
that do (starting with 901 for example), sequentially o
XXXXX-llXX-XXX. Can (or should) this be defined in the tabl
properties. If not what is my best approach. Any help would b
appreciated:confused

 

[Jackie]

I have do a similar thing with developing a quote number.

You can add the code to the Current property of the form or, as I prefer, to a button to add record. First you need to be able to determine the last record for the sequencing. The easiest way to do this may be to have an additional field in the table to keep the last 3 digits. Also, are the digits to be zero filled? Try this (where the form is frmInputForm and fields are SerialNo and SequenceNo), it is assuming zero fill

Dim BegSerial as Strin
Dim Sequence as Intege

'Where the # represent the static part of the serial
BegSerial = "#####-####
Sequence = DMax("[SequenceNo]","tblSerialData")+

DoCmd.GotoRecord , , acNewRe
Me!SequenceNo = Sequenc
Me!SerialNo = BegSerial & Right(([Sequence]+10000),4

Hope this helps


----- dgilman wrote: ----


OK here's the deal. We have 36 craft. I am keeping track of Deviation
from specs and have to create a serial number every time a deviatin i
authorized. The makeup of the number is five digits that never chang
dash four alphnumberic digits that will not change dash three digit
that do (starting with 901 for example), sequentially o
XXXXX-llXX-XXX. Can (or should) this be defined in the tabl
properties. If not what is my best approach. Any help would b
appreciated:confused