Automatic Number Generator

[John Campeau]

Hi,
I am trying to write an automatic number generator for an issue tracking
system.
The number should look like this:

08-06-003

Where 08 represents the workplan section related to the issue. 06 represents
the year during which the issue was created. 003 is the number of the issue
in this section.

From time to time, the Workplan changes and is allocated a new name.
I have a form that contains 2 combo boxes. 1 for the workplan and 2 for the
workplan section.

I am having difficulty with the last part of the issue number generator.
I created Query1 that contains:
workplan criteria = issueFrm!workplanCombo
workplanSection criteria = issueFrm!workplanSectionCombo
I created Query2 that counts the number of records in Query1.

Private Sub workplanSectionNoCombo_AfterUpdate() triggers the number
generator (see code below)

I created a subform that has itsNoCountQry (Query2) as a Record Source.

For some reason, the subform doesn't get updated.
As it stands, the generated number looks like this:

08-06-

Me.Refresh does not work.

Your help is much appreciated.

Private Sub workplanSectionNoCombo_AfterUpdate()
'On Error GoTo Err_workplanSectionNoCombo_AfterUpdate

Dim TheDate As Date
Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3, temp As String

TheDate = Now
theYear = DatePart("yyyy", TheDate)
theShortYear = Right(theYear, 2)
itsNo_1 = Me!workplanSectionNoCombo.Column(0)
itsNo_2 = theShortYear
' DoCmd.OpenQuery "itsNoCountQry"
itsNo_3 = Me!issueCountSubfrm!itsNo_3
itsNo_3Len = Len(itsNo_3)
Select Case itsNo_3Len
Case 1
itsNo_3 = "00" & itsNo_3
Case 2
itsNo_3 = "0" & itsNo_3
End Select

Me!WorkplanSection = Me!workplanSectionNoCombo.Column(1)
Me![goal] = Me!workplanSectionNoCombo.Column(2)
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

Exit_workplanSectionNoCombo_AfterUpdate:
Exit Sub

Err_workplanSectionNoCombo_AfterUpdate:
MsgBox Err.Description
Resume Exit_workplanSectionNoCombo_AfterUpdate
End Sub

This first query gets the needed information from the itsTbl table.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query generates the relevant number.
This query is the record source for the subform.

itsNoCountQry

 

[BruceM]

You probably would do this in a form event rather than in a query. More
comments and questions inline. The comments in the code are intended to
point out a few things, but without more information there is really no way
to know how to proceed.

Hi,
I am trying to write an automatic number generator for an issue tracking
system.
The number should look like this:

08-06-003

Where 08 represents the workplan section related to the issue. 06
represents
the year during which the issue was created. 003 is the number of the
issue
in this section.

We can't see your database. Some more explanation is needed. What is the
workplan section, and what is the connection between it and the issue? Are
there related tables, or is this all one table? From some things you wrote
later in your posting I gather that there is a subform. If so, are you
trying to get this number to appear in each subform record, or on the main
form, or what exactly?

From time to time, the Workplan changes and is allocated a new name.
I have a form that contains 2 combo boxes. 1 for the workplan and 2 for
the
workplan section.

I am having difficulty with the last part of the issue number generator.
I created Query1 that contains:
workplan criteria = issueFrm!workplanCombo
workplanSection criteria = issueFrm!workplanSectionCombo
I created Query2 that counts the number of records in Query1.

Private Sub workplanSectionNoCombo_AfterUpdate() triggers the number
generator (see code below)

I created a subform that has itsNoCountQry (Query2) as a Record Source.

For some reason, the subform doesn't get updated.
As it stands, the generated number looks like this:

08-06-

Me.Refresh does not work.

Your help is much appreciated.

Private Sub workplanSectionNoCombo_AfterUpdate()
'On Error GoTo Err_workplanSectionNoCombo_AfterUpdate

Dim TheDate As Date

No need for this. Just use Date or Now as needed.

Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3, temp As String

You need to declare each item as a String, otherwise it will be a Variant:
Dim theYear as String, theShortYear as String, etc.

TheDate = Now

Not needed.

theYear = DatePart("yyyy", TheDate)

This could be: theYear = Format(Date,"yy")

theShortYear = Right(theYear, 2)

Not needed. See previous comment.

itsNo_1 = Me!workplanSectionNoCombo.Column(0)
itsNo_2 = theShortYear

Even if you need theShortYear, which you don't from what I can see, why
redefine it?

' DoCmd.OpenQuery "itsNoCountQry"
itsNo_3 = Me!issueCountSubfrm!itsNo_3
itsNo_3Len = Len(itsNo_3)

You've really lost me here. Why are you opening the query? Is itsNo_3
something from a subform?

You could put this sort of thing into the Select Case statement:
Select Case Len(itsNo_3)
However, if itsNo_3 is a number you could define it as such, then apply a
format to save yourself several lines of code. Where you concatenate
itsNo_3 into the string you could have Format(itsNo_3,"000"), and skip all
of the Select Case stuff.

Select Case itsNo_3Len
Case 1
itsNo_3 = "00" & itsNo_3
Case 2
itsNo_3 = "0" & itsNo_3
End Select

Me!WorkplanSection = Me!workplanSectionNoCombo.Column(1)

If your combo box has a visible column you can just reference the combo box
when you need that value.

Me![goal] = Me!workplanSectionNoCombo.Column(2)
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

Exit_workplanSectionNoCombo_AfterUpdate:
Exit Sub

Err_workplanSectionNoCombo_AfterUpdate:
MsgBox Err.Description
Resume Exit_workplanSectionNoCombo_AfterUpdate
End Sub

This first query gets the needed information from the itsTbl table.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query generates the relevant number.
This query is the record source for the subform.

itsNoCountQry

 

[John Campeau]

Hi Bruce,
Thank you for your help with this issue.

The issue tracking system has to have the capability to generate an issue
number automatically.
The issue number has 3 parts.

The first part: itsNo_1 represents the workplan section. More on this later.

The second part represents the year during whice the issue was created.
Thanks for your comments regarding this part.

The third part: itsNo_3 represents the number of issues for a particular
workplan section.

Here is the way I am trying to generate this number:
All workplan sections belong to a workplan.
The workplan changes from time to time.
The workplan number and workplan title are stored in a table:

TABLE workplanTbl
--------
workplanNo Represents the workplan number Text 3 key
workplan Workplan title Text 50

Each workplan is divided into workplan 10 sections.

TABLE workplanSectionTbl
--------
workplanSectionNo Represents the workplan section number Text 3 key
workplanSection Workplan title
Text 50

TABLE itsTbl
--------
....
itsNo Text 12 Represents the issue number
Text 12 key
....
workplanNo Represents the workplan number Text 3
workplanSectionNo Represents the workplan section number Text 3
....

I created form itsFrm to input each issue information.
The form's control Source is itsTbl.
2 different fields contain the workplan and workplan section related to the
issue.
I created 2 combo boxes for the user to select the workplan and workplan
section:

Combo box 1:
Name: workplanNoCombo
Control Source:workplan From workplanTbl
RowSource:
SELECT workplanTbl.workplanNo, workplanTbl.workplan
FROM workplanTbl
ORDER BY workplanTbl.workplanNo;

Combo box 2:
Name: workplanSectionNoCombo
Control Source:workplanSection From workplanSectionTbl
RowSource:
SELECT workplanSectionTbl.workplanSectionNo, workplanSectionTbl.workplan
FROM workplanSectionTbl
ORDER BY workplanSectionTbl.workplanSectionNo;

I created subform issueCountSubfrm which is attached to itsFrm
The record source for the subform is the combination of 2 queries.

The first query extracts the workplan and workplan section selected by the
user in the combo boxes of itsFrm.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query calculates the maximum value of the third part of the issue
number from the first query.

itsNoCountQry
-------------
SELECT Max(Right([itsNoCount1Qry].[itsNo],3))+1 AS itsNo_3
FROM itsNoCount1Qry;

issueCountSubfrm Record Source: itsNoCountQry

The After Update event of workplanSectionNoCombo (itsFrm) contains the code
used to generate the issue number.
....
Dim TheDate As Date
Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3 As String

TheDate = Now
theYear = DatePart("yyyy", TheDate)
theShortYear = Right(theYear, 2)
' Generating the first part of the issue number.
' itsNo_1 comes from the selection the user made
' in workplanSectionNoCombo of itsFrm
itsNo_1 = Me!workplanSectionNoCombo.Column(0)
' The second part of the issue number is generated by date manipulation
itsNo_2 = theShortYear
....
' Here, I am getting the third part of the issue number from the subform
itsNo_3 = Me!issueCountSubfrm!itsNo_3
' the third part of the issue number always contains 3 characters.
' Here I am buffering the third part of the issue number depending od the
size of itsNo_3Len
itsNo_3Len = Len(itsNo_3)
Select Case itsNo_3Len
Case 1
' The number has 1 digit
itsNo_3 = "00" & itsNo_3
Case 2
' The number has 2 digits
itsNo_3 = "0" & itsNo_3
End Select
....
' Assembling the 3 parts of the isue number
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

I hope that you can make heads or tails fro all this.
Let me know if you have questions
--
Cheers,
John

You probably would do this in a form event rather than in a query. More
comments and questions inline. The comments in the code are intended to
point out a few things, but without more information there is really no way
to know how to proceed.

Hi,
I am trying to write an automatic number generator for an issue tracking
system.
The number should look like this:

08-06-003

Where 08 represents the workplan section related to the issue. 06
represents
the year during which the issue was created. 003 is the number of the
issue
in this section.

We can't see your database. Some more explanation is needed. What is the
workplan section, and what is the connection between it and the issue? Are
there related tables, or is this all one table? From some things you wrote
later in your posting I gather that there is a subform. If so, are you
trying to get this number to appear in each subform record, or on the main
form, or what exactly?

From time to time, the Workplan changes and is allocated a new name.
I have a form that contains 2 combo boxes. 1 for the workplan and 2 for
the
workplan section.

I am having difficulty with the last part of the issue number generator.
I created Query1 that contains:
workplan criteria = issueFrm!workplanCombo
workplanSection criteria = issueFrm!workplanSectionCombo
I created Query2 that counts the number of records in Query1.

Private Sub workplanSectionNoCombo_AfterUpdate() triggers the number
generator (see code below)

I created a subform that has itsNoCountQry (Query2) as a Record Source.

For some reason, the subform doesn't get updated.
As it stands, the generated number looks like this:

08-06-

Me.Refresh does not work.

Your help is much appreciated.

Private Sub workplanSectionNoCombo_AfterUpdate()
'On Error GoTo Err_workplanSectionNoCombo_AfterUpdate

Dim TheDate As Date

No need for this. Just use Date or Now as needed.

Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3, temp As String

You need to declare each item as a String, otherwise it will be a Variant:
Dim theYear as String, theShortYear as String, etc.

TheDate = Now

Not needed.

theYear = DatePart("yyyy", TheDate)

This could be: theYear = Format(Date,"yy")

theShortYear = Right(theYear, 2)

Not needed. See previous comment.

itsNo_1 = Me!workplanSectionNoCombo.Column(0)
itsNo_2 = theShortYear

Even if you need theShortYear, which you don't from what I can see, why
redefine it?

' DoCmd.OpenQuery "itsNoCountQry"
itsNo_3 = Me!issueCountSubfrm!itsNo_3
itsNo_3Len = Len(itsNo_3)

You've really lost me here. Why are you opening the query? Is itsNo_3
something from a subform?

You could put this sort of thing into the Select Case statement:
Select Case Len(itsNo_3)
However, if itsNo_3 is a number you could define it as such, then apply a
format to save yourself several lines of code. Where you concatenate
itsNo_3 into the string you could have Format(itsNo_3,"000"), and skip all
of the Select Case stuff.

Select Case itsNo_3Len
Case 1
itsNo_3 = "00" & itsNo_3
Case 2
itsNo_3 = "0" & itsNo_3
End Select

Me!WorkplanSection = Me!workplanSectionNoCombo.Column(1)

If your combo box has a visible column you can just reference the combo box
when you need that value.

Me![goal] = Me!workplanSectionNoCombo.Column(2)
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

Exit_workplanSectionNoCombo_AfterUpdate:
Exit Sub

Err_workplanSectionNoCombo_AfterUpdate:
MsgBox Err.Description
Resume Exit_workplanSectionNoCombo_AfterUpdate
End Sub

This first query gets the needed information from the itsTbl table.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query generates the relevant number.
This query is the record source for the subform.

itsNoCountQry

 

[BruceM]

This can all be done, but I still need to understand some things.

1) First, in non-database language, what are you trying to do? If you
could provide some pared-down examples it would help. In describing it,
remember that Workplan, workplansection, etc. mean little to me.

2) Is this correct: Each workplan may contain many sections, but a section
is associated with just one workplan?

3) itsTbl apparently is for issue information, but what is that, and how
does it relate to workplan and workplan section. I can't tell for sure is
WorkplanNo and WorkplanSectionNo are part of itsTbl. If they are, is itsTbl
related to the other two tables through these fields? If so, to what end?

4) Somebody may jump in and disagree with me here, but I don't think
queries are an effective way to go about finding the number. In general you
will probably be better off with something like this in the combo box After
Update event:

If Me.NewRecord Then
Me.YourNumber = DMax("YourNumber","YourTable") + 1
End If

I'm vague about the table and field names here since I'm not quite sure
which table is involved. Since there are three parts to the number it is
more complex than this simple equation, but in general this is how you will
increment

5) Give some examples of numbers that will be produced. For instance,
after 08-06-001, when you choose workplan 07 is the number 07-06-001, or is
it 07-06-002? In other words, is the end of the number supposed to start
over with 001 for each Workplan, or how does that work?

In general, I will need to try to see how everything fits together. In
particular, you are going to a form that adds a record to itsTbl, but what
is the rationale for selecting a workplan and a workplanSection while you
are on the Issue form?

Hi Bruce,
Thank you for your help with this issue.

The issue tracking system has to have the capability to generate an issue
number automatically.
The issue number has 3 parts.

The first part: itsNo_1 represents the workplan section. More on this
later.

The second part represents the year during whice the issue was created.
Thanks for your comments regarding this part.

The third part: itsNo_3 represents the number of issues for a particular
workplan section.

Here is the way I am trying to generate this number:
All workplan sections belong to a workplan.
The workplan changes from time to time.
The workplan number and workplan title are stored in a table:

TABLE workplanTbl
--------
workplanNo Represents the workplan number Text 3 key
workplan Workplan title Text 50

Each workplan is divided into workplan 10 sections.

TABLE workplanSectionTbl
--------
workplanSectionNo Represents the workplan section number Text 3 key
workplanSection Workplan title
Text 50

TABLE itsTbl
--------
...
itsNo Text 12 Represents the issue number
Text 12 key
...
workplanNo Represents the workplan number Text
3
workplanSectionNo Represents the workplan section number Text 3
...

I created form itsFrm to input each issue information.
The form's control Source is itsTbl.
2 different fields contain the workplan and workplan section related to
the
issue.
I created 2 combo boxes for the user to select the workplan and workplan
section:

Combo box 1:
Name: workplanNoCombo
Control Source:workplan From workplanTbl
RowSource:
SELECT workplanTbl.workplanNo, workplanTbl.workplan
FROM workplanTbl
ORDER BY workplanTbl.workplanNo;

Combo box 2:
Name: workplanSectionNoCombo
Control Source:workplanSection From workplanSectionTbl
RowSource:
SELECT workplanSectionTbl.workplanSectionNo, workplanSectionTbl.workplan
FROM workplanSectionTbl
ORDER BY workplanSectionTbl.workplanSectionNo;

I created subform issueCountSubfrm which is attached to itsFrm
The record source for the subform is the combination of 2 queries.

The first query extracts the workplan and workplan section selected by the
user in the combo boxes of itsFrm.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query calculates the maximum value of the third part of the
issue
number from the first query.

itsNoCountQry
-------------
SELECT Max(Right([itsNoCount1Qry].[itsNo],3))+1 AS itsNo_3
FROM itsNoCount1Qry;

issueCountSubfrm Record Source: itsNoCountQry

The After Update event of workplanSectionNoCombo (itsFrm) contains the
code
used to generate the issue number.
...
Dim TheDate As Date
Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3 As String

TheDate = Now
theYear = DatePart("yyyy", TheDate)
theShortYear = Right(theYear, 2)
' Generating the first part of the issue number.
' itsNo_1 comes from the selection the user made
' in workplanSectionNoCombo of itsFrm
itsNo_1 = Me!workplanSectionNoCombo.Column(0)
' The second part of the issue number is generated by date manipulation
itsNo_2 = theShortYear
...
' Here, I am getting the third part of the issue number from the subform
itsNo_3 = Me!issueCountSubfrm!itsNo_3
' the third part of the issue number always contains 3 characters.
' Here I am buffering the third part of the issue number depending od
the
size of itsNo_3Len
itsNo_3Len = Len(itsNo_3)
Select Case itsNo_3Len
Case 1
' The number has 1 digit
itsNo_3 = "00" & itsNo_3
Case 2
' The number has 2 digits
itsNo_3 = "0" & itsNo_3
End Select
...
' Assembling the 3 parts of the isue number
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

I hope that you can make heads or tails fro all this.
Let me know if you have questions
--
Cheers,
John

You probably would do this in a form event rather than in a query. More
comments and questions inline. The comments in the code are intended to
point out a few things, but without more information there is really no
way
to know how to proceed.

Hi,
I am trying to write an automatic number generator for an issue
tracking
system.
The number should look like this:

08-06-003

Where 08 represents the workplan section related to the issue. 06
represents
the year during which the issue was created. 003 is the number of the
issue
in this section.

We can't see your database. Some more explanation is needed. What is
the
workplan section, and what is the connection between it and the issue?
Are
there related tables, or is this all one table? From some things you
wrote
later in your posting I gather that there is a subform. If so, are you
trying to get this number to appear in each subform record, or on the
main
form, or what exactly?

From time to time, the Workplan changes and is allocated a new name.
I have a form that contains 2 combo boxes. 1 for the workplan and 2 for
the
workplan section.

I am having difficulty with the last part of the issue number
generator.
I created Query1 that contains:
workplan criteria = issueFrm!workplanCombo
workplanSection criteria = issueFrm!workplanSectionCombo
I created Query2 that counts the number of records in Query1.

Private Sub workplanSectionNoCombo_AfterUpdate() triggers the number
generator (see code below)

I created a subform that has itsNoCountQry (Query2) as a Record Source.

For some reason, the subform doesn't get updated.
As it stands, the generated number looks like this:

08-06-

Me.Refresh does not work.

Your help is much appreciated.

Private Sub workplanSectionNoCombo_AfterUpdate()
'On Error GoTo Err_workplanSectionNoCombo_AfterUpdate

Dim TheDate As Date

No need for this. Just use Date or Now as needed.

Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3, temp As String

You need to declare each item as a String, otherwise it will be a
Variant:
Dim theYear as String, theShortYear as String, etc.

TheDate = Now

Not needed.

theYear = DatePart("yyyy", TheDate)

This could be: theYear = Format(Date,"yy")

theShortYear = Right(theYear, 2)

Not needed. See previous comment.

itsNo_1 = Me!workplanSectionNoCombo.Column(0)
itsNo_2 = theShortYear

Even if you need theShortYear, which you don't from what I can see, why
redefine it?

' DoCmd.OpenQuery "itsNoCountQry"
itsNo_3 = Me!issueCountSubfrm!itsNo_3
itsNo_3Len = Len(itsNo_3)

You've really lost me here. Why are you opening the query? Is itsNo_3
something from a subform?

You could put this sort of thing into the Select Case statement:
Select Case Len(itsNo_3)
However, if itsNo_3 is a number you could define it as such, then apply a
format to save yourself several lines of code. Where you concatenate
itsNo_3 into the string you could have Format(itsNo_3,"000"), and skip
all
of the Select Case stuff.

Select Case itsNo_3Len
Case 1
itsNo_3 = "00" & itsNo_3
Case 2
itsNo_3 = "0" & itsNo_3
End Select

Me!WorkplanSection = Me!workplanSectionNoCombo.Column(1)

If your combo box has a visible column you can just reference the combo
box
when you need that value.

Me![goal] = Me!workplanSectionNoCombo.Column(2)
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

Exit_workplanSectionNoCombo_AfterUpdate:
Exit Sub

Err_workplanSectionNoCombo_AfterUpdate:
MsgBox Err.Description
Resume Exit_workplanSectionNoCombo_AfterUpdate
End Sub

This first query gets the needed information from the itsTbl table.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query generates the relevant number.
This query is the record source for the subform.

itsNoCountQry

 

[John Campeau]

I tried to answer your questions the best I could.

1)
I was asked to write an "issue tracking system" for a political organization.
The issues are tracked by "workplan" and workplan sections"
A workplan is a plan of action for a certain period of time. In this case,
Workplans don't have a fixed time period.
Typically, a workplan lasts between 6 months and 3 years.

Here are some examples of workplan entries in the "Worplan" Table
TABLE workplanTbl
--------
workplanNo workplan
-----------------------
01 Work With The People Jan 2004
02 Working For The Future May 2005
03 Taking Care Of Our Youth Dec 2005
....

A "workplan section" table is a way to categorise the issues.
There are always 10 workplan sections. These are stable. They never change.
A workpaln section may store Health issues. Another section may store
Education issues, another deals with Political issues...

TABLE workplanSection
--------
workplanSectionNo workplanSection
--------------------------------------
01 Health
02 Education
03 Political issue
....

Another relevant table is the issue table.
Here are the relevant fields of the issue table

TABLE itsTbl
--------
itsNo workplanNo workplanSectionNo
----------------------------------------
01-04-001 01 01 ' First issue in "Health" deaprtment
in 2004
01-04-002 01 01 ' Second issue in "Health" deaprtment
in 2004
02-04-001 01 02 ' First issue in "Education"
deaprtment in 2004
02-05-001 01 03 ' First issue in "Education"
deaprtment in 2005

The First part of the issue number represents the workplan section under
which the issue is stored.
The second part of the issue number represents the year during which the
issue was created.
The third part of the issue number is the issue number as it relates to the
issue section and the year during which it was created.

2)
Each workplan may contain 0 to 10 workplan sections.
Each workplan section may be included in any of the workplans.

3)
The itsTbl contains a lot of information relating to each issue.

3 relevant fields:
itsNo ----------------> issue number
workplanNo ---------> linked to workplanTbl.workplanNo
workplanSectionNo--> linked to workplanSectionTbl.workplanSectionNo

4)
I think that the code you are showing me in this section will generate
numbers relating to the whole its table.
This is not what I am trying to accomplish.
its numbers must relate to a worplan section and a year of creation of the
issue.
See section 1 of this reply for an explaination of the issue number
generation.

5)
Each issue number is related to the workplan section and year of issue
created.
See section 1 of this reply for an explaination of the issue number
generation (itsTbl).

Actually, the form in question is the New Issue form.
The user will go to this form to create a new issue entry.

As you can probably tell, I am a bit green with regards to Access.
Don't hesitate to let me know if you think my reasoning or the way I chose
to resolve this problem is flawed.
Thanks again,
--
Cheers,
Grease

This can all be done, but I still need to understand some things.

1) First, in non-database language, what are you trying to do? If you
could provide some pared-down examples it would help. In describing it,
remember that Workplan, workplansection, etc. mean little to me.

2) Is this correct: Each workplan may contain many sections, but a section
is associated with just one workplan?

3) itsTbl apparently is for issue information, but what is that, and how
does it relate to workplan and workplan section. I can't tell for sure is
WorkplanNo and WorkplanSectionNo are part of itsTbl. If they are, is itsTbl
related to the other two tables through these fields? If so, to what end?

4) Somebody may jump in and disagree with me here, but I don't think
queries are an effective way to go about finding the number. In general you
will probably be better off with something like this in the combo box After
Update event:

If Me.NewRecord Then
Me.YourNumber = DMax("YourNumber","YourTable") + 1
End If

I'm vague about the table and field names here since I'm not quite sure
which table is involved. Since there are three parts to the number it is
more complex than this simple equation, but in general this is how you will
increment

5) Give some examples of numbers that will be produced. For instance,
after 08-06-001, when you choose workplan 07 is the number 07-06-001, or is
it 07-06-002? In other words, is the end of the number supposed to start
over with 001 for each Workplan, or how does that work?

In general, I will need to try to see how everything fits together. In
particular, you are going to a form that adds a record to itsTbl, but what
is the rationale for selecting a workplan and a workplanSection while you
are on the Issue form?

Hi Bruce,
Thank you for your help with this issue.

The issue tracking system has to have the capability to generate an issue
number automatically.
The issue number has 3 parts.

The first part: itsNo_1 represents the workplan section. More on this
later.

The second part represents the year during whice the issue was created.
Thanks for your comments regarding this part.

The third part: itsNo_3 represents the number of issues for a particular
workplan section.

Here is the way I am trying to generate this number:
All workplan sections belong to a workplan.
The workplan changes from time to time.
The workplan number and workplan title are stored in a table:

TABLE workplanTbl
--------
workplanNo Represents the workplan number Text 3 key
workplan Workplan title Text 50

Each workplan is divided into workplan 10 sections.

TABLE workplanSectionTbl
--------
workplanSectionNo Represents the workplan section number Text 3 key
workplanSection Workplan title
Text 50

TABLE itsTbl
--------
...
itsNo Text 12 Represents the issue number
Text 12 key
...
workplanNo Represents the workplan number Text
3
workplanSectionNo Represents the workplan section number Text 3
...

I created form itsFrm to input each issue information.
The form's control Source is itsTbl.
2 different fields contain the workplan and workplan section related to
the
issue.
I created 2 combo boxes for the user to select the workplan and workplan
section:

Combo box 1:
Name: workplanNoCombo
Control Source:workplan From workplanTbl
RowSource:
SELECT workplanTbl.workplanNo, workplanTbl.workplan
FROM workplanTbl
ORDER BY workplanTbl.workplanNo;

Combo box 2:
Name: workplanSectionNoCombo
Control Source:workplanSection From workplanSectionTbl
RowSource:
SELECT workplanSectionTbl.workplanSectionNo, workplanSectionTbl.workplan
FROM workplanSectionTbl
ORDER BY workplanSectionTbl.workplanSectionNo;

I created subform issueCountSubfrm which is attached to itsFrm
The record source for the subform is the combination of 2 queries.

The first query extracts the workplan and workplan section selected by the
user in the combo boxes of itsFrm.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query calculates the maximum value of the third part of the
issue
number from the first query.

itsNoCountQry
-------------
SELECT Max(Right([itsNoCount1Qry].[itsNo],3))+1 AS itsNo_3
FROM itsNoCount1Qry;

issueCountSubfrm Record Source: itsNoCountQry

The After Update event of workplanSectionNoCombo (itsFrm) contains the
code
used to generate the issue number.
...
Dim TheDate As Date
Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3 As String

TheDate = Now
theYear = DatePart("yyyy", TheDate)
theShortYear = Right(theYear, 2)
' Generating the first part of the issue number.
' itsNo_1 comes from the selection the user made
' in workplanSectionNoCombo of itsFrm
itsNo_1 = Me!workplanSectionNoCombo.Column(0)
' The second part of the issue number is generated by date manipulation
itsNo_2 = theShortYear
...
' Here, I am getting the third part of the issue number from the subform
itsNo_3 = Me!issueCountSubfrm!itsNo_3
' the third part of the issue number always contains 3 characters.
' Here I am buffering the third part of the issue number depending od
the
size of itsNo_3Len
itsNo_3Len = Len(itsNo_3)
Select Case itsNo_3Len
Case 1
' The number has 1 digit
itsNo_3 = "00" & itsNo_3
Case 2
' The number has 2 digits
itsNo_3 = "0" & itsNo_3
End Select
...
' Assembling the 3 parts of the isue number
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

I hope that you can make heads or tails fro all this.
Let me know if you have questions
--
Cheers,
John

You probably would do this in a form event rather than in a query. More
comments and questions inline. The comments in the code are intended to
point out a few things, but without more information there is really no
way
to know how to proceed.

Hi,
I am trying to write an automatic number generator for an issue
tracking
system.
The number should look like this:

08-06-003

Where 08 represents the workplan section related to the issue. 06
represents
the year during which the issue was created. 003 is the number of the
issue
in this section.
We can't see your database. Some more explanation is needed. What is
the
workplan section, and what is the connection between it and the issue?
Are
there related tables, or is this all one table? From some things you
wrote
later in your posting I gather that there is a subform. If so, are you
trying to get this number to appear in each subform record, or on the
main
form, or what exactly?


From time to time, the Workplan changes and is allocated a new name.
I have a form that contains 2 combo boxes. 1 for the workplan and 2 for
the
workplan section.

I am having difficulty with the last part of the issue number
generator.
I created Query1 that contains:
workplan criteria = issueFrm!workplanCombo
workplanSection criteria = issueFrm!workplanSectionCombo
I created Query2 that counts the number of records in Query1.

Private Sub workplanSectionNoCombo_AfterUpdate() triggers the number
generator (see code below)

I created a subform that has itsNoCountQry (Query2) as a Record Source.

For some reason, the subform doesn't get updated.
As it stands, the generated number looks like this:

08-06-

Me.Refresh does not work.

Your help is much appreciated.

Private Sub workplanSectionNoCombo_AfterUpdate()
'On Error GoTo Err_workplanSectionNoCombo_AfterUpdate

Dim TheDate As Date
No need for this. Just use Date or Now as needed.

Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3, temp As String
You need to declare each item as a String, otherwise it will be a
Variant:
Dim theYear as String, theShortYear as String, etc.

TheDate = Now
Not needed.

theYear = DatePart("yyyy", TheDate)
This could be: theYear = Format(Date,"yy")

theShortYear = Right(theYear, 2)
Not needed. See previous comment.

itsNo_1 = Me!workplanSectionNoCombo.Column(0)
itsNo_2 = theShortYear
Even if you need theShortYear, which you don't from what I can see, why
redefine it?

' DoCmd.OpenQuery "itsNoCountQry"
itsNo_3 = Me!issueCountSubfrm!itsNo_3
itsNo_3Len = Len(itsNo_3)

You've really lost me here. Why are you opening the query? Is itsNo_3
something from a subform?

You could put this sort of thing into the Select Case statement:
Select Case Len(itsNo_3)
However, if itsNo_3 is a number you could define it as such, then apply a
format to save yourself several lines of code. Where you concatenate
itsNo_3 into the string you could have Format(itsNo_3,"000"), and skip
all
of the Select Case stuff.

Select Case itsNo_3Len
Case 1
itsNo_3 = "00" & itsNo_3
Case 2
itsNo_3 = "0" & itsNo_3
End Select

Me!WorkplanSection = Me!workplanSectionNoCombo.Column(1)
If your combo box has a visible column you can just reference the combo
box
when you need that value.

Me![goal] = Me!workplanSectionNoCombo.Column(2)
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

Exit_workplanSectionNoCombo_AfterUpdate:
Exit Sub

Err_workplanSectionNoCombo_AfterUpdate:
MsgBox Err.Description
Resume Exit_workplanSectionNoCombo_AfterUpdate
End Sub

 

[BruceM]

You wrote two things at different times. First: "There are always 10
workplan sections. These are stable. They never change."
Then: "Each workplan may contain 0 to 10 workplan sections. Each workplan
section may be included in any of the workplans."
These seem to contradict each other. Once this is cleared up we can find a
way to proceed.
I provided the DMax example to show in general how a number can be
incremented. If there is a workplan table, and if it can have any number of
issues (which are recorded in itsTbl) for each workplan, then you would
create a one-to-many relationship between workplanTbl and itsTbl. For data
entry you could create a main form based on workplanTbl and a subform based
on itsTbl. The subform could have a text box bound to the field in itsTbl
that needs to increment. The subform's Current event could contain code
similar to what I posted, except there would be a criteria. All of the
subform records would share the identifying number from the main form. If
the main form's unique identifier is the WorkplanID number, that number
appears in each subform record that is related to that particular Workplan.
If the WorkplanID is 10, each related record in tblIts will have 10 in its
WorkplanID field. The code will be looking for the subform record with 10
in the WorkplanID field (there could be many) that has the largest number in
the Incremented field, then will add 1 to that Incremented number.
I realize that this is probably bewildering right now, but there is some
complexity to your project. For one thing, you have not described table
relationships. It would help if you can sum up the database structure (not
just the tables, but how they are related).
The good news is that this is not as complex as you may have thought. For
one thing, you will need to concatenate the number for display purposes
only; you don't need to create a multi-part number. The other news, though,
is that you need a properly-designed database for this to work.

I tried to answer your questions the best I could.

1)
I was asked to write an "issue tracking system" for a political
organization.
The issues are tracked by "workplan" and workplan sections"
A workplan is a plan of action for a certain period of time. In this case,
Workplans don't have a fixed time period.
Typically, a workplan lasts between 6 months and 3 years.

Here are some examples of workplan entries in the "Worplan" Table
TABLE workplanTbl
--------
workplanNo workplan
-----------------------
01 Work With The People Jan 2004
02 Working For The Future May 2005
03 Taking Care Of Our Youth Dec 2005
...

A "workplan section" table is a way to categorise the issues.
There are always 10 workplan sections. These are stable. They never
change.
A workpaln section may store Health issues. Another section may store
Education issues, another deals with Political issues...

TABLE workplanSection
--------
workplanSectionNo workplanSection
--------------------------------------
01 Health
02 Education
03 Political issue
...

Another relevant table is the issue table.
Here are the relevant fields of the issue table

TABLE itsTbl
--------
itsNo workplanNo workplanSectionNo
----------------------------------------
01-04-001 01 01 ' First issue in "Health"
deaprtment
in 2004
01-04-002 01 01 ' Second issue in "Health"
deaprtment
in 2004
02-04-001 01 02 ' First issue in "Education"
deaprtment in 2004
02-05-001 01 03 ' First issue in "Education"
deaprtment in 2005

The First part of the issue number represents the workplan section under
which the issue is stored.
The second part of the issue number represents the year during which the
issue was created.
The third part of the issue number is the issue number as it relates to
the
issue section and the year during which it was created.

2)
Each workplan may contain 0 to 10 workplan sections.
Each workplan section may be included in any of the workplans.

3)
The itsTbl contains a lot of information relating to each issue.

3 relevant fields:
itsNo ----------------> issue number
workplanNo ---------> linked to workplanTbl.workplanNo
workplanSectionNo--> linked to workplanSectionTbl.workplanSectionNo

4)
I think that the code you are showing me in this section will generate
numbers relating to the whole its table.
This is not what I am trying to accomplish.
its numbers must relate to a worplan section and a year of creation of the
issue.
See section 1 of this reply for an explaination of the issue number
generation.

5)
Each issue number is related to the workplan section and year of issue
created.
See section 1 of this reply for an explaination of the issue number
generation (itsTbl).

Actually, the form in question is the New Issue form.
The user will go to this form to create a new issue entry.

As you can probably tell, I am a bit green with regards to Access.
Don't hesitate to let me know if you think my reasoning or the way I chose
to resolve this problem is flawed.
Thanks again,
--
Cheers,
Grease

This can all be done, but I still need to understand some things.

1) First, in non-database language, what are you trying to do? If you
could provide some pared-down examples it would help. In describing it,
remember that Workplan, workplansection, etc. mean little to me.

2) Is this correct: Each workplan may contain many sections, but a
section
is associated with just one workplan?

3) itsTbl apparently is for issue information, but what is that, and how
does it relate to workplan and workplan section. I can't tell for sure
is
WorkplanNo and WorkplanSectionNo are part of itsTbl. If they are, is
itsTbl
related to the other two tables through these fields? If so, to what
end?

4) Somebody may jump in and disagree with me here, but I don't think
queries are an effective way to go about finding the number. In general
you
will probably be better off with something like this in the combo box
After
Update event:

If Me.NewRecord Then
Me.YourNumber = DMax("YourNumber","YourTable") + 1
End If

I'm vague about the table and field names here since I'm not quite sure
which table is involved. Since there are three parts to the number it is
more complex than this simple equation, but in general this is how you
will
increment

5) Give some examples of numbers that will be produced. For instance,
after 08-06-001, when you choose workplan 07 is the number 07-06-001, or
is
it 07-06-002? In other words, is the end of the number supposed to start
over with 001 for each Workplan, or how does that work?

In general, I will need to try to see how everything fits together. In
particular, you are going to a form that adds a record to itsTbl, but
what
is the rationale for selecting a workplan and a workplanSection while you
are on the Issue form?

Hi Bruce,
Thank you for your help with this issue.

The issue tracking system has to have the capability to generate an
issue
number automatically.
The issue number has 3 parts.

The first part: itsNo_1 represents the workplan section. More on this
later.

The second part represents the year during whice the issue was created.
Thanks for your comments regarding this part.

The third part: itsNo_3 represents the number of issues for a
particular
workplan section.

Here is the way I am trying to generate this number:
All workplan sections belong to a workplan.
The workplan changes from time to time.
The workplan number and workplan title are stored in a table:

TABLE workplanTbl
--------
workplanNo Represents the workplan number Text 3 key
workplan Workplan title Text 50

Each workplan is divided into workplan 10 sections.

TABLE workplanSectionTbl
--------
workplanSectionNo Represents the workplan section number Text 3
key
workplanSection Workplan title
Text 50

TABLE itsTbl
--------
...
itsNo Text 12 Represents the issue number
Text 12 key
...
workplanNo Represents the workplan number
Text
3
workplanSectionNo Represents the workplan section number Text 3
...

I created form itsFrm to input each issue information.
The form's control Source is itsTbl.
2 different fields contain the workplan and workplan section related to
the
issue.
I created 2 combo boxes for the user to select the workplan and
workplan
section:

Combo box 1:
Name: workplanNoCombo
Control Source:workplan From workplanTbl
RowSource:
SELECT workplanTbl.workplanNo, workplanTbl.workplan
FROM workplanTbl
ORDER BY workplanTbl.workplanNo;

Combo box 2:
Name: workplanSectionNoCombo
Control Source:workplanSection From workplanSectionTbl
RowSource:
SELECT workplanSectionTbl.workplanSectionNo,
workplanSectionTbl.workplan
FROM workplanSectionTbl
ORDER BY workplanSectionTbl.workplanSectionNo;

I created subform issueCountSubfrm which is attached to itsFrm
The record source for the subform is the combination of 2 queries.

The first query extracts the workplan and workplan section selected by
the
user in the combo boxes of itsFrm.

itsNoCount1Qry
--------------
SELECT itsTbl.itsNo, itsTbl.workplanNo, itsTbl.workplanSectionNo
FROM itsTbl
WHERE (((itsTbl.workplanNo)=[Forms]![itsNewFrm]![workplanNoCombo]) AND
((itsTbl.workplanSectionNo)=[Forms]![itsNewFrm]![workplanSectionNoCombo]));

The second query calculates the maximum value of the third part of the
issue
number from the first query.

itsNoCountQry
-------------
SELECT Max(Right([itsNoCount1Qry].[itsNo],3))+1 AS itsNo_3
FROM itsNoCount1Qry;

issueCountSubfrm Record Source: itsNoCountQry

The After Update event of workplanSectionNoCombo (itsFrm) contains the
code
used to generate the issue number.
...
Dim TheDate As Date
Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3 As String

TheDate = Now
theYear = DatePart("yyyy", TheDate)
theShortYear = Right(theYear, 2)
' Generating the first part of the issue number.
' itsNo_1 comes from the selection the user made
' in workplanSectionNoCombo of itsFrm
itsNo_1 = Me!workplanSectionNoCombo.Column(0)
' The second part of the issue number is generated by date
manipulation
itsNo_2 = theShortYear
...
' Here, I am getting the third part of the issue number from the
subform
itsNo_3 = Me!issueCountSubfrm!itsNo_3
' the third part of the issue number always contains 3 characters.
' Here I am buffering the third part of the issue number depending od
the
size of itsNo_3Len
itsNo_3Len = Len(itsNo_3)
Select Case itsNo_3Len
Case 1
' The number has 1 digit
itsNo_3 = "00" & itsNo_3
Case 2
' The number has 2 digits
itsNo_3 = "0" & itsNo_3
End Select
...
' Assembling the 3 parts of the isue number
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

I hope that you can make heads or tails fro all this.
Let me know if you have questions
--
Cheers,
John


:

You probably would do this in a form event rather than in a query.
More
comments and questions inline. The comments in the code are intended
to
point out a few things, but without more information there is really
no
way
to know how to proceed.

Hi,
I am trying to write an automatic number generator for an issue
tracking
system.
The number should look like this:

08-06-003

Where 08 represents the workplan section related to the issue. 06
represents
the year during which the issue was created. 003 is the number of
the
issue
in this section.
We can't see your database. Some more explanation is needed. What is
the
workplan section, and what is the connection between it and the issue?
Are
there related tables, or is this all one table? From some things you
wrote
later in your posting I gather that there is a subform. If so, are
you
trying to get this number to appear in each subform record, or on the
main
form, or what exactly?


From time to time, the Workplan changes and is allocated a new name.
I have a form that contains 2 combo boxes. 1 for the workplan and 2
for
the
workplan section.

I am having difficulty with the last part of the issue number
generator.
I created Query1 that contains:
workplan criteria = issueFrm!workplanCombo
workplanSection criteria = issueFrm!workplanSectionCombo
I created Query2 that counts the number of records in Query1.

Private Sub workplanSectionNoCombo_AfterUpdate() triggers the number
generator (see code below)

I created a subform that has itsNoCountQry (Query2) as a Record
Source.

For some reason, the subform doesn't get updated.
As it stands, the generated number looks like this:

08-06-

Me.Refresh does not work.

Your help is much appreciated.

Private Sub workplanSectionNoCombo_AfterUpdate()
'On Error GoTo Err_workplanSectionNoCombo_AfterUpdate

Dim TheDate As Date
No need for this. Just use Date or Now as needed.

Dim theYear, theShortYear, itsNo_1, itsNo_2, itsNo_3, temp As
String
You need to declare each item as a String, otherwise it will be a
Variant:
Dim theYear as String, theShortYear as String, etc.

TheDate = Now
Not needed.

theYear = DatePart("yyyy", TheDate)
This could be: theYear = Format(Date,"yy")

theShortYear = Right(theYear, 2)
Not needed. See previous comment.

itsNo_1 = Me!workplanSectionNoCombo.Column(0)
itsNo_2 = theShortYear
Even if you need theShortYear, which you don't from what I can see,
why
redefine it?

' DoCmd.OpenQuery "itsNoCountQry"
itsNo_3 = Me!issueCountSubfrm!itsNo_3
itsNo_3Len = Len(itsNo_3)

You've really lost me here. Why are you opening the query? Is
itsNo_3
something from a subform?

You could put this sort of thing into the Select Case statement:
Select Case Len(itsNo_3)
However, if itsNo_3 is a number you could define it as such, then
apply a
format to save yourself several lines of code. Where you concatenate
itsNo_3 into the string you could have Format(itsNo_3,"000"), and skip
all
of the Select Case stuff.

Select Case itsNo_3Len
Case 1
itsNo_3 = "00" & itsNo_3
Case 2
itsNo_3 = "0" & itsNo_3
End Select

Me!WorkplanSection = Me!workplanSectionNoCombo.Column(1)
If your combo box has a visible column you can just reference the
combo
box
when you need that value.

Me![goal] = Me!workplanSectionNoCombo.Column(2)
Me!itsNo = itsNo_1 & "-" & itsNo_2 & "-" & itsNo_3

Exit_workplanSectionNoCombo_AfterUpdate:
Exit Sub

Err_workplanSectionNoCombo_AfterUpdate:
MsgBox Err.Description
Resume Exit_workplanSectionNoCombo_AfterUpdate
End Sub