Using a combo box to determine which field to search.
in [Queries]
|
From: vander via AccessMonster.com on 7 Jun 2010 15:38 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
From: KARL DEWEY on 7 Jun 2010 17:38 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 > > . >
From: vander via AccessMonster.com on 7 Jun 2010 18:08 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
From: PieterLinden via AccessMonster.com on 7 Jun 2010 18:32 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
|
Pages: 1 Prev: in access, if then statement Next: Like Criteria Question using a control from a Form |