Solved Populate Textbox Based On Other Controls

[Weekleyba]

I'm trying to populate a textbox based on other controls on the same form using the AfterUdate event.
How do I populate from a combobox?
How do I populate from another textbox and string them together?

Private Sub PROJECT_NAME_AfterUpdate()
    Dim Prefix1 As String
    Prefix1 = "GP-"
    Me![BergenNumber] = Prefix1 & Format([cboProjectFY].[Column](1), "00")
End Sub

I tried the above but not working. Only gives me the "GP-".
The cboProjectFY is a combobox with the ID and the two digit year.

Need a hand.
Thanks

 

[Isaac]

What's the row source of your combo box?

 

[Weekleyba]

SELECT T_FY.FYID, T_FY.FY FROM T_FY ORDER BY T_FY.FY;

 

[theDBguy]

SELECT T_FY.FYID, T_FY.FY FROM T_FY ORDER BY T_FY.FY;

Hi. What are your Combo's Column Count and Widths?

 

[June7]

If you can see the FY value in combobox list, your code should work. But why save a static prefix? Can format the FY to display with prefix with calculation when needed.

Concatenate textboxes, comboboxes, listboxes values basically same way you are concatenating literal text to combobox.

 

[Weekleyba]

Column count 2; Column widths 0";1"
Bound to column 1

 

[June7]

Why are you using Project_Name AfterUpdate instead of FY combobox AfterUpdate?

 

[Weekleyba]

I guess I could, but I don't think that would matter...would it?

The first column in a combobox is 0, right? It's 1.

 

[June7]

Has a value already been selected in FY combobox when ProjectName is selected?

 

[Weekleyba]

No but I don't let the form save until FY combobox is populated.

 

[Weekleyba]

Here's my table for FY.
It doesn't seem to matter if I select 'Number' or 'Short Text' for the FY field.

 

[arnelgp]

you may also try this:

Private Sub PROJECT_NAME_AfterUpdate()
    Dim Prefix1 As String
    Prefix1 = "GP-"
    Me![BergenNumber] = Prefix1 & Format("0" & [cboProjectFY].Column(1), "00")
End Sub

 

[June7]

No but I don't let the form save until FY combobox is populated.

If FY is not selected then how could you expect Project_Name event to have a FY value to use? Use FY combobox AfterUpdate event or the form BeforeUpdate event. Or don't save this composite value, format FY when needed for reporting.

 

[Weekleyba]

Thanks guys.
I figured out that small part but, allow me share what I'm trying to accomplish, because I most definitely need some additional assistance.

The bigger picture is, I'm trying to automate the creation of the Bergen Number.
The Bergen Number has six parts as described below.
I've managed to do the easy part. That is, "GP-20-BSU- -H-6."
The part I need help with is the "300", which I'll call the Project Number. (See the "300" below)
The Project Number is generated by the Project Type selection and would be sequential starting at the beginning at the start of the fascial year, 1 Oct.
Can anyone help me with code to create the Project Number?
Attached is what I have for a test database.
I don't yet have the validation code in the Before Update event but, I think I have a good idea of what to do there and I'll enter that in at the end.
Thanks for any and all help.


BERGEN NUMBER

 

[June7]

Generating custom unique identifier is a common topic. Will involve finding the most recent issued value for a category, parsing the string to get the sequential value and incrementing it then putting the parts back together. It would probably be easier to save the parts into separate fields then concatenate for display. Review https://www.accessforums.net/showthread.php?t=23329

 

[arnelgp]

see the code on your forms.

 

[Weekleyba]

Thanks Arnelgp and Pat!
Arnelgp, I like the Public Function that is executed on the After Update of each control. Nice!
Pat, I took what you sent and tried to implement it in my database.

I'm much further along but I'm stuck once again.

The Bergen Number is made up of six parts to give the full number: GP-20-BSU-300 -H-6.
1. GP - This is just text.
2. 20 - This will come from the selected FY.
3. BSU - This is the LocationAbbrevation
4. 300 - This is the tough one. It is sequential by FY and Project Type. (note: from table above, 300 is the first number in Project Type, Major M&I)
So the first Major M&I project for BSU in FY 20, is GP-20-BSU-300-H-6
The second Major M&I project for BSU in FY 20 is GP-20-BSU-301-H-6 and so on.
If a different Project Type is selected, then the sequenced number starts from a different starting point, as the table above shows.
Example: a DES project for BSU in FY 20 is GP-20-BSU-001-H-6, a second DES would be GP-20-BSU-002-H-6, and so on.
5. H - Facility Type
6. 3 - Funding Type

So, I've worked on number 4 today and I'm still not getting it to work.
I thought it would be smart to take it one step at a time, and get the sequence numbering to work with the FY starting at 001, first.
Attached is what I have but it has been giving me errors.

Can you please take another look and give me hand?
Thanks!

 

[arnelgp]

here is your Bergen Number.
for demo enter:

Project FY:11
Project Type: DES
Funding Type: Quarters
Facility Type: H

Location subform
Location: Belcourt Service

save the record.
the Bergen Number is GP-11-BSU-001-H-1

Now, add new record with Same Info.
the Bergen Number is GP-11-BSU-002-H-1

 

[June7]

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]

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.