If...ElseIf....statements

I'm trying to restrict available data for fields based on previous fields.

I have 3 text boxes - Region, County, Branch - When the user picks North in the Region combo-box I want the County combo-box to only show the counties associated with the North Region. In turn I only want the Branches for the selected County to be available in the Branch combo-box.

Basically I'm looking for Region=Central; County=Orange & Seminole & Osceola; Branch=Headquarters & Oviedo & Kissimmee; etc.

I've been able to write the:

If Region = "Central" Then County = "Orange"
ElseIf Region = "Central-East" Then County = "Brevard"
End If

statements but now I need to take it to the next level.

Any assistance would be greatly appreciated.

re: If...ElseIf....statements

I have an alternative idea (it might sound complex, but really it doesn't seem that hard inm y mind. mind you I haven't tried this myself, but it might be worth a shot to try out):

Make the combo-boxes multi value combo's. As in for the Region combobox have 2 columns, one for a number and the other for the text that the user sees in the combo (in the properties for the combo set the width for the value column to 0 so the user doesnt see it the numbers).
e.g.
Reg | Txt
-----------------
1 | Central
2 | North
etc.
For the 'County' combo make it a 3 column combo, each row has its text, its own identifier value (like in the 'Region' combo), but also a column attributed to values in the 'Region' combo.
e.g.
Reg | Cty | Txt
-------------------------
1 | 1 | Orange
1 | 2 | Seminole
1 | 3 | Osceola
2 | 4 | <county for North>
etc.
Again do the same sort of thing for the final combo, except they refer to the individual values for the 'County' combo (the second column in my diagram).
e.g.
Cty | Br | Txt
--------------------------
1 | 1 | Headquarters
1 | 2 | Oviedo
1 | 3 | Kissimmee
2 | 4 | <branch for Seminole>
etc.

Then in the 'AfterUpdate' function for the 'Region' combo make it so only the rows with the value for that region in their 'Reg' column are shown, and then call the 'AfterUpdate' function for the 'County' combo at the end. Do the same sort of thing for the 'County' combo, but you won't need to call another 'AfterUpdate' function.

Hope that makes some kind of sense.

re: If...ElseIf....statements

If anyone wants to correct me on the above idea, add to it, or tell me that it won't work for whatever reason please do.

re: If...ElseIf....statements

The three lookup tables need to be designed as follows:

tblBranch {BranchID, BranchName, CountyID(FK)}
tblCounty {CountyID, CountyName, RegionID(FK)}
tblRegion {RegionID, RegionName}

Create a cboRegion (Region Combo Box) and make Row Source = SELECT RegionID, RegionName FROM tblRegion. Column Count = 2, Bound Column = 1 and Column Widths = 0 cm; 3 cm

Create a cboCounty (County Combo Box and leave the Row Source blank. Column Count = 2, Bound Column = 1 and Column Widths = 0 cm; 3 cm

Create a cboBranch (Branch Combo Box and leave the Row Source blank. Column Count = 2, Bound Column = 1 and Column Widths = 0 cm; 3 cm

Make sure that the Row Source Type of all the Combo boxes are "Table/Query"

Now in the After Update event of cboRegion put the following code:

And in the After Update event of cboCounty put the following code:


Hope this makes sense

re: If...ElseIf....statements

I think that's what I said, just in a more coded fashion. Yay!

re: If...ElseIf....statements

Wasn't disagreeing with you. Just trying to be helpful ...

re: If...ElseIf....statements

Never said you weren't disagreeing with me, in fact more the opposite. I was happy that you backed me up actually. and presented it in a different way.