Solved Project Number with Year and serial number YYNNN

[Dark Stranger]

I need your help. I work in a process improvement group in a small division of a large corporation. We have just been notified that the corporate group that created and maintains the project tracking database we use, that it will no longer be supporting it. I have been tasked by my boss with creating a quick Access based replacement. My Access and VBA skills are limited (I'm an old school Excel guy - Primarily just in-cell formulas ), so, i am requesting your help.
What I have done so far:
The DB consists of 4 tables:
tblProjectInfo - a table of the project demographics, status, participants
tblBenefits - a table of the estimated and actual savings from the projects
tblResources - A table of Participant demographics
tblTasks - A table of associated tasks for the project

The primary keys for each table are auto-numbers that users will not see.

Users would enter info for project ideas into the form frmProjectInfo, but a project number would not be generated until the idea is accepted as a project and is ready to go active. I attempted to create a code that would create a 5 digit project number that consists of 2 digit year and three digit serial number (Three digits is enough - we are a small group and will never exceed 999 projects in a year.) The code would be in the "On Click" Event property of a command button on frmProjectInfo. The project number field is named ProjectNumber. There are several dates on tblProjectInfo that could be used to get the year, but i would prefer to use the system date when the button is clicked.

I'm not knowledgeable enough to create the code myself, so i tried to use a few examples I found on earlier threads, but couldn't get them to work.

What code should I use?

Thanks

 

[theDBguy]

I need your help. I work in a process improvement group in a small division of a large corporation. We have just been notified that the corporate group that created and maintains the project tracking database we use, that it will no longer be supporting it. I have been tasked by my boss with creating a quick Access based replacement. My Access and VBA skills are limited (I'm an old school Excel guy - Primarily just in-cell formulas ), so, i am requesting your help.
What I have done so far:
The DB consists of 4 tables:
tblProjectInfo - a table of the project demographics, status, participants
tblBenefits - a table of the estimated and actual savings from the projects
tblResources - A table of Participant demographics
tblTasks - A table of associated tasks for the project

The primary keys for each table are auto-numbers that users will not see.

Users would enter info for project ideas into the form frmProjectInfo, but a project number would not be generated until the idea is accepted as a project and is ready to go active. I attempted to create a code that would create a 5 digit project number that consists of 2 digit year and three digit serial number (Three digits is enough - we are a small group and will never exceed 999 projects in a year.) The code would be in the "On Click" Event property of a command button on frmProjectInfo. The project number field is named ProjectNumber. There are several dates on tblProjectInfo that could be used to get the year, but i would prefer to use the system date when the button is clicked.

I'm not knowledgeable enough to create the code myself, so i tried to use a few examples I found on earlier threads, but couldn't get them to work.

What code should I use?

Thanks

Hi. What I would suggest is have two separate fields for the components of your project number. One would be a date/time field to store when the project was approved, and the other one is a number field to store the sequential number for the project. Combining and formatting the two information together to present to the user is easy. Leaving the numeric part separate will make it easy for you to create the sequential number and also to restart the count in the new year.

 

[Bullschmidt]

I attempted to create a code that would create a 5 digit project number that consists of 2 digit year and three digit serial number (Three digits is enough - we are a small group and will never exceed 999 projects in a year.) The code would be in the "On Click" Event property of a command button on frmProjectInfo. The project number field is named ProjectNumber. There are several dates on tblProjectInfo that could be used to get the year, but i would prefer to use the system date when the button is clicked.

I'm not knowledgeable enough to create the code myself, so i tried to use a few examples I found on earlier threads, but couldn't get them to work.

What code should I use?

Maybe you don't even have to deal with the year except for the first entry of each year which you could manually correct.

For example data in a ProjectNumber number field could be something like this:
20001
20002
20003
20004

And then to calculate the next ProjectNumber you could find the current maximum and then add 1 perhaps using something like this:

=DMax("[ProjectNumber]","tblProjectInfo")+1

And in this case the result would be 20005.

And theDBGuy has some good ideas too, and if you do take his ideas you could still probably use a DMax plus 1...

 

[Dark Stranger]

Thanks for the help. I borrowed some code from another thread that did what you guys described, and modified it to work for me,
I set up two text fields in my table- tblProjectInfo. - BaseID and YearID. Then I added two corresponding hidden text boxes on the Data entry form. I also added a Command Button on the data entry form to Assign A Project Number. On the On Click property for this button i added the code:

Private Sub AssignProjectNumber_Click()
If IsNull (Me! [BaseID]) Then
Me! [BaseID = Format (Nz(DMax("[BaseID]", "[tblProjectInfo]", "[YearID]='" & Format(Now(),"YY") & "'"), 0) +1)
End If
Me! [ProjectNumber} = Format(Now(),"YY") & Format([BaseID], "000")

One thing is strange. When I click the button, the project number does not instantly appear. I have to scroll down and then back up and the number appears. Also the entry does not appear in the table until i close the entry form. Strange!

 

[June7]

Posted code should error since there is a } character where should be ].

Value will not show in table until record is committed. Record is committed when closing table/query/form or moving to another record or run code to save.

Generating this value at beginning of record entry and immediately committing to table has risk of multiple users generating same number and causing conflict error.