Cascading comboboxes does not allow selection of all values

[sebrahimi]

Hi guys,

I was successful creating two cascading combo boxes according to the instructions in another thread. Here is what I did:

1- I created 3 tables:
table1: farms:
field1: FarmID
field2: FarmName

table2: virtualDBs:
field1: VirtualDBID
field2: VirtualDBInstance

table3: F_VDB:
field1: FarmID
field2: VDBID

table3 is created to show the many-to-many relationship between tables 1 and 2.

2- I created two combo boxes in a form:

combo box1: cboFarm: Column Count = 2
Column Widths = 0;1
Bound Column = 1
Row Source = SELECT farms.FarmID, farms.FarmName FROM farms ORDER BY farms.[FarmName];

Private Sub cboFarm_AfterUpdate()
Dim strSQL As String
 
strSQL = "SELECT farms.FarmID, VirtualDBs.VirtualDBID, VirtualDBs.VirtualDBInstance FROM VirtualDBs INNER JOIN " & _
         "(farms INNER JOIN F_VDB ON farms.FarmID= F_VDB.FarmID) " & _
         "ON VirtualDBs.VirtualDBID = F_VDB.VDBID WHERE farms.FarmID= " & Me![cboFarm].Column(0)
 
Me![cboVDB].RowSource = strSQL
Me![cboVDB].SetFocus
Me![cboVDB].Dropdown
End Sub

combo box2: Column Count = 3
Column Widths = 0;0;1.5
Bound Column = 1
Row Source = None


cascading two combo boxes and filtreing the second combo box according to the first one works well but the problem is that I cannot select all the values in the second combo box. No matter what I click on, only the very first value gets selected. Any idea why?

Many thanks!

[jforbes]

I'm pretty sure you want to change the ComboBox2.BoundColumn=2. Right now it is being bound to column 1 which is farms.FarmID which at this point is the same value for all rows because of the WhereClause. So no matter what row you pick, the Value of ComboBox2 is the same as the Value for the first row, so Access displays the first row back to the user.

An alternate way to go about this is to change your select like this:

strSQL = "SELECT VirtualDBs.VirtualDBID, farms.FarmID, VirtualDBs.VirtualDBInstance FROM VirtualDBs INNER JOIN " & _

[sebrahimi]

Thank you jforbes, you are a real life saver!