Solved Populate Textbox Based On Other Controls (1 Viewer)

June7

AWF VIP
Local time
Today, 08:15
Joined
Mar 9, 2014
Messages
5,423
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?
 

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
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!

Thanks again and stay safe.
 

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:15
Joined
May 7, 2009
Messages
19,169
happy coding:)
 

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
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.
 

Attachments

  • Bergen Number Generator - Ver 3 - revising.zip
    58 KB · Views: 88

June7

AWF VIP
Local time
Today, 08:15
Joined
Mar 9, 2014
Messages
5,423
Build a procedure that does the check and have it called by all 5 combobox AfterUpdate events.

"Not working" means what - error message, wrong result, nothing happens?
 

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
Ok here's more detail on the problem.
I have form below call F_Project.
2020-12-14 155756.jpg

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.
2020-12-14 161022.jpg


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.
 

Attachments

  • Bergen Number Generator - Ver 4.zip
    51.8 KB · Views: 80

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
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.
2020-12-15 224736.jpg
 

Attachments

  • Bergen Number Generator - Ver 5.zip
    83.9 KB · Views: 89

June7

AWF VIP
Local time
Today, 08:15
Joined
Mar 9, 2014
Messages
5,423
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?
 

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:15
Joined
May 7, 2009
Messages
19,169
see if this resolved your issue.
 

Attachments

  • Bergen Number Generator - Ver 5.zip
    166.5 KB · Views: 92

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
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

Thanks again for the assist!!!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:15
Joined
Feb 19, 2002
Messages
42,970
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:15
Joined
May 7, 2009
Messages
19,169
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.
 

Weekleyba

Registered User.
Local time
Today, 11:15
Joined
Oct 10, 2013
Messages
586
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.
 

Users who are viewing this thread

Top Bottom