Using a combo box to determine which field to search.

[vander via AccessMonster.com]

What I want to do, hopefully, will be simple enough. I want to have a search
form that has two fields, one a combo box and the other an unbound field. I
want the combo box to list the different fields within a table that I want to
search. I want the unbound field to be where I enter the criteria for
searching the field that I selected in the combo box. In the past I usually
just created an unbound form and created unbound fields for each field and
then call the fields to the query. This has always worked well enough but
thought It would be easier using 2 fields as opposed to many. Any help would
be greatly appreciated.

 

[KARL DEWEY]

Try this --
Your table having Field1, Field2, Field3 and Field4.
Combo having two fields --
1 Field1
2 Field2
3 Field3
4 Field4

Search_Field: IIF([Forms]![YourForm][Cbo1] = 1, Field1,
IIF([Forms]![YourForm][Cbo1] = 2, Field2, IIF([Forms]![YourForm][Cbo1] = 3,
Field3, Field4)))

Criteria: [Forms]![YourForm][Text1]

 

[vander via AccessMonster.com]

Thanks Karl, I appreciate the help, but one question. How do I set up the
combo box so that it lists field names as opposed to records.

Try this --
Your table having Field1, Field2, Field3 and Field4.
Combo having two fields --
1 Field1
2 Field2
3 Field3
4 Field4

Search_Field: IIF([Forms]![YourForm][Cbo1] = 1, Field1,
IIF([Forms]![YourForm][Cbo1] = 2, Field2, IIF([Forms]![YourForm][Cbo1] = 3,
Field3, Field4)))

Criteria: [Forms]![YourForm][Text1]

What I want to do, hopefully, will be simple enough. I want to have a search
form that has two fields, one a combo box and the other an unbound field. I

[quoted text clipped - 5 lines]

thought It would be easier using 2 fields as opposed to many. Any help would
be greatly appreciated.

 

[PieterLinden via AccessMonster.com]

What I want to do, hopefully, will be simple enough. I want to have a search
form that has two fields, one a combo box and the other an unbound field. I
want the combo box to list the different fields within a table that I want to
search. I want the unbound field to be where I enter the criteria for
searching the field that I selected in the combo box. In the past I usually
just created an unbound form and created unbound fields for each field and
then call the fields to the query. This has always worked well enough but
thought It would be easier using 2 fields as opposed to many. Any help would
be greatly appreciated.

You would have to build the filter on the fly... If you open it as a query,
you'd have to use a temporary or stored query that whose SQL property you
could overwrite. The rest is pretty easy....

control source for first combobox:
SELECT Name FROM MSysObjects WHERE Type = 1 AND Name Not Like "MSys*";

control source for second combobox would be a bit more fun...

Private Sub Combo0_AfterUpdate()
PopulateSecondComboWithFieldNames Me.Combo0
End Sub


Private Sub PopulateSecondComboWithFieldNames(ByVal strTable As String)
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim lngIndex As Long

Set tdf = DBEngine(0)(0).TableDefs(strTable)
For Each fld In tdf.Fields
Me.Combo2.AddItem fld.name, Index:=lngIndex
lngIndex = lngIndex + 1
Next fld
End Sub

then you'd have to use these controls to build your SQL statement, and then
you'd probably need to assign the result of that to a holder query's SQL
property... eg

DBEngine(0)(0).QueryDefs("HolderQuery").SQL = <your function to build query>

 

[Marshall Barton]

What I want to do, hopefully, will be simple enough. I want to have a search
form that has two fields, one a combo box and the other an unbound field. I
want the combo box to list the different fields within a table that I want to
search. I want the unbound field to be where I enter the criteria for
searching the field that I selected in the combo box. In the past I usually
just created an unbound form and created unbound fields for each field and
then call the fields to the query. This has always worked well enough but
thought It would be easier using 2 fields as opposed to many.

I'm not sure I follow that, but if all you want is the names
of the fields in a table, then set the combo box's RowSource
to the table's name and set the RowSourceType to Field List.