Solved ListBox in Form tied to Control Source

[Weekleyba]

In the sample database attached, I create a project title and then select a tribe.
I want the listbox to populate the chairperson automatically when the tribe is selected in the NOFA_Create_SF.
I does this but, I also want to save the ChairpersonID to the NOFA_T so that when the chairperson becomes inactive, it won't disappear from the record.
For some reason, I'm stuck on this.
Can anyone lend a hand?
Thanks.

 

[bob fitz]

Perhaps this is what you want:

 

[Weekleyba]

Thanks Bob.
This gets me in the right direction, but how can I make it store multiple ChairPersonIDs, when multiple Tribes are chosen?

 

[bob fitz]

Thanks Bob.
This gets me in the right direction, but how can I make it store multiple ChairPersonIDs, when multiple Tribes are chosen?

Difficult to say without an understanding of what your db is for, its tables and their relationships.

My guess is that you need to add a field for the ChairPersonID data in the table called "NOFA_Tribe_JT".

 

[Weekleyba]

I still could use a hand with this one.
Attached is the full db which may aid in seeing what I'm trying to accomplish.

Upon opening the db, click "Summary All" then "Open" to see the form "NOFA_Create_F".
What I would like to do on this form is, as the Tribes get selected from combo in subform, I want the active chairperson(s) from each tribe, to be saved to the record (in this case, in table NOFA_T) and show up in either a list box or subform, so the user can see who the active chairperson is.
I have one of each in the form, but it's not working of course. That is, a list box "ListChairpersons" and a subform "NOFA_ChairPerson_SF"

Can anyone help me a little farther along?
Thanks.

 

[Weekleyba]

I believe I'm closer to what I want. See attached.
The problem now is when I create a new NOFA and select a tribe and chairperson, if I need to select another tribe and chairperson, the first chairperson disappears. Like below. (Before and after snips)
The data is actually still then when you check the table NOFA_Tribe_JT. But it doesn't show in the form...??

The row source for the Chairperson is a select query that has criteria for the TribeID of [Forms]![NOFA_Create_F]![NOFA_Create_SF]![cboTribe2], which may be causing the disappearing problem but, I don't know how to fix.

Any suggestions here?
Thanks.

 

[Weekleyba]

So here’s the solution I was able to research and come up with for:

Updating a Combo Box based on another Combo Box’s value in a Continuous Form

From some deep dive searching I found:

“A continuous form has only one set of controls. When the row source of a combo box is changed, it changes for ALL records. So when the row source is updated in the second record, it is updated in the first record too, which means that the value of the combo box may not be an item in the list any more. This makes the value become invisible (it's still there though).
The workaround is to place a text box on top of the combo box that displays the text corresponding to the value of the combo box. You may have to modify the record source of the form to add the relevant field.
This text box should cover the combo box except for the dropdown arrow, and its Back Style should be set to Transparent.”
- from HansV MVP 6 Jan 2011

Now to displaying a Combo Box’s text automatically in a Text Box, you need the following code in the combo box’s AfterUpdate event:

Private Sub cboSomething_AfterUpdate()
Me!txtSomething = Me!cboSomething.Column(1)
End Sub

Given everything else is in place, this will do.
I’m back on track with my small database!