Automatic ID Number

[Bruce]

This is a sort of repost of a similar question. Thanks to
those who provided help, but I still have questions, and
have clarified what I am trying to do. I think it is best
to start a new thread.
My startup screen contains a combo box of department
names. Each name has a corresponding abbreviation (e.g.
Quality is "Q"). The combo box source is a table
(tblSelectDept) that contains 3 columns: Autonumber,
Department Name, and Abbreviation. The idea is that a
user will select a department (e.g. Quality), and a form
(frmMainForm) will open with the identifying number Q-04-
01 filled in. Q is the department abbreviation, 04 is the
year, and 01 is the first Quality department record for
the year. The next time somebody selects Quality from the
department list in the combo box the identifying number
will be Q-04-02. Production will be P-04-01, etc. The
first record for any department will end with "01", the
second with "02", etc.
Since I will one day want to query the records for such
things as all those related to the Quality department, or
all those for a particular year, I think it is best if I
have a table field for Department Abbreviation
[DeptAbbrev], and one for Year [Year]. Department
Abbreviation will sometimes be two letters. I don't need
to keep the last two digits (01, 02, etc.) of the
identifying number in their own field, but will do so if
it helps. The way I see this working is that clicking on
the department name in the combo box will cause
[DeptAbbrev] & "-" & [Year] & "-" & (the last two digits)
to appear in the [IDnumber] field on the form (and be
stored in [IDnumber] in the underlying table (tblMain).
The question is "How"?
I plan to use autonumbers for primary keys (field name is
[ID]) in all tables unless I would do better otherwise.
Records will be stored in a single table (tblMain).
[IDnumber] (e.g. Q-04-01) will be among the fields stored
in tblMain. [IDnumber] will be unique, by the way, so as
far as that goes it could be the primary key, if needed.
I would think [DeptAbbrev] and [Year] will also be stored
in tblMain.
That is probably enough information for now. I am glad to
adjust my assumptions about how this will be done if they
are off track. If you have gotten this far, thanks for
your patience.

 

[Roger Carlson]

Your plan to store these values in separate fields is a good one. On my
website is a small sample database called "AutonumberProblem.mdb" which uses
the DMax function to find the next number. You can download it from the URL
below.

 

[Bruce]

Thanks. I have a better understanding of how to increment
a number. It still leaves me wondering how to set it up
in my case, though. In the original posting I specified
the names of the fields (in tblMain) for the Department
Abbreviation [DeptAbbrev] and the Year [Year]. For the
last two digits I will name the field [NumSequence]. Now
in the instance where I select the Quality department,
what I need to do is use DMax to find (and add +1) to the
largest value of the [NumSequence] field when [DeptAbbrev]
= Q and [Year] = 04. If this is the second record created
for the Quality department this year, the number created
will be Q-04-02. If, however, it is the first record
created for the Production department this year, the
number needs to be P-04-01, even if Q-04-01 already
exists. In other words, the first record this year for
any department needs to be [DeptAbbrev]-04-01. With ten
departments, there would be ten records ending with -01.
I have tried searching for this through Google groups, but
it is difficult to figure out how to phrase the question.
The suggestions I have received or found use a different
set of assumptions, and I can't figure out how to
customize them to my needs. Thanks.

-----Original Message-----
Your plan to store these values in separate fields is a good one. On my
website is a small sample database

called "AutonumberProblem.mdb" which uses

the DMax function to find the next number. You can download it from the URL
below.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

This is a sort of repost of a similar question. Thanks to
those who provided help, but I still have questions, and
have clarified what I am trying to do. I think it is best
to start a new thread.
My startup screen contains a combo box of department
names. Each name has a corresponding abbreviation (e.g.
Quality is "Q"). The combo box source is a table
(tblSelectDept) that contains 3 columns: Autonumber,
Department Name, and Abbreviation. The idea is that a
user will select a department (e.g. Quality), and a form
(frmMainForm) will open with the identifying number Q- 04-
01 filled in. Q is the department abbreviation, 04 is the
year, and 01 is the first Quality department record for
the year. The next time somebody selects Quality from the
department list in the combo box the identifying number
will be Q-04-02. Production will be P-04-01, etc. The
first record for any department will end with "01", the
second with "02", etc.
Since I will one day want to query the records for such
things as all those related to the Quality department, or
all those for a particular year, I think it is best if I
have a table field for Department Abbreviation
[DeptAbbrev], and one for Year [Year]. Department
Abbreviation will sometimes be two letters. I don't need
to keep the last two digits (01, 02, etc.) of the
identifying number in their own field, but will do so if
it helps. The way I see this working is that clicking on
the department name in the combo box will cause
[DeptAbbrev] & "-" & [Year] & "-" & (the last two digits)
to appear in the [IDnumber] field on the form (and be
stored in [IDnumber] in the underlying table (tblMain).
The question is "How"?
I plan to use autonumbers for primary keys (field name is
[ID]) in all tables unless I would do better otherwise.
Records will be stored in a single table (tblMain).
[IDnumber] (e.g. Q-04-01) will be among the fields stored
in tblMain. [IDnumber] will be unique, by the way, so as
far as that goes it could be the primary key, if needed.
I would think [DeptAbbrev] and [Year] will also be stored
in tblMain.
That is probably enough information for now. I am glad to
adjust my assumptions about how this will be done if they
are off track. If you have gotten this far, thanks for
your patience.

.

 

[Roger Carlson]

OK, let's assume you have 3 controls on your form:
txtDeptAbbrev: bound to [DeptAbbrev]
txtYear: bound to [Year]
txtNumSequence: bound to [NumSequence]

You can find your proper sequence number with the DMax and use a "Where"
condition that uses the values on the form as the criteria. Your code would
look something like this:

If Me!txtNumSequence= 0 Then
Me!txtNumSequence= Nz(DMax("NumSequence", "tblMain", "[DeptAbbrev] = '"
& txtDeptAbbrev & "' and [Year] = '" & txtYear & "'")) + 1
End If

"[DeptAbbrev] = ' " & txtDeptAbbrev & " ' and [Year] = ' " & txtYear & " ' "
is essentially a WHERE clause without the Where. (I put extra spaces
between the quotes and apostrophes for clarity, but there should be none.)

There is another sample on my site called "FormSubform.mdb" which also uses
this technique (but without the Where clause).

Hopefully this will work for you as I am doing this from my head and have
not been able to test it. It might require some fiddling.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks. I have a better understanding of how to increment
a number. It still leaves me wondering how to set it up
in my case, though. In the original posting I specified
the names of the fields (in tblMain) for the Department
Abbreviation [DeptAbbrev] and the Year [Year]. For the
last two digits I will name the field [NumSequence]. Now
in the instance where I select the Quality department,
what I need to do is use DMax to find (and add +1) to the
largest value of the [NumSequence] field when [DeptAbbrev]
= Q and [Year] = 04. If this is the second record created
for the Quality department this year, the number created
will be Q-04-02. If, however, it is the first record
created for the Production department this year, the
number needs to be P-04-01, even if Q-04-01 already
exists. In other words, the first record this year for
any department needs to be [DeptAbbrev]-04-01. With ten
departments, there would be ten records ending with -01.
I have tried searching for this through Google groups, but
it is difficult to figure out how to phrase the question.
The suggestions I have received or found use a different
set of assumptions, and I can't figure out how to
customize them to my needs. Thanks.

-----Original Message-----
Your plan to store these values in separate fields is a good one. On my
website is a small sample database

called "AutonumberProblem.mdb" which uses

the DMax function to find the next number. You can download it from the URL
below.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

This is a sort of repost of a similar question. Thanks to
those who provided help, but I still have questions, and
have clarified what I am trying to do. I think it is best
to start a new thread.
My startup screen contains a combo box of department
names. Each name has a corresponding abbreviation (e.g.
Quality is "Q"). The combo box source is a table
(tblSelectDept) that contains 3 columns: Autonumber,
Department Name, and Abbreviation. The idea is that a
user will select a department (e.g. Quality), and a form
(frmMainForm) will open with the identifying number Q- 04-
01 filled in. Q is the department abbreviation, 04 is the
year, and 01 is the first Quality department record for
the year. The next time somebody selects Quality from the
department list in the combo box the identifying number
will be Q-04-02. Production will be P-04-01, etc. The
first record for any department will end with "01", the
second with "02", etc.
Since I will one day want to query the records for such
things as all those related to the Quality department, or
all those for a particular year, I think it is best if I
have a table field for Department Abbreviation
[DeptAbbrev], and one for Year [Year]. Department
Abbreviation will sometimes be two letters. I don't need
to keep the last two digits (01, 02, etc.) of the
identifying number in their own field, but will do so if
it helps. The way I see this working is that clicking on
the department name in the combo box will cause
[DeptAbbrev] & "-" & [Year] & "-" & (the last two digits)
to appear in the [IDnumber] field on the form (and be
stored in [IDnumber] in the underlying table (tblMain).
The question is "How"?
I plan to use autonumbers for primary keys (field name is
[ID]) in all tables unless I would do better otherwise.
Records will be stored in a single table (tblMain).
[IDnumber] (e.g. Q-04-01) will be among the fields stored
in tblMain. [IDnumber] will be unique, by the way, so as
far as that goes it could be the primary key, if needed.
I would think [DeptAbbrev] and [Year] will also be stored
in tblMain.
That is probably enough information for now. I am glad to
adjust my assumptions about how this will be done if they
are off track. If you have gotten this far, thanks for
your patience.

.

 

[Bruce]

My difficulty is that I do not have those three controls
on the form. I need, both for ease of use and for future
reference, a concatenated field on the form.
I would like the user to see first an opening screen
(switchboard) that contains several options, one of which
is to select a department from a combo box (cboSelectDept)
in order to start a new form. cboSelectDept gets its list
from a table (tblDeptList). Selecting the Quality
department in the combo box will cause frmMainForm to
open, with Q-04-02 filled in if this is the second form
(record) for the Quality Department this year.
tblMain contains the fields [DeptAbbrev] (which value
comes from cboSelectDept on the switchboard), [Year]
(which already contains the default value Date() with the
format yy), and NumSequence (which needs to be incremented
by 1 from the previous record for that same department and
year).
The part in your sample code I cannot make work for me
is "[DeptAbbrev] = ' " & txtDeptAbbrev. It seems to me,
if I am understanding Dmax correctly, that where you have
txtDeptAbbrev I need the value of either cboSelectDept
(from the switchboard form) or the field from the combo
box's source table (tblDeptList). If this is correct, I
can't sort out how to specify that in the code.
One thing that might help the [Year] part is that I can
just specify the current year. In other words, could I
use =Format(Date(),"yy") in place of txtYear in your
code? The year will always be the current year, no
exceptions.
I feel like I am so close to getting this. Please bear
with me for a little longer. I really appreciate your
help.

-----Original Message-----
OK, let's assume you have 3 controls on your form:
txtDeptAbbrev: bound to [DeptAbbrev]
txtYear: bound to [Year]
txtNumSequence: bound to [NumSequence]

You can find your proper sequence number with the DMax and use a "Where"
condition that uses the values on the form as the criteria. Your code would
look something like this:

If Me!txtNumSequence= 0 Then
Me!txtNumSequence= Nz(DMax

("NumSequence", "tblMain", "[DeptAbbrev] = '"

& txtDeptAbbrev & "' and [Year] = '" & txtYear & "'")) + 1
End If

"[DeptAbbrev] = ' " & txtDeptAbbrev & " ' and [Year] = ' " & txtYear & " ' "
is essentially a WHERE clause without the Where. (I put extra spaces
between the quotes and apostrophes for clarity, but there should be none.)

There is another sample on my site

called "FormSubform.mdb" which also uses

this technique (but without the Where clause).

Hopefully this will work for you as I am doing this from my head and have
not been able to test it. It might require some fiddling.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks. I have a better understanding of how to increment
a number. It still leaves me wondering how to set it up
in my case, though. In the original posting I specified
the names of the fields (in tblMain) for the Department
Abbreviation [DeptAbbrev] and the Year [Year]. For the
last two digits I will name the field [NumSequence]. Now
in the instance where I select the Quality department,
what I need to do is use DMax to find (and add +1) to the
largest value of the [NumSequence] field when [DeptAbbrev]
= Q and [Year] = 04. If this is the second record created
for the Quality department this year, the number created
will be Q-04-02. If, however, it is the first record
created for the Production department this year, the
number needs to be P-04-01, even if Q-04-01 already
exists. In other words, the first record this year for
any department needs to be [DeptAbbrev]-04-01. With ten
departments, there would be ten records ending with -01.
I have tried searching for this through Google groups, but
it is difficult to figure out how to phrase the question.
The suggestions I have received or found use a different
set of assumptions, and I can't figure out how to
customize them to my needs. Thanks.

-----Original Message-----
Your plan to store these values in separate fields is a good one. On my
website is a small sample database

called "AutonumberProblem.mdb" which uses

the DMax function to find the next number. You can download it from the URL
below.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

This is a sort of repost of a similar question.

Thanks
to

those who provided help, but I still have questions, and
have clarified what I am trying to do. I think it is best
to start a new thread.
My startup screen contains a combo box of department
names. Each name has a corresponding abbreviation (e.g.
Quality is "Q"). The combo box source is a table
(tblSelectDept) that contains 3 columns: Autonumber,
Department Name, and Abbreviation. The idea is that a
user will select a department (e.g. Quality), and a form
(frmMainForm) will open with the identifying number

Q-
04-

01 filled in. Q is the department abbreviation, 04

is
the

year, and 01 is the first Quality department record for
the year. The next time somebody selects Quality

from
the

department list in the combo box the identifying number
will be Q-04-02. Production will be P-04-01, etc. The
first record for any department will end with "01", the
second with "02", etc.
Since I will one day want to query the records for such
things as all those related to the Quality

department,
or

all those for a particular year, I think it is best if I
have a table field for Department Abbreviation
[DeptAbbrev], and one for Year [Year]. Department
Abbreviation will sometimes be two letters. I don't need
to keep the last two digits (01, 02, etc.) of the
identifying number in their own field, but will do so if
it helps. The way I see this working is that

clicking
on

the department name in the combo box will cause
[DeptAbbrev] & "-" & [Year] & "-" & (the last two digits)
to appear in the [IDnumber] field on the form (and be
stored in [IDnumber] in the underlying table (tblMain).
The question is "How"?
I plan to use autonumbers for primary keys (field

name
is

[ID]) in all tables unless I would do better otherwise.
Records will be stored in a single table (tblMain).
[IDnumber] (e.g. Q-04-01) will be among the fields stored
in tblMain. [IDnumber] will be unique, by the way,

so
as

far as that goes it could be the primary key, if needed.
I would think [DeptAbbrev] and [Year] will also be stored
in tblMain.
That is probably enough information for now. I am

glad
to

adjust my assumptions about how this will be done if they
are off track. If you have gotten this far, thanks for
your patience.


.

.

 

[Roger Carlson]

You could do 2 things:

1) If the switchboard form stays open the whole time you can just refer to
the controls on it:
"[DeptAbbrev] = ' " & forms!Switchboard!cboSelectDept ...

2) If the switchboard does not stay open, you could send those values to
controls on the other form with OpenArgs. On my website there is a sample
called OpenArgs.mdb which illustrates how this works.


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

My difficulty is that I do not have those three controls
on the form. I need, both for ease of use and for future
reference, a concatenated field on the form.
I would like the user to see first an opening screen
(switchboard) that contains several options, one of which
is to select a department from a combo box (cboSelectDept)
in order to start a new form. cboSelectDept gets its list
from a table (tblDeptList). Selecting the Quality
department in the combo box will cause frmMainForm to
open, with Q-04-02 filled in if this is the second form
(record) for the Quality Department this year.
tblMain contains the fields [DeptAbbrev] (which value
comes from cboSelectDept on the switchboard), [Year]
(which already contains the default value Date() with the
format yy), and NumSequence (which needs to be incremented
by 1 from the previous record for that same department and
year).
The part in your sample code I cannot make work for me
is "[DeptAbbrev] = ' " & txtDeptAbbrev. It seems to me,
if I am understanding Dmax correctly, that where you have
txtDeptAbbrev I need the value of either cboSelectDept
(from the switchboard form) or the field from the combo
box's source table (tblDeptList). If this is correct, I
can't sort out how to specify that in the code.
One thing that might help the [Year] part is that I can
just specify the current year. In other words, could I
use =Format(Date(),"yy") in place of txtYear in your
code? The year will always be the current year, no
exceptions.
I feel like I am so close to getting this. Please bear
with me for a little longer. I really appreciate your
help.

-----Original Message-----
OK, let's assume you have 3 controls on your form:
txtDeptAbbrev: bound to [DeptAbbrev]
txtYear: bound to [Year]
txtNumSequence: bound to [NumSequence]

You can find your proper sequence number with the DMax and use a "Where"
condition that uses the values on the form as the criteria. Your code would
look something like this:

If Me!txtNumSequence= 0 Then
Me!txtNumSequence= Nz(DMax

("NumSequence", "tblMain", "[DeptAbbrev] = '"

& txtDeptAbbrev & "' and [Year] = '" & txtYear & "'")) + 1
End If

"[DeptAbbrev] = ' " & txtDeptAbbrev & " ' and [Year] = ' " & txtYear & " ' "
is essentially a WHERE clause without the Where. (I put extra spaces
between the quotes and apostrophes for clarity, but there should be none.)

There is another sample on my site

called "FormSubform.mdb" which also uses

this technique (but without the Where clause).

Hopefully this will work for you as I am doing this from my head and have
not been able to test it. It might require some fiddling.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Thanks. I have a better understanding of how to increment
a number. It still leaves me wondering how to set it up
in my case, though. In the original posting I specified
the names of the fields (in tblMain) for the Department
Abbreviation [DeptAbbrev] and the Year [Year]. For the
last two digits I will name the field [NumSequence]. Now
in the instance where I select the Quality department,
what I need to do is use DMax to find (and add +1) to the
largest value of the [NumSequence] field when [DeptAbbrev]
= Q and [Year] = 04. If this is the second record created
for the Quality department this year, the number created
will be Q-04-02. If, however, it is the first record
created for the Production department this year, the
number needs to be P-04-01, even if Q-04-01 already
exists. In other words, the first record this year for
any department needs to be [DeptAbbrev]-04-01. With ten
departments, there would be ten records ending with -01.
I have tried searching for this through Google groups, but
it is difficult to figure out how to phrase the question.
The suggestions I have received or found use a different
set of assumptions, and I can't figure out how to
customize them to my needs. Thanks.
-----Original Message-----
Your plan to store these values in separate fields is a
good one. On my
website is a small sample database
called "AutonumberProblem.mdb" which uses
the DMax function to find the next number. You can
download it from the URL
below.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

message
This is a sort of repost of a similar question. Thanks
to
those who provided help, but I still have questions, and
have clarified what I am trying to do. I think it is
best
to start a new thread.
My startup screen contains a combo box of department
names. Each name has a corresponding abbreviation (e.g.
Quality is "Q"). The combo box source is a table
(tblSelectDept) that contains 3 columns: Autonumber,
Department Name, and Abbreviation. The idea is that a
user will select a department (e.g. Quality), and a form
(frmMainForm) will open with the identifying number Q-
04-
01 filled in. Q is the department abbreviation, 04 is
the
year, and 01 is the first Quality department record for
the year. The next time somebody selects Quality from
the
department list in the combo box the identifying number
will be Q-04-02. Production will be P-04-01, etc. The
first record for any department will end with "01", the
second with "02", etc.
Since I will one day want to query the records for such
things as all those related to the Quality department,
or
all those for a particular year, I think it is best if I
have a table field for Department Abbreviation
[DeptAbbrev], and one for Year [Year]. Department
Abbreviation will sometimes be two letters. I don't
need
to keep the last two digits (01, 02, etc.) of the
identifying number in their own field, but will do so if
it helps. The way I see this working is that clicking
on
the department name in the combo box will cause
[DeptAbbrev] & "-" & [Year] & "-" & (the last two
digits)
to appear in the [IDnumber] field on the form (and be
stored in [IDnumber] in the underlying table (tblMain).
The question is "How"?
I plan to use autonumbers for primary keys (field name
is
[ID]) in all tables unless I would do better otherwise.
Records will be stored in a single table (tblMain).
[IDnumber] (e.g. Q-04-01) will be among the fields
stored
in tblMain. [IDnumber] will be unique, by the way, so
as
far as that goes it could be the primary key, if needed.
I would think [DeptAbbrev] and [Year] will also be
stored
in tblMain.
That is probably enough information for now. I am glad
to
adjust my assumptions about how this will be done if
they
are off track. If you have gotten this far, thanks for
your patience.


.

.

 

[Bruce]

Thanks again for all of your help. I have to move to
another project for a while, so I am not sure when I will
be trying to implement the suggestions, but I think I have
enough to do what I need to. Thanks again.

-----Original Message-----
You could do 2 things:

1) If the switchboard form stays open the whole time you can just refer to
the controls on it:
"[DeptAbbrev] = ' " & forms!Switchboard!cboSelectDept ...

2) If the switchboard does not stay open, you could send those values to
controls on the other form with OpenArgs. On my website there is a sample
called OpenArgs.mdb which illustrates how this works.


--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

My difficulty is that I do not have those three controls
on the form. I need, both for ease of use and for future
reference, a concatenated field on the form.
I would like the user to see first an opening screen
(switchboard) that contains several options, one of which
is to select a department from a combo box (cboSelectDept)
in order to start a new form. cboSelectDept gets its list
from a table (tblDeptList). Selecting the Quality
department in the combo box will cause frmMainForm to
open, with Q-04-02 filled in if this is the second form
(record) for the Quality Department this year.
tblMain contains the fields [DeptAbbrev] (which value
comes from cboSelectDept on the switchboard), [Year]
(which already contains the default value Date() with the
format yy), and NumSequence (which needs to be incremented
by 1 from the previous record for that same department and
year).
The part in your sample code I cannot make work for me
is "[DeptAbbrev] = ' " & txtDeptAbbrev. It seems to me,
if I am understanding Dmax correctly, that where you have
txtDeptAbbrev I need the value of either cboSelectDept
(from the switchboard form) or the field from the combo
box's source table (tblDeptList). If this is correct, I
can't sort out how to specify that in the code.
One thing that might help the [Year] part is that I can
just specify the current year. In other words, could I
use =Format(Date(),"yy") in place of txtYear in your
code? The year will always be the current year, no
exceptions.
I feel like I am so close to getting this. Please bear
with me for a little longer. I really appreciate your
help.

-----Original Message-----
OK, let's assume you have 3 controls on your form:
txtDeptAbbrev: bound to [DeptAbbrev]
txtYear: bound to [Year]
txtNumSequence: bound to [NumSequence]

You can find your proper sequence number with the DMax and use a "Where"
condition that uses the values on the form as the criteria. Your code would
look something like this:

If Me!txtNumSequence= 0 Then
Me!txtNumSequence= Nz(DMax

("NumSequence", "tblMain", "[DeptAbbrev] = '"

& txtDeptAbbrev & "' and [Year] = '" & txtYear & "'")) + 1
End If

"[DeptAbbrev] = ' " & txtDeptAbbrev & " ' and [Year] = ' " & txtYear & " ' "
is essentially a WHERE clause without the Where. (I

put
extra spaces

between the quotes and apostrophes for clarity, but

there
should be none.)

There is another sample on my site

called "FormSubform.mdb" which also uses

this technique (but without the Where clause).

Hopefully this will work for you as I am doing this

from
my head and have

not been able to test it. It might require some fiddling.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


Thanks. I have a better understanding of how to increment
a number. It still leaves me wondering how to set it up
in my case, though. In the original posting I specified
the names of the fields (in tblMain) for the Department
Abbreviation [DeptAbbrev] and the Year [Year]. For the
last two digits I will name the field [NumSequence]. Now
in the instance where I select the Quality department,
what I need to do is use DMax to find (and add +1) to the
largest value of the [NumSequence] field when [DeptAbbrev]
= Q and [Year] = 04. If this is the second record created
for the Quality department this year, the number created
will be Q-04-02. If, however, it is the first record
created for the Production department this year, the
number needs to be P-04-01, even if Q-04-01 already
exists. In other words, the first record this year for
any department needs to be [DeptAbbrev]-04-01. With ten
departments, there would be ten records ending with - 01.
I have tried searching for this through Google

groups,
but

it is difficult to figure out how to phrase the question.
The suggestions I have received or found use a different
set of assumptions, and I can't figure out how to
customize them to my needs. Thanks.
-----Original Message-----
Your plan to store these values in separate fields is a
good one. On my
website is a small sample database
called "AutonumberProblem.mdb" which uses
the DMax function to find the next number. You can
download it from the URL
below.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

message
This is a sort of repost of a similar question. Thanks
to
those who provided help, but I still have

questions,
and

have clarified what I am trying to do. I think it is
best
to start a new thread.
My startup screen contains a combo box of department
names. Each name has a corresponding abbreviation (e.g.
Quality is "Q"). The combo box source is a table
(tblSelectDept) that contains 3 columns: Autonumber,
Department Name, and Abbreviation. The idea is

that
a

user will select a department (e.g. Quality), and

a
form

(frmMainForm) will open with the identifying

number
Q-

04-
01 filled in. Q is the department abbreviation,

04
is

the
year, and 01 is the first Quality department

record
for

the year. The next time somebody selects Quality from
the
department list in the combo box the identifying number
will be Q-04-02. Production will be P-04-01, etc. The
first record for any department will end

with "01",
the

second with "02", etc.
Since I will one day want to query the records for such
things as all those related to the Quality department,
or
all those for a particular year, I think it is

best
if I

have a table field for Department Abbreviation
[DeptAbbrev], and one for Year [Year]. Department
Abbreviation will sometimes be two letters. I don't
need
to keep the last two digits (01, 02, etc.) of the
identifying number in their own field, but will do so if
it helps. The way I see this working is that clicking
on
the department name in the combo box will cause
[DeptAbbrev] & "-" & [Year] & "-" & (the last two
digits)
to appear in the [IDnumber] field on the form (and be
stored in [IDnumber] in the underlying table (tblMain).
The question is "How"?
I plan to use autonumbers for primary keys (field name
is
[ID]) in all tables unless I would do better otherwise.
Records will be stored in a single table (tblMain).
[IDnumber] (e.g. Q-04-01) will be among the fields
stored
in tblMain. [IDnumber] will be unique, by the

way,
so

as
far as that goes it could be the primary key, if needed.
I would think [DeptAbbrev] and [Year] will also be
stored
in tblMain.
That is probably enough information for now. I am glad
to
adjust my assumptions about how this will be done if
they
are off track. If you have gotten this far,

thanks
for

your patience.


.



.

.