Automatic Number Generator

[Greaser]

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.

I created a subform that has Query2 a a Record Source.
For some reason, Query2 doesn't get updated.
Me.Refresh does not work.

Your help is much appreciated.
Cheers,
John

 

[AlexCuse]

why not set up a query that looks at a particular section/year pairing based on your specified criteria, then returns max(right 3 digits) + 1 as the right 3?

Then you run this query to create the new record prior to opening your subform, and subform looks for the resulting number?

Hope this helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Greaser]

As you suggested, I used Max instead of Count in the subform query:
itsNo_3: Max(Right([itsNo],3))+1
The query works fine, but the subform still does not get updated.

Is there a way to have the issue number generated after updating the workplanSectionCombobox (second combo box)?
Thanks again,
John

 

[AlexCuse]

As farr as your first question is concerned, what is the record source for your subform?

Second question - Yes, what you would need to do is have an append query set up to create a new record set to run in your on update event.

A wise man once said
"The only thing normal about database guys is their tables".

 

[Greaser]

Record source for subform is countNo_3Qry:
itsNo_3: Max(Right([itsNo],3))+1

countNo_3Qry's source is another query that gets its criteria from the issue form 2 combo boxes:
workplan criteria = issueFrm!workplanCombo
workplanSection criteria = issueFrm!workplanSectionCombo

The first query is called on AfterUpdate of issueFrm!workplanSectionCombo
Cheers,
John

 

[AlexCuse]

Can you post the SQL view for the entire query used as the record source for your subform? I am not great with subforms but I will do my best.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Greaser]

First query: itsNoCount1Qry

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


Second query (subform record source) takes its record source from the first query: itsNoCountQry


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