Combo box based on another combo box

[Darhl Thomason]

I have a combo box that is dependant on another combo box and it mostly
works properly. I'm trying to clean up the filter boxes on my form so
instead of having multiple filter boxes, I do it with two boxes. The first
box is where you select the field you want to filter on (cboFilterCriteria)
and the second box is what selects the value to filter on (cboFilterItem).
There are seven values to choose from in the first combo box, and all but 1
work properly. the one that doesn't work properly is the "Number" criteria.
In all other cases, the data is coming from a table with two fields,
"table"ID and "table"Name (i.e. tblFC = FCID, FCName or tblOwners = OwnerID,
OwnerName etc.). The table that has the Number field is the primary key for
my main data table (tblStoreData) and has multiple fields. Number
corresponds to the unique store number.

My code is:
Private Sub cboFilterItem_GotFocus()
If cboFilterCriteria = "Owner" Then
strSQL2 = "SELECT OwnerID, OwnerName FROM tblOwners ORDER BY OwnerName"
Else
If cboFilterCriteria = "Store" Then
strSQL2 = "SELECT Number FROM tblStoreData ORDER BY Number"
Else
If cboFilterCriteria = "FC" Then
strSQL2 = "SELECT FCID, FCName from tblFC ORDER BY FCName"
Else
If cboFilterCriteria = "Region" Then
strSQL2 = "SELECT RegionID, RegionName from tblRegion ORDER BY
RegionName"
Else
If cboFilterCriteria = "District" Then
strSQL2 = "SELECT DistrictID, DistrictName from tblDistrict
ORDER BY DistrictName"
Else
If cboFilterCriteria = "DMA" Then
strSQL2 = "SELECT DMAID, DMAName from tblDMA ORDER BY DMAName"
Else
If cboFilterCriteria = "Checklist Week" Then
strSQL2 = "SELECT WeekID, WeekName from tblCheckListStatus
ORDER BY WeekID"
Else
GoTo NotSetYet:
End If
End If
End If
End If
End If
End If
End If
Me.cboFilterItem.RowSource = strSQL2
Exit Sub
NotSetYet:
MsgBox "That criteria is not set up yet", vbOKOnly
Me.cboFilterCriteria.SetFocus
End Sub

Thanks!

Darhl

 

[Darhl Thomason]

OK, I got it! The problem was with the cboFilterItem, it was set for 2
columns with the first column width set at 0". I changed it around and
viola! It works like a champ.

Now to code it into my filter routine...

Darhl