Don't expect to have more than 100 projects for each ProjectType?
What is your fiscal year? Rely on user to select fiscal year - don't want to calculate it based on current date?
Arnelgp........... I learn so much from you!
Thank you sir.
I am officially following you now.
....somehow that only sounds right to say when on a forum.
I'm going to have to spend more time learning the code that you wrote in the VBA editor, SF_Location, that increases the project number by one.
But it looks fantastic!
Don't expect to have more than 100 projects for each ProjectType?
What is your fiscal year? Rely on user to select fiscal year - don't want to calculate it based on current date?
No. Shouldn't get even close to 100 per Location per FY per Project Type.
Actually I want the ability to select it because I've determined that it will be the FY in which the project will receive funding. It could very well be a future year.
I'm needing to modify the solution since users need to sometimes modify an existing Bergen Number.
Once this happens, I need the code to again check for the max Project Type number for that specific change and update the Bergen Number.
The check would need to happen for a change to any of the five combo boxes.
I am stuck on how to do this?
Attached is where I'm at with this, but I know it's not working.
Any help would be appreciated.
Ok here's more detail on the problem.
I have form below call F_Project.
One textbox for Project Name, five combo boxes, and another textbox to store the generated Bergen Number.
I have a public function called genBerger that is called for in the After Update event of the textbox ProjectName and the five combo boxes.
Code:
Public Function genBerger() As String
Dim strBerger As String
strBerger = strBerger & "GP-"
If Len(Me!cboProjectFY & "") > 0 Then
strBerger = strBerger & Me!cboProjectFY.Column(1) & "-"
Else
strBerger = strBerger & "??-"
End If
If Len(Me!cboLocation & "") > 0 Then
strBerger = strBerger & Me!cboLocation.Column(3) & "-"
Else
strBerger = strBerger & "??-"
End If
If Len(Me!cboProjectType & "") > 0 Then
strBerger = strBerger & Me!cboProjectType.Column(2) & "-"
Else
strBerger = strBerger & "???-"
End If
If Len(Me!cboFacilityType & "") > 0 Then
strBerger = strBerger & Me!cboFacilityType.Column(2) & "-"
Else
strBerger = strBerger & "?-"
End If
If Len(Me!cboFundingType & "") > 0 Then
strBerger = strBerger & Me!cboFundingType.Column(2)
Else
strBerger = strBerger & "?"
End If
genBerger = strBerger
End Function
'This is in the After Update event for FY, ProjectType, FundingType, & FacilityType
Public Function fncUpdateBerger()
Me!txtBergenNumber = genBerger()
End Function
This generates the number but does not find the next number for the Project Type.
I then have the following code in the forms After Update event.
Code:
Private Sub Form_AfterUpdate()
Dim strBergen As String
Dim varArr As Variant
Dim intSeries As Integer
strBergen = Forms("F_Project").txtBergenNumber
varArr = Split(strBergen, "-")
intSeries = Val(varArr(3))
Do Until DCount("1", "T_Project", "BergenNumber='" & strBergen & "'") = 0
intSeries = intSeries + 1
varArr(3) = Format(intSeries, "000")
strBergen = Join(varArr, "-")
Loop
Forms("F_Project").txtBergenNumber = strBergen
End Sub
This is to generate the lowest available number for the specific project type.
But this is where I run into problems.
One form remains dirty even when I click on the record selector to save it.
When I close the form it gives me this error.
There's other problems too. Like, if I click the record selector multiple times when the F_Project is open, it grabs the next available number and puts it in the Bergen Number. But, I think if I can figure what is happening above, this problem will be easier to rectify.
I also attached a copy of the latest database for anyone to look at.
If someone can help me out with the above that would be great.
Ok I've been working on this for hours and I'm so close I can taste it....
But I need some help.
I basically got the database to do what I want it to do but, for some reason I cannot get the F_Project to save after all the fields are populate or if there is a change in one of the combo boxes.
So the form basically looks like this once populated. Notice it's dirty.
If I click on the record selector, it saves and bingo! the Bergen Number is updated and all is fine with the world.
I tried a few things but no luck.
Can someone take a look and see where I'm going wrong?
Attached is the small database.
Thanks.
What few things have you tried? What does 'no luck' mean - error message, wrong result, nothing happens?
Record entry/edit is committed when: 1) close table/query/form or 2) move to another record or 3) run code to save. Committing by just clicking on record selector of the dirty record is something I never noticed before so I guess that is 4th method. Have you tried 1 through 3?
The form is in single form view and I want to be able to see the generated Bergen Number prior to closing the form, so #1 & #2 will not work for me. That said, if I do move to the next record and then back again after making a change, it does show the correct Bergen Number.
As far as #3, I've tried DoCmd.RunCommand acCmdSaveRecord, DoCmd.Save, also If Me.Dirty then Me.Dirty = False. All in multiple places of the code but can't get it work.
It's probable in the way I've set it up and the order of how things are firing but, I can't seem to figure it out.
Can you take a look at the db and see something I'm missing?
Thanks for the help.
Yes, Yes, Yes! Thanks Arnelp!
I dug into your solution and I have a couple of questions.
One of the changes you made was to declare the m_strBergen at the top, I think as either a public or global variable.
Code:
Option Compare Database
Dim strBergen As String
When the code executes for a single record, does the m_strBergen first get defined by genBerger and then the following code is able to grab that same data since it is a global variable?
Second, you added the DoEvents after the loop.
I don't understand what this is doing for me.
I read up on it and it's suppose to return a zero in this instance, I believe.
'What does DoEvents do for me in this instance?
Code:
Private Sub fncCheck()
Dim l_strBergen As String
Dim varArr As Variant
Dim intSeries As Integer
intSeries = 1
varArr = Split(m_strBergen, "-")
intSeries = Val(varArr(3))
Do Until DCount("1", "T_Project", "BergenNumber='" & m_strBergen & "'") = 0
intSeries = intSeries + 1
varArr(3) = intSeries
m_strBergen = Join(varArr, "-")
Loop
Me!txtBergenNumber = m_strBergen
DoEvents
End Sub
You also need to make declarations required. There is a setting to do that. Or add it to existing modules by adding
Option Explicit
after Option Compare Database.
you can delete the DoEvents, since it does nothing.
m_strBergen is Form-wise variable (see i declare it in the form but outside any sub/function).
so as long as the form is open, m_strBergen can be used by all sub/func in same form.
Thanks Arnelgp. I always learn so much from you.
Every time you respond, I think to myself "What is Arnelgp going to pull out of his bag of tricks this time?"
Thank you for investing in my learning of Access VBA.