Document number auto fill help

[dcc15]

I have a DB (single/ flat table contains all data) that I have out grown. I
would start a new DB that will allow better separation of data types. This
will be a Quality Database that contains information related to rejected/
defective material, 2 tables: in-house (MRB) & customer (DMR) and associated
(with DMR) Return Material Authorization (RMA). The thing I need help with is
having a unique Doc. ID/Tracking number for each record (of course in the
format I want). I would like to have a command function form (button for each
type) to create a new record that when selected would open a data entry form
with a "created date", "auto-number" (can an auto-number have a format which
contains leading zeros?) field and then auto fill another field "docID". The
number field does not have to be auto as I can start a number sequence and
use add 1 in the formula (might be better). The ideal number would look like:
DMR-2007000001, where "DMR-" is a fixed text string, 2007 is the year
(obtained from "created date") and 000001 would be from the auto-num/ record
num. The "year" portion is not absolutely necessary as quarries can be made
from "created date", but it would be nice. Any help/ examples would be
greatly appreciated...Thanks

 

[mscertified]

You are breaking database normalization rules by encoding information into a
field (i.e. the year). Why not just use the Access autonumber for your unique
key. After your autonumber has been created you could then use that to build
your own artificial key, however you will need to have it indexed and unique
and have a loop to assign the number portion since multiple users could
generate the same number.


-Dorian

 

[heifler via AccessMonster.com]

You can always pad the real autonumber with zeros using so VBA code.
A simple function could be used for form and report displaying of the padded
record number.
If you are not yet up to writing this code, I would be happy to assist. If
you have considered making the jump to coding yourself. Please contact me.
There are many free video tutorials available.

 

[dcc15 via AccessMonster.com]

Thanks all
It is probably how I am (not) explaining what I am trying to do. I am getting
the help I need in "New Users" fairly simply (well not for me). Please take a
look if interested, I have seen many inquiries from people that seem to be
trying to do what I am (several forums) and I think with help I have almost
suceeded. If interested please look at the post in "New Users". Any
feedback/suggestions would be appreciated. At some point this DB will have to
be split (I guess) to allow multi-user interface and I have a concern about
editing/creating the same/duplicate record (opening the entry form at the
same time before the record is "saved". Thanks again.

You can always pad the real autonumber with zeros using so VBA code.
A simple function could be used for form and report displaying of the padded
record number.
If you are not yet up to writing this code, I would be happy to assist. If
you have considered making the jump to coding yourself. Please contact me.
There are many free video tutorials available.

I have a DB (single/ flat table contains all data) that I have out grown. I
would start a new DB that will allow better separation of data types. This

[quoted text clipped - 13 lines]

from "created date", but it would be nice. Any help/ examples would be
greatly appreciated...Thanks