Solved Populate Textbox Based On Other Controls (1 Viewer)

Weekleyba

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

Code:
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

Lifelong Learner
Local time
Today, 14:32
Joined
Mar 14, 2017
Messages
8,738
What's the row source of your combo box?
 

Weekleyba

Registered User.
Local time
Today, 16:32
Joined
Oct 10, 2013
Messages
586
SELECT T_FY.FYID, T_FY.FY FROM T_FY ORDER BY T_FY.FY;
 

June7

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

Registered User.
Local time
Today, 16:32
Joined
Oct 10, 2013
Messages
586
Column count 2; Column widths 0";1"
Bound to column 1
 

June7

AWF VIP
Local time
Today, 13:32
Joined
Mar 9, 2014
Messages
5,423
Why are you using Project_Name AfterUpdate instead of FY combobox AfterUpdate?
 

Weekleyba

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

AWF VIP
Local time
Today, 13:32
Joined
Mar 9, 2014
Messages
5,423
Has a value already been selected in FY combobox when ProjectName is selected?
 

Weekleyba

Registered User.
Local time
Today, 16:32
Joined
Oct 10, 2013
Messages
586
No but I don't let the form save until FY combobox is populated.
 

Weekleyba

Registered User.
Local time
Today, 16:32
Joined
Oct 10, 2013
Messages
586
Here's my table for FY.
It doesn't seem to matter if I select 'Number' or 'Short Text' for the FY field.

1603595961466.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:32
Joined
May 7, 2009
Messages
19,169
you may also try this:
Code:
Private Sub PROJECT_NAME_AfterUpdate()
    Dim Prefix1 As String
    Prefix1 = "GP-"
    Me![BergenNumber] = Prefix1 & Format("0" & [cboProjectFY].Column(1), "00")
End Sub
 

June7

AWF VIP
Local time
Today, 13:32
Joined
Mar 9, 2014
Messages
5,423
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
You need validation code in the Form's BeforeUpdate event to ensure that both fields are filled correctly but there is no reason to actually store the value. Just concatenate the two parts if that's how you want to display them.
 

Weekleyba

Registered User.
Local time
Today, 16:32
Joined
Oct 10, 2013
Messages
586
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
1603673630904.png
 

Attachments

  • Auto_Project_Number - Copy.zip
    75.7 KB · Views: 167

June7

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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:32
Joined
May 7, 2009
Messages
19,169
see the code on your forms.
 

Attachments

  • Auto_Project_Number - Copy.zip
    52.2 KB · Views: 154

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
Here's a sample with two uses for a "sequence number"
 

Attachments

  • CustomSequenceNumber20201020c.zip
    85.6 KB · Views: 163

Weekleyba

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

Attachments

  • Auto_Project_Number - Copy1 EDITED1.zip
    48.9 KB · Views: 154

arnelgp

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

Attachments

  • Auto_Project_Number - Copy1 EDITED1.zip
    73.9 KB · Views: 156

Users who are viewing this thread

Top Bottom