autonumbering projects but in a different way

[PK.10987]

Programme > Subject > Project

This is the way we classify our projects.

There are different programme categories marked by a programme code.
Each programme has different subjects marked by a subject code.
Each subject has different projects with a project number.
The programme and subject codes and selected by the user via combobox. There
is no issues for that.

What we need is that when move into a new subject area the project numbering
should start from 01 and be incremented by 01 consecutively for projects
under the same subject.

For example look at this code.
D1.10.01 (D1 is the prog, 10 is the subj and 01 is proj number)
D1.20.01 (it's still prog D1, sub is different here; now its' 20 and the
proj number01)

I have a field with coding D1.10, D2.10, D3.10 in my tables
How can i create a project numbering system using the above values? I also
have the programme code and subject code in seperate fields.

One of my friends told me to use a combination of NZ and DMAX function for
the case when you treat prog code and subj code seperately. But I didn't
quite understand that.

Please give me suggestions on that and if there are any other way of solving
this issue.

Thanks,
PK

 

[Jay]

Hi,

I am pretty new to Access, and have only used Access 2007, so this is a
"suggestion if you are using Access 2007" reply rather than a "I know this
will work in any version of Access" reply.

Put a hidden ComboBox on the form with a Row Source set to something like
"SELECT MAX(ProjectNumber) FROM ProjectFile WHERE ProgrammeAndSubjectField =
Forms![This Form].ProgrammeAndSubjectField"

In the form's "BeforeUpdate" event, write some Visual Basic code to:
1) Requery the hidden ComboBox
2) Check if the hidden ComboBox has >0 items
3) Assign to the ProjectNumber field the value of the first hidden ComboBox
item + 1

As I said, I am pretty new to Access, so there may be an easier way!

Jay

 

[Ken Sheridan]

Storing the Programme/Subject codes in a single column as you seem to be
doing, put code in the AfterUpdate event procedure of the control on your
form bound to this column, e.g.

Dim ctrl As Control
Dim strCriteria as Sting

Set ctrl = Me.ActiveControl

If Me.NewRecord Then
If Not IsNull(ctrl) Then
strCriteria = "[ProgrammeSubjectCode] = """ & ctrl & """"

Me.[ProjectCode] = _
Nz(DMax("[ProjectCode]", "[YourTable]", strCriteria),0)+1
End If
End If

where ProgrammeSubjectCode is the name of the column containing the
programme/subject code combination and ProgramSubjectCode is the name of the
column containing the serial numbers per program/subject.

The ProjectCode column would best be an integer number data type. You can
return the full code formatted with a leading zero for the project code with:

[ProgrammeSubjectCode] & Format([ProjectCode],"00")

If ProjectCode is a text data type and you are storing the leading zeros
then the above code would need amending as follows:

Me.[ProjectCode] = _
Format(Nz(DMax("[ProjectCode]", "[YourTable]",
strCriteria),0)+1,"00")


However, I'd recommend splitting the programme and subject codes over two
columns, ProgrammeCode and SubjectCode say, the former text data type the
latter integer number data type. The code would then go in the AfterUpdate
event procedures of both controls on the form and would be like this:

Dim ctrl1 As Control, ctrl2 As Control
Dim strCriteria as Sting

Set ctrl1 = Me.[ProgrammeCode]
Set ctrl2 = Me.[SubjectCode]

If Me.NewRecord Then
If Not IsNull(ctrl1) And Not IsNull(ctrl2) Then
strCriteria = "[ProgrammeCode] = """ & ctrl1 & """ And " & _
"[SubjectCode] = " & ctrl2

Me.[ProjectCode] = _
Nz(DMax("[ProjectCode]", "[YourTable]", strCriteria),0)+1
End If
End If

With separate columns the full code can be returned with:

[ProgrammeCode] & "." & Format([SubjectCode],"00") & "." &
Format([ProjectCode],"00")

The last is a single line; it might well be split over two lines by your
newsreader.

Ken Sheridan
Stafford, England