Automatically generate ID number

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

We have forms that are identified by the department
abbreviation, the last two digits of the year, and a
sequential number. If the department is Quality Control,
the first form this year is Q-04-01. The next one is Q-04-
02. For Production it is P-04-01, etc. The idea is that
if P-04-01 has already been completed, the user does not
need to know that in order for the number of the next one
to be P-04-02. I would like the user to be able to select
a department on an opening screen, click a button, and
have the form open with the correct identifying number
filled in. I think I could figure out some way (probably
not the best way) to get the department abbreviation and
the last two digits of the year, but the sequential number
has me stumped unless I have a separate table for each
department, and bring them all together with a query.
Somehow that doesn't seem like the best way to solve the
numbering problem. There are about twelve departments,
and the total number of forms completed in a year is less
than 100, in case those things matter.
 
Bruce,

To begin with, your hunch that breaking up the table by department is not a
good idea, is correct. It will only make things much worse.

What you're trying to do is a piece of cake with the use of a few lines of
code, fired by the button on your first form. Not knowing your actual names,
I'll make the following assumptions, and you'll have to change accordingly:
ctlDept -The name of the control via which the user selects a department
EntryForm -The name of the form to open
Entry_ID -The name of the control that holds the key (what you call the form
identifier - it is actually the primatry key in an underlying table) on form
EntryForm
tblMaster -The name of the underlying table
EntryKey - the name of the key field in tblMaster

So, while you have your first form ope in design view, select the button and
open the properties window. Select tab Evens, put the cursor in line for On
Click and click on the little button with the three dots on the right hand
side. Select Code Builder. You will be taken to the VBA screen, and the
cursor will be between two lines that look like:

Private Sub ButtonName_Click(Cancel as Integer)

End Sub

Pate the following code between these two lines, and make the name changes:

Dept = Me.ctlDept
Yr = Format(Date(),"yy")
Lst = DMax("[EntryKey]","tblMaster","Left([EntryKey],4) = ' " & Dept & "-" &
Yr & " ' ")
Nxt = Format(Val(Right(Lst,2)) + 1, "00")
Docmd.OpenForm "EntryForm"
DoCmd.GoToRecord , , acNewRec
Me.Entry_ID = Dept & "-" & Yr & "-" & Nxt

HTH,
Nikos
 
I would advise having three separate columns on the
relevant table for department, year and sequence number.
It generally proves easier to compose composite fields on
the fly than decompose them. With the three columns, it
should be easy to determine the next available sequence
number given the department and year information.

Hope That Helps
Gerald Stanley MCSD
 
Thanks for the reply. I think I see where you are going
with this, but I have questions. I don't see what
Entry_ID is (how it is different from EntryKey). EntryKey
is an autonumber primary key in the underlying table.
Although the form ID (e.g. Q-04-01) is unique, it is a
concatenated field, which I doubt would work well as the
primary key. amy understanding is that Entry_ID is the
name of the field in which the ID number such as Q-04-04
would appear, but when I try to compile the code I
get "Compile error: Method or data member not found".
Entry_ID and the dot before it are highlighted. By the
way, the control on the form has the same name as the
field in the underlying table.
I don't fully understand your concatenation, but I wonder
if it will lead to problems if there is a two-letter
department abbreviation?
The other posting in response to my question led me to
think about having one table field each for department,
year, and the last two digits. My experience with
concatenation is with Excel, but I think I am more
comfortable with concatenating fields rather than
concatenating calculations. It seems cleaner somehow,
although since there is no user input into generating the
number (other than choosing the department) I suppose that
wouldn't matter so much. My thinking is that I would end
up with something like = [DeptID] & [Year] & [the rest of
the number], and would somehow get that into the FormID
field (where FormID is the number such as Q-04-01).
Thanks for the help. Please let me know if I am getting
warm.
-----Original Message-----
Bruce,

To begin with, your hunch that breaking up the table by department is not a
good idea, is correct. It will only make things much worse.

What you're trying to do is a piece of cake with the use of a few lines of
code, fired by the button on your first form. Not knowing your actual names,
I'll make the following assumptions, and you'll have to change accordingly:
ctlDept -The name of the control via which the user selects a department
EntryForm -The name of the form to open
Entry_ID -The name of the control that holds the key (what you call the form
identifier - it is actually the primatry key in an underlying table) on form
EntryForm
tblMaster -The name of the underlying table
EntryKey - the name of the key field in tblMaster

So, while you have your first form ope in design view, select the button and
open the properties window. Select tab Evens, put the cursor in line for On
Click and click on the little button with the three dots on the right hand
side. Select Code Builder. You will be taken to the VBA screen, and the
cursor will be between two lines that look like:

Private Sub ButtonName_Click(Cancel as Integer)

End Sub

Pate the following code between these two lines, and make the name changes:

Dept = Me.ctlDept
Yr = Format(Date(),"yy")
Lst = DMax("[EntryKey]","tblMaster","Left([EntryKey],4) = ' " & Dept & "-" &
Yr & " ' ")
Nxt = Format(Val(Right(Lst,2)) + 1, "00")
Docmd.OpenForm "EntryForm"
DoCmd.GoToRecord , , acNewRec
Me.Entry_ID = Dept & "-" & Yr & "-" & Nxt

HTH,
Nikos

We have forms that are identified by the department
abbreviation, the last two digits of the year, and a
sequential number. If the department is Quality Control,
the first form this year is Q-04-01. The next one is Q- 04-
02. For Production it is P-04-01, etc. The idea is that
if P-04-01 has already been completed, the user does not
need to know that in order for the number of the next one
to be P-04-02. I would like the user to be able to select
a department on an opening screen, click a button, and
have the form open with the correct identifying number
filled in. I think I could figure out some way (probably
not the best way) to get the department abbreviation and
the last two digits of the year, but the sequential number
has me stumped unless I have a separate table for each
department, and bring them all together with a query.
Somehow that doesn't seem like the best way to solve the
numbering problem. There are about twelve departments,
and the total number of forms completed in a year is less
than 100, in case those things matter.
Click to expand...


.
Click to expand...
 
I think I like the idea of concatenating fields, but I
don't have much to go on in expressing this preference. I
have posted more questions to the other response to my
original question, and I would like to ask you to have a
look at what I wrote there. I'm not sure the best way to
continue the thread, so I am posting a reply to both
answers. In any case, thank you for your help.
 
Bruce,

What I was referring to by EntryKey was the field in your table that holds
the "form number", assuming that this was the PK. Yes, you can use this
field as your primary key, and get rid of the autonumber altogether - or you
can set the EntryKey field to required>yes and indexed>Yes(no duplicates)
and keep the autonumber as well.


Bruce said:
Thanks for the reply. I think I see where you are going
with this, but I have questions. I don't see what
Entry_ID is (how it is different from EntryKey). EntryKey
is an autonumber primary key in the underlying table.
Although the form ID (e.g. Q-04-01) is unique, it is a
concatenated field, which I doubt would work well as the
primary key. amy understanding is that Entry_ID is the
name of the field in which the ID number such as Q-04-04
would appear, but when I try to compile the code I
get "Compile error: Method or data member not found".
Entry_ID and the dot before it are highlighted. By the
way, the control on the form has the same name as the
field in the underlying table.
I don't fully understand your concatenation, but I wonder
if it will lead to problems if there is a two-letter
department abbreviation?
The other posting in response to my question led me to
think about having one table field each for department,
year, and the last two digits. My experience with
concatenation is with Excel, but I think I am more
comfortable with concatenating fields rather than
concatenating calculations. It seems cleaner somehow,
although since there is no user input into generating the
number (other than choosing the department) I suppose that
wouldn't matter so much. My thinking is that I would end
up with something like = [DeptID] & [Year] & [the rest of
the number], and would somehow get that into the FormID
field (where FormID is the number such as Q-04-01).
Thanks for the help. Please let me know if I am getting
warm.
-----Original Message-----
Bruce,

To begin with, your hunch that breaking up the table by department is not a
good idea, is correct. It will only make things much worse.

What you're trying to do is a piece of cake with the use of a few lines of
code, fired by the button on your first form. Not knowing your actual names,
I'll make the following assumptions, and you'll have to change accordingly:
ctlDept -The name of the control via which the user selects a department
EntryForm -The name of the form to open
Entry_ID -The name of the control that holds the key (what you call the form
identifier - it is actually the primatry key in an underlying table) on form
EntryForm
tblMaster -The name of the underlying table
EntryKey - the name of the key field in tblMaster

So, while you have your first form ope in design view, select the button and
open the properties window. Select tab Evens, put the cursor in line for On
Click and click on the little button with the three dots on the right hand
side. Select Code Builder. You will be taken to the VBA screen, and the
cursor will be between two lines that look like:

Private Sub ButtonName_Click(Cancel as Integer)

End Sub

Pate the following code between these two lines, and make the name changes:

Dept = Me.ctlDept
Yr = Format(Date(),"yy")
Lst = DMax("[EntryKey]","tblMaster","Left([EntryKey],4) = ' " & Dept & "-" &
Yr & " ' ")
Nxt = Format(Val(Right(Lst,2)) + 1, "00")
Docmd.OpenForm "EntryForm"
DoCmd.GoToRecord , , acNewRec
Me.Entry_ID = Dept & "-" & Yr & "-" & Nxt

HTH,
Nikos

We have forms that are identified by the department
abbreviation, the last two digits of the year, and a
sequential number. If the department is Quality Control,
the first form this year is Q-04-01. The next one is Q- 04-
02. For Production it is P-04-01, etc. The idea is that
if P-04-01 has already been completed, the user does not
need to know that in order for the number of the next one
to be P-04-02. I would like the user to be able to select
a department on an opening screen, click a button, and
have the form open with the correct identifying number
filled in. I think I could figure out some way (probably
not the best way) to get the department abbreviation and
the last two digits of the year, but the sequential number
has me stumped unless I have a separate table for each
department, and bring them all together with a query.
Somehow that doesn't seem like the best way to solve the
numbering problem. There are about twelve departments,
and the total number of forms completed in a year is less
than 100, in case those things matter.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top