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:
-
Private Sub cboRegion_AfterUpdate()
-
Me.cboCounty.RowSource = "SELECT CountyID, CountyName FROM tblCounty WHERE RegionID=" & Me.cboRegion
-
End Sub
-
-
And in the After Update event of cboCounty put the following code:
-
Private Sub cboCounty_AfterUpdate()
-
Me.cboCounty.RowSource = "SELECT BranchD, BranchName FROM tblBranch WHERE CountyID=" & Me.cboCounty
-
End Sub
-
-
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.