By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

If...ElseIf....statements

P: 1
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.
Sep 1 '06 #1
Share this Question
Share on Google+
6 Replies


P: 8
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.
Sep 1 '06 #2

P: 8
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.
Sep 1 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboRegion_AfterUpdate()
  2.     Me.cboCounty.RowSource = "SELECT CountyID, CountyName FROM tblCounty WHERE RegionID=" & Me.cboRegion
  3. End Sub
  4.  
  5.  
And in the After Update event of cboCounty put the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboCounty_AfterUpdate()
  2.     Me.cboCounty.RowSource = "SELECT BranchD, BranchName FROM tblBranch WHERE CountyID=" & Me.cboCounty
  3. End Sub
  4.  
  5.  

Hope this makes sense

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.
Sep 1 '06 #4

P: 8
I think that's what I said, just in a more coded fashion. Yay!
Sep 3 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Wasn't disagreeing with you. Just trying to be helpful ...



I think that's what I said, just in a more coded fashion. Yay!
Sep 3 '06 #6

P: 8
Wasn't disagreeing with you. Just trying to be helpful ...
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.
Sep 4 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.