Question ComboBox on Subform Problem (1 Viewer)

[Steve_T]

Hello,
I was wondering if someone could take a look at my problem as i as struggling to come up with a solution.

Is it possible to restricted the possible options available in a Combobox dependant on the value shown in a TextBox?
Eg:

A form called "frmmaster" has a Textbox called txt01, there is a subform on the form called "frmsub01" with a combo box called "cmb01" that has values taken from a table which are "1,2,3,4,5"

If the Textbox (txt01) had a value of "10" and i wanted people to only beable to select "1 or 2" from the Combobox (cmb01)on the Subform is this possible?
I understand this is simular to a cascading Comcobox but i have never used them on a subform before


Either way thankyou for taking the time to look at this problem

 

[JANR]

Sure what you are looking for is called cascading combobox, but the principal is the same. But I am not following the logic, you say that a value of "10" will yield 1 and 2, but what if i type 19 or 7 or anything else?

JR

 

[Steve_T]

Sure what you are looking for is called cascading combobox, but the principal is the same. But I am not following the logic, you say that a value of "10" will yield 1 and 2, but what if i type 19 or 7 or anything else?

JR

If anything else is shown in the Textbox then the other values in the Combobox situated on the Subform would be available to be used. Are you saying when you have the initail value of a Cascading combobox on a form and the final combobox on a subform then there is nothing different in designing it?
For example in the past when i have pulled values stored on a subform and wanted to show them on a associated form its not as simple as pulling a value from one textbox to another on the same form.

 

[JANR]

If you use a textboks as your entrypoint to filter your combobox on your subform dosen't matter (but I recomend that you use combobox on both)

A simpel approach would be in the After_Update event on txt01 you simply requery cmb01. But I don't see your data so cannot give an exact example. For this to work the must be some logic aka a recordsource behind both txt01 and cmb01 which you can filter on. Hope this makes sence.

JR

 

[Steve_T]

If you use a textboks as your entrypoint to filter your combobox on your subform dosen't matter (but I recomend that you use combobox on both)

A simpel approach would be in the After_Update event on txt01 you simply requery cmb01. But I don't see your data so cannot give an exact example. For this to work the must be some logic aka a recordsource behind both txt01 and cmb01 which you can filter on. Hope this makes sence.

JR

Thank you for taking the time to help, i think the best thing here for me is to try it and see where i get with it. I see you point about the recordsource, as it will be a common value to filter on.

 

[JANR]

I have been sitting here and banging my head thinking about your request and it downed on me. Since your data
collection dosen't look normalized, you could use the ValueList insted of "Table/Query", then it is just a little VBA
snippet and you are good to go.

First change your combobox cmb01 to valuelist and it's RowSource to NOTHING. Now in the After_Update event in txt01 put this:
Private Sub txt01_AfterUpdate()
If (Me.txt01 = 10) Then
Me.frmsub01.Form!cmb01.RowSource = "1;2"
Else
Me.frmsub01.Form!cmb01.RowSource = "3;4;5"
End If
Me.frmsub01.Form!cmb01.Requery
Me.frmsub01.Form!cmb01.SetFocus
Me.frmsub01.Form!cmb01.Dropdown
End Sub

The 2 last statements is there to force the user to enter something in cmb01, but since you have cmb01 on a SubForm you
have to set the TabOrder on your MainForm correctly. Put your SubForm immediatly after txt01.

I assume that your NAME-property of the subform is called frmsub01 or else you get an error. To check open the propertysheet
on your subformContainer under Other it should read: NAME -> frmsub01 if not change it.

Good Luck
JR

 

[Steve_T]

JR,
Thank you so much for your effort and time, i have gone with the first option of creating a table and setting incerting a value to allow the Combo boxes to filter by. Its seems the cleaner and simpler option.

 

[JANR]

Thank you so much for your effort and time, i have gone with the first option of creating a table and setting incerting a value to allow the Combo boxes to filter by. Its seems the cleaner and simpler option. Yesterday 07:57 PM

I agree, the promble with my methode is hardcoding of values, much harder to change and you can bet that somebody in the future will say "That looks good, but........."

JR