Combox Requery after Update of Another Combobox

Question

I have 2 combo boxes one for Region and One for District (cboRR and cboDD). I am trying to get the cboDD combo box to show only the districts that are in the selected Region. What I have tried so far is adding in an AfterUpdate to the cboRR with a long IF statement with all the RRDD combinations and for the cboDD to have an OnClick Requery. I have tried various combinations of those and nothing so far is working. Are there any thoughts as to why I cannot get this to work?

The if statement for the AfterUpdate is:

Public Sub AfterUpdate()
If cboRR = "03" Then
cboDD = "03" & ";" & "12" & ";" & "13" & ";" & "30" & ";" & "46" & ";" & "55" & ";" & "56" & ";" & "76" & ";" & "86" & ";" & "92" & ";" & "95"
Else
cboDD = " "
End If
If cboRR = "07" Then
cboDD = "07" & ";" & "17" & ";" & "20" & ";" & "27" & ";" & "32" & ";" & "33" & ";" & "36" & ";" & "40" & ";" & "44" & ";" & "45" & ";" & "49" & ";" & "64"
Else
cboDD = " "
End If
If cboRR = "10" Then
cboDD = "17"
Else
cboDD = " "
End If
If cboRR = "12" Then
cboDD = "12" & ";" & "97"
Else
cboDD = " "
End If
If cboRR = "13" Then
cboDD = "02" & ";" & "04" & ";" & "21" & ";" & "41" & ";" & "45" & ";" & "46"
End Sub

What is not working? Is your AfterUpdate function not working? Are you firing it correctly on the change event? — attila, Apr 04 '14 at 20:47
Well the change is not working, I am not getting any error messages. I change the Region and I still have all the possible districts listed instead of just that regions districts. — user2119980, Apr 04 '14 at 20:48
Can you show how you are tying the Change event to the AfterUpdate function? — attila, Apr 04 '14 at 20:50
The code above is what I have set for the AfterUpdate in the cboRR combo box. — user2119980, Apr 04 '14 at 20:51
Are you sure AfterUpdate is getting called? If not, show how you are trying to call it. — attila, Apr 04 '14 at 20:58
If you have a junction table that shows which regions are associated with which districts, it all gets much easier. — Fionnuala, Apr 04 '14 at 21:03
More or less, you need RegionId and DistrictId, values might be 07,17; 07, 20; 13; 02 and so on. — Fionnuala, Apr 04 '14 at 21:16
You might get some idea here: http://stackoverflow.com/questions/12131211/create-form-to-add-records-in-multiple-tables/12132196#12132196 — Fionnuala, Apr 04 '14 at 21:17

score -1 · Answer 1 · answered Apr 04 '14 at 21:53

-1

Create a table that list all possible choices for your first combo box the regions. Then create a second table for your districts with a column that will have all applicable regions separated by the pipe symbol like so |01|02|. Then create a query that looks at the value in your first combo with criteria set to:

Like "*|" & [Forms]![YourForm]![MyFirstComboBox] & "|*"

answered Apr 04 '14 at 21:53

Mike

144
4

This is not a standard solution. – Fionnuala Apr 04 '14 at 22:12
I am trying to make this as simple as possible, the less activity going on in the background the better. This is going to be used by people other than me. So the less there is to go wrong the easier their lives will be. I do appreciate the input and will look into this method. – user2119980 Apr 07 '14 at 13:07

Combox Requery after Update of Another Combobox

1 Answers1