Solved Is it possible to change a combobox's choices (1 Viewer)

[CuriousGeo]

depending on a text box? I have a form with a text box containing body sites which is imported from another application. For certain specific body sites I would like the combobox to have different selections available than for "all others". Is this possible? If so, can it be done on a continuous form? If not, I'm ok with using a single record form.
Here is an example:

 

[theDBguy]

Hi. Yes, that's possible. Try searching for "Cascading Comboboxes." That approach should be similar to what you want to do.

 

[Isaac]

Look into the AddItem method of the combobox. If you use this, make sure the combobox's RowSourceType property is set to Value List.
You would write some very simple code to test the value of the Textbox: Depending on what it is, clear the combobox and add the necessary items. For me I usually think of cascading comboboxes in terms of query-sourced comboboxes, whose Where clauses are being dynamically adjusted based on previous comboboxes' selections....But in your case you have a small amount of explicit/known values, might as well just add them. All else being equal. IMHO.

 

[CuriousGeo]

Thank you for the responses. I understand cascading comboboxes, this is similar, except I want the combobox to change according to what is in a TEXT BOX, not a different combobox.
I would need to use a Like statement, since each text box contains slightly different variations of body sites:

For example, the text box might read "Left lobe of thyroid" on one record, but say "Thyroid gland" in another record. I want all things that have thyroid in them to have comboboxes with thyroid diagnosis.

Another example would be one record is called "cervical" and another record says "vaginal". On those types (Like "Cerv*" Or Like "Vag*" I want the combobox to have "Pap" diagnosis types. (I have them listed in my image on the original post)
Do I set up the combobox to read the text box and change accordingly? I kind of understand how it's similar to cascading comboboxes, but cannot grasp exactly what I need to do.

 

[Isaac]

Do I set up the combobox to read the text box and change accordingly?

Not sure if this was directed at myself or dbGuy, but:

You would add code to, perhaps, the Textbox's AfterUpdate event.

Something like:

Private Sub Text2_AfterUpdate()
Me.Combo0.RowSource = ""
If InStr(1, Me.Text2.Value, "sometext") > 0 Then
    Me.Combo0.AddItem "value1"
    Me.Combo0.AddItem "value2"
ElseIf InStr(1, Me.Text2.Value, "someothertext") > 0 Then
    Me.Combo0.AddItem "value3"
    Me.Combo0.AddItem "value4"
End If
End Sub

You might have to loop through the combobox and explicitly RemoveItem, per row, rather than setting RowSource to "". I can't remember at the moment.

 

[The_Doc_Man]

When you are talking about adjusting a combo box in the manner of cascading combo boxes, you need to use a similar principle. You have a control that is independent (e.g. your text box or the first combo box in a cascade sequence). When you put something in that control, you have to then do something that will let Access know you want to affect the dependent (or next dependent, if more than two) combo box. This can be the .LostFocus event from the independent control, though other events also work. But you would also need code in the form's Current Event to "reset" the chain of dependent controls when you navigate to a different record or start to enter a new record.

Isaac suggests the .AfterUpdate event for the control. That would also work, but be careful to not confuse a control's event from a form event of the same name. That might get ... awkward.

 

[jdraw]

Are there more sites and more related choices OR is the posted set the entire issue?

 

[arnelgp]

you can put it in a Table or if the same table exists, use it.
see this demo.

 

[CuriousGeo]

you can put it in a Table or if the same table exists, use it.
see this demo.

Thank you arnelgp! I couldn't really grasp how to get the combo associated with the textbox. I downloaded your sample. It is exactly what I was trying to do. I was thinking of making a separate table which held all the combo values too. SOLVED!

 

[arnelgp]

And you can expand the "partial" table to hold other partial keyword but has same combo content.