On May 9, 9:48 am, Elainie <Elaine.Macint...@bsc.wales.nhs.ukwrote:
I would like to search a form with many fields on it, with out using
the search facility through access. Througth a drop down list if
possible.... How would I go about this? How could I also create this
from a top bar menu option too...?
Elaine
ARC's solution looks pretty complex: I think you can do what you want
by adding two .ComboBox es and a button to your form.
..cbxFields.RowSourceType = 'Field List'
..cbxFields.RowSource = 'myTable' (the table you want to search)
On open, this box will be populated with a list of the fields in the
myTable base table.
..cbxValues.RowSourceType = 'Table/Query'
..cbxValues.RowSource = "SELECT " & Me.cbxFields & " FROM myTable WHERE
" & Me.cbxFields & " IS NOT NULL GROUP BY " & Me.cbxFields
Add an event to cbxFields_After Update:
Private Sub cbxFields_AfterUpdate
Me.cbxValues.Requery
End Sub
So after you select which field to search, the second ComboBox is
populated with a list of all of the unique values in the field chosen
in cbxFields
As you type in the Value combo box, the selection will narrow. Add a
button to click when you have selected the value you want to search
for. In that button's _OnClick
Private Sub btnFilter_OnClick()
Me.Filter = Me.cbxFields & " = " & Me.cbxValues
Me.FilterOn = True
End Sub
This will filter the rows returned from the base table to those
matching the value in the filter setting. This is simplified somewhat
- you will have to add buttons to clear the filter and reset the
comboboxes, and you will also have to alter the filter syntax slightly
to accomodate text, numeric and date datatypes (damn it!). This should
be a start.
Ron, King of Chi