- Joined
- Jun 30, 2009
- Messages
- 1
- Reaction score
- 0
Hi All,
I'm trying to make a form that users can dynamically filter the results on. I have two combo boxes. The first one shows all the possible fields names in the table of concern. The second combo box should show the records for the field the user selected in combo 1. This allows the user to have one filter field but also have the flexibility to choose which field.
Combo cboSearchField - I use a value list and create the value list when I open the form using the following code. This works fine.
Private Sub Form_Open(Cancel As Integer)
Dim rs As Recordset
Dim f As Field
Set rs = CurrentDb.OpenRecordset("Ali")
For Each f In rs.Fields
Me.cboSearchField.AddItem (f.Name)
Next
rs.Close
End Sub
The second Combo box (cboSearch) gets the rowsource created dynamically depending on the selection in cboSerachField. The onClick event is as follows.
Private Sub cboSearchField_Click()
Me.cboSearch.RowSource = "SELECT Ali.[File No] FROM Ali ORDER BY [File No]; "
Me.Search_Label.Caption = Me.cboSearchField
Me.cboSearch.RowSource = "SELECT Ali.[File No], [" & Me.cboSearchField & "] FROM Ali ORDER BY [" & Me.cboSearchField & "]; "
Me.cboSearch.Requery
End Sub
The problem I'm encountering is the second combo box does NOT refresh. I tried the repaint and it just doesn't display. The code in the Row Source shows the right sql statement and the sql checks out
I'm trying to make a form that users can dynamically filter the results on. I have two combo boxes. The first one shows all the possible fields names in the table of concern. The second combo box should show the records for the field the user selected in combo 1. This allows the user to have one filter field but also have the flexibility to choose which field.
Combo cboSearchField - I use a value list and create the value list when I open the form using the following code. This works fine.
Private Sub Form_Open(Cancel As Integer)
Dim rs As Recordset
Dim f As Field
Set rs = CurrentDb.OpenRecordset("Ali")
For Each f In rs.Fields
Me.cboSearchField.AddItem (f.Name)
Next
rs.Close
End Sub
The second Combo box (cboSearch) gets the rowsource created dynamically depending on the selection in cboSerachField. The onClick event is as follows.
Private Sub cboSearchField_Click()
Me.cboSearch.RowSource = "SELECT Ali.[File No] FROM Ali ORDER BY [File No]; "
Me.Search_Label.Caption = Me.cboSearchField
Me.cboSearch.RowSource = "SELECT Ali.[File No], [" & Me.cboSearchField & "] FROM Ali ORDER BY [" & Me.cboSearchField & "]; "
Me.cboSearch.Requery
End Sub
The problem I'm encountering is the second combo box does NOT refresh. I tried the repaint and it just doesn't display. The code in the Row Source shows the right sql statement and the sql checks out