If...Then...Else statement

[Lebbsy]

I have two combobox fields with the second combobox (Department) dependent on the first combobox (Ministry). If the value of the first combobox changes the options in the second combobox should change as well. I have used an

If combobox1.value change Then combobox2.Additem "values"

. What this does is, when I select value for first field then second combobox updates well, but when I try to change value for first combobox without closing the form, combobox2 shows options of chosen combobox1 value and those of the option that I had chosen before. The values I cached. So am wondering if I am using the correct statement or there is a way to modify my code.

Example code:

If Not IsNull(Me.Ministry) And Ministry.Value = "PAT" Then
Department.AddItem "xyzs"
Department.AddItem "sxcty"
End If
If Not IsNull(Me.Ministry) And Ministry.Value = "MSP" Then
Department.AddItem "bps"
Department.AddItem "dce"
Department.AddItem "dps"
Department.AddItem "gic"
Department.AddItem "nac"
Department.AddItem "ofp"
Department.AddItem "oop"
Department.AddItem "ste"
End If

[NeoPa]

I'm not sure if you're even on the right lines here Lebbsy.

Check out Example Filtering on a Form. You should find all you need to know in there.

Come back if you have any specific questions related to this.

PS. It could be as simple as calling the Me.Requery, but it will help you to understand it better.

[missinglinq]

I have two combobox fields with the second combobox (Department) dependent on the first combobox (Ministry). If the value of the first combobox changes the options in the second combobox should change as well.

The concept you're describing is called Cascading Comboboxes. You shouldn't be trying to use AddItem to fill the second combobox, but rather basing the second combobox's Rowsource on the value of the first one.

Here's a tutorial on the process by Rabbit, one of our experts:

http://bytes.com/topic/access/insights/605958-cascading-combo-list-boxes

Linq ;0)>

[Lebbsy]

Thanks for the response but that will not do. I have 23 choices for combobox1 and each choice can have between 1 and 15 choices. That is, assume I have the following:

• Combobox1 value = RSDT Then Combobox2 values are FGHT, GHTYU, SDTU, PDSR
• Combobox1 value = MUNSE Then Combobox2 values are SPEDT, KGUTY, GSDE
• Combobox1 value = DESC Then Combobox2 values are FRGTY, SDERT, SDERT

RSDT has 4 choices, MUNSE and DESC have 3 each. On choosing RSDT only the 4 values under it should be shown. If I change to MUNSE only the 3 values under it should be shown, etc. Now the situation I have is: If I open a form and choose DESC, the three values will show ok for the first time. Without closing the form or saving my data, when I select MUNSE, both values for DESC and MUNSE will show whereas I want values for MUNSE only. How can I stop this caching?

Regards,

[Lebbsy]

So missingling how do you suggest I handle values for combobox2?

[NeoPa]

I would suggest that probably it IS the way to go. However, if you want to continue to use the .AddItem route (RowSourceType= "Value List") then there is also a .RemoveItem method associated with a ComboBox control.

As this would need to be called repetitively though (to clear it down completely), it may be easier to say :

Me.YourCombo.RowSource = ""

[missinglinq]

How many options is not really the point at all! Using cascading comboboxes is the only rational way to do this. All you need is a table with the two fields, Ministry and Department, then base your comboboxes on the table. Reconstructing the second combobox every time you access it simply makes no sense.

Also, what happens when a new item is added, or someone decides the abbreviations you're now using need to be changed? Your way, you've got to go back into the code, because you've hard wired the items. Using cascading a table and comboboxes you simply add/edit the items in the table. No code changes need to be made.

Linq ;0)>

[Lebbsy]

Thank you guys.. I cascaded the combobox and it worked fine. Just did not want to create a new table but ended up doing it......

[missinglinq]

It's really the only way to handle this! Storing items, options, etc. in tables is always preferable to hardwiring them. Makes it easy for you or your end users to modify/add to them without going back into the code.

Glad you got it working!

Linq ;0)>