Automatic ID Number

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

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.
 
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.
 
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
Click to expand...
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.
Click to expand...


.
Click to expand...
 
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


Bruce said:
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
Click to expand...
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.
Click to expand...


.
Click to expand...
Click to expand...
 
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
Click to expand...
("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
Click to expand...
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
Click to expand...
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.
Click to expand...
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
Click to expand...
Q-
04-
01 filled in. Q is the department abbreviation, 04
Click to expand...
is
the
year, and 01 is the first Quality department record for
the year. The next time somebody selects Quality
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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,
Click to expand...
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
Click to expand...
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.


.
Click to expand...
Click to expand...


.
Click to expand...
 
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


Bruce said:
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
Click to expand...
("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
Click to expand...
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.


.
Click to expand...


.
Click to expand...
Click to expand...
 
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
Click to expand...
("NumSequence", "tblMain", "[DeptAbbrev] = '"
& txtDeptAbbrev & "' and [Year] = '" & txtYear & "'")) + 1
End If

"[DeptAbbrev] = ' " & txtDeptAbbrev & " ' and [Year] = ' " & txtYear & " ' "
is essentially a WHERE clause without the Where. (I
Click to expand...
put
extra spaces
between the quotes and apostrophes for clarity, but
Click to expand...
there
should be none.)
There is another sample on my site
Click to expand...
called "FormSubform.mdb" which also uses
this technique (but without the Where clause).

Hopefully this will work for you as I am doing this
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
that
a
user will select a department (e.g. Quality), and
Click to expand...
a
form
(frmMainForm) will open with the identifying
Click to expand...
number
Q-
04-
01 filled in. Q is the department abbreviation,
Click to expand...
04
is
the
year, and 01 is the first Quality department
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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,
Click to expand...
thanks
for
your patience.


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top