I'd suggest two combo boxes, one to select the field and then one to select
from all the available values from that field. It could be done with a
single combo box, but it would be trickier as you'd have to determine from
the last control with focus what the field name is and what data type it is
so that you could change the second combo box's list and determine the
delimiters required for the value in the string expression for the Filter
property. Having a 'fields' combo box makes this simple as you just store
the data type in a second hidden column of the combo box.
To simplify matters I'll illustrate this with just three fields, LastName,
Amount and TimeStamp, one of text data type, one of number data type and one
of date/time data type, and that the form is bound to a table named MyTable.
Add an unbound combo box, cboFields, to the form. Set its RowSourceType
property to Value List, and set its RowSource property to:
LastName;Text;Amount;Number;TimeStamp;DateTime
i.e. a series of pairs of values made up of the field name followed by the
data type in each case.
Set its ColumnCount property to 2.
Add an combo box, cboValue, to the form. Set its RowSourceType property to
Table/Query, but leave its RowSource property blank.
In the AfterUpdate event procedure of cboFields put:
Dim ctrl As Control
Dim strSQL As String
Set ctrl = Me.ActiveControl
Select Case ctrl
Case "LastName"
strSQL = "SELECT DISTINCT LastName " & _
"FROM MyTable " & _
"WHERE LastName IS NOT NULL " & _
"ORDER BY LastName"
Case "Amount"
strSQL = "SELECT DISTINCT Amount " & _
"FROM MyTable " & _
"WHERE Amount IS NOT NULL " & _
"ORDER BY Amount"
Case "TimeStamp"
strSQL = "SELECT DISTINCT TimeStamp " & _
"FROM MyTable " & _
"WHERE TimeStamp IS NOT NULL " & _
"ORDER BY TimeStamp"
Case Else
' this will return no rows, so the combo box's
' list will be empty if no field is selected
strSQL = "SELECT * FROM MyTable WHERE FALSE"
End Select
Me.cboValue.RowSource = strSQL
Me.cboValue.Requery
Me.cboValue = Null
The field names in the cboFields combo box don't have to be the actual field
names, so you could use Last Name instead of LastName, along with CASE
"LastName", but the real fieldname LastName must still be used in the SQL
statement.
In the AfterUpdate event procedure of cboValue put:
Dim strDelimiter As String
If Not IsNull(Me.cboFields) Then
If Not IsNull(Me.cboValue) Then
Select Case Me.cboFields.Column(1)
Case "Text"
strDelimiter = """"
Case "Number"
strDelimiter = ""
Case "DateTime"
strDelimiter = "#"
End Select
Me.Filter = Me.cboFields & _
" = " & strDelimiter & _
Me.cboValue & strDelimiter
Me.FilterOn = True
End If
End If
To open the form without any record's showing put the following in the
form's Open event procedure:
Me.Filter = False
Me.FilterOn = True
However, using the built in Filter by Form facility in the way I described
in my first post would give you a lot more flexibility, enabling you to
filter on multiple fields, e.g. Lastname = "Smith" And City = "London", or to
use a Boolean OR operation, e.g. LastName = "Smith" OR LastName = "Jones".
The Filter by Form mechanism automatically gives you combo boxes to select
from for each field.
Ken Sheridan
Stafford, England
Dj said:
I stumbled thought and got your method to work. While I appreciate every's
suggestions, I'm leaning towards the combo box becuase filter works better
than search. When seaching for a last name, if there are 10 Smiths, it would
be better if all 10 records open up in a filter that you can scroll through
rather than the Search method of having to keep going find next, etc.
My last question is, if I wanted the option of picking 1 of 50 different
fields to search on (i.e. lastname or firstname or hiredate, etc) can I do
that with just the one combo box or do I have to have a different combo box
for the various fields?
If I can do it with one combo box, what should the afterupdate code look
like. Using the example you gave me, I can only filter off of one field (the
one I plug into [your field name].
I hope I explained my senario clearly. Thanks for all your help.
Golfinray said:
Use a form. Put a combo box on the form. Allow the wizard to install the
combo box using whatever you want to search on a the field (like an id
number.) Then right click on the combo, go to events, start the code on the
afterupdate event (the little on the right) and type:
Me.filter = "[your field name] = """ & Me.combo# & """"
Me.filteron = true
The combo# will be listed, like combo12 or combo6
Dj said:
I feel like I should know this, but I can't figure it out.
I have one table with 1000 records, 50 fields per record. I want my user to
be able to search by any one of the 50 fields they choose. I started out
with a query that opens all records. If I put a search button in my form,
the form opens with all the records and I'd rather not do it that way. I'd
rather it would open blank and then the user can still tab their cursor to
the field they want to search.
Since the search will be for read only, I can use a form or report. Either
way, how do I set it up so that the user decides which field they want to
search by? Thx, Dj
