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.

-- 
Message posted via http://www.accessmonster.com

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] 


-- 
Build a little, test a little.


"vander via AccessMonster.com" wrote:

> 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.
> 
> -- 
> Message posted via http://www.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.

KARL DEWEY wrote:
>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.

-- 
Message posted via http://www.accessmonster.com

vander wrote:
>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>

-- 
Message posted via http://www.accessmonster.com