RE: Filter by Form using combo box

[angiew]

I have an &quot;interactive&quot;database set up to allow managers to filter an underlying query by using combo boxes on a form.&nbsp;&nbsp;Right now, it is set up for them to open the form, click the filter by form button, choose their filter options, click the filter button, and then click a button on the form to preview a report showing the filtered information.&nbsp;&nbsp;My boss would like me to automate this process so that they can choose their information and not have to go through the process of clicking on the filter by form and filter buttons on the toolbar.&nbsp;&nbsp;Is there any way for them to open the form, make the filter choices, and then click one button that will filter the information and preview a report?&nbsp;&nbsp;They need to be able to make multiple choices as is possible with the filter by form option.&nbsp;&nbsp;This form will not be used to add or change any information, only make choices for filtering.<br><br>Thanks!<br><br>Angie Wenzel<br>

 

[samara]

Man,,,<br>what about making a form that has many list boxes (a list box for each field you wanna Filter), each list will display the whole values in each field... so your user can make choice from each list (you can make this list boxes support multi-selection as you want).<br>then you have a button in that form, if the user clicks it it'll generate an SQL statement that will open another form and filter it.<br><br>i didn't try it before, but i wish this would help you.. if there's any errors pls inform me <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[DougP]

First of all its best to narrow down a filters selction.<br>Most time's you will find that the end user does not need to now every concievable way to filter the data.<br>They want to do basically the same thing over and over.<br><br>Make a list of which fields, and what they may want from those fields.<br>Like a date field for example:<br>Say they want to know which data that falls between Last Monday and last Friday.<br><br>You would then make two functions:<br><br>Function LastMonday(TodaysDate)<br>' figure out info<br>End Function<br><br>Function LastFriday (LastMonday)<br>'&nbsp;&nbsp;last friday is actually Lastmonday plus 4 days<br>End Function<br><br>You could put those two functions in a query that when run would always pull up data from &quot;Last Week&quot;<br><br>Between LastMonday(now) and LastFriday(LastMonday)<br><br>Note these are vague examples.<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[angiew]

Thanks for the suggestions, but I am afraid that neither of them will meet my needs.&nbsp;&nbsp;I need to use combo boxes, not list boxes, and the users will need to know all of the possibilties for filtering.&nbsp;&nbsp;Any other ideas?<br><br>Angie Wenzel<br>

 

[samara]

Ok,<br>You can make it with combo boxes, but combo boxes can't provide you with multiple choices for a given one field... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[angiew]

When you use the Filter by Form option, they can.

 

[Lightning]

Angie<br><br>Mark Dunning answered a similar question in the January 1996 edition of &quot;Access-VB Advisor&quot;.&nbsp;&nbsp;I've used his technique successfully with up to 10 combo boxes to select the criteria.<br><br>Make a select query for each field that you want to use as criteria.&nbsp;&nbsp;Set their property to Group By and sort ascending.<br><br>Make another query and add all your criteria fields to it.&nbsp;&nbsp;In each field criteria, enter<br><br>Forms![YourFormName]![ComboBoxName] or Forms![YourFormName]![ComboBoxName] Is Null<br><br>Base a list box on the query and select from the combo boxes.&nbsp;&nbsp;This will filter the records as each criteria is added.<br><br>I have only ever used this as a lookup form, but it should be workable for reports as well.<br><br>Let me know how you go.

 

[Quehay]

There is an example given by Elizabeth in the FAQ's that<br>addresses filtering by combo and adding a command button<br>that uses DoCmd to open the query after combo selection on a form.&nbsp;&nbsp;This is a bit twitchy since it jumps to the query window for every parameter change/cmdbutton sequence.&nbsp;&nbsp;<br><br>My guess is that what you want is a form that has a list box used to show the query results, with the underlying query designed for parameter entries (as Lightning offered above, e.g.,&nbsp;&nbsp;[Forms]![frmSeeResults]!cboPara)&nbsp;&nbsp;This gives a more stable info page.&nbsp;&nbsp;The problem I've yet to conclusively resolve is how to code an update to the listbox after combo entry (which event to use and what method--update the box or the form with requery, etc.)&nbsp;&nbsp;Also the listbox wizard doesn't like parameter queries for the source, so you have to set it up &quot;manually.&quot;&nbsp;&nbsp;(This is an attempt to direct you and solicit additional advice from the others.)

 

[Quehay]

First off, check Elizabeth’s FAQ response on the subject.&nbsp;&nbsp;It works well and the instructions are much more succinct than mine.&nbsp;&nbsp;However, I like to have the selection and<br>viewing occur all within one form.&nbsp;&nbsp;So, here’s the way to have a nice clean form view of a query with parameter input via a combo box(es) on the form:&nbsp;&nbsp;First you set up the query that you want to view (run it to test), then in the criteria area for the field(s) that you want users to be able to change enter [Forms]![frm_QryView]![cboFieldChoice], substituting the actual names of your form and combo box. .&nbsp;&nbsp;&nbsp;(This functions like a variable that allows changing values to be inserted into the WHERE part of the SQL statement.)&nbsp;&nbsp;Save the query with a name that will identify it as a parameter query as well as indicating what it shows&nbsp;&nbsp;&nbsp;Run the query by itself and type in one of the values from your list when prompted via input box to check the viability of the query in its new format.&nbsp;&nbsp;For the form view control use either a subform based on the parameter query (the wizard will allow you to set this up) or a list box set to the same.&nbsp;&nbsp;The query gives you the record navigator at the bottom with “Record #x of y total,” but the list box offers a cleaner presentation (you’ll have to adjust the column widths of the list box to get the look you want).&nbsp;&nbsp;The combo box has its RowSource set to the field of the table that you want to select from—this is just like setting up a Lookup table for entering the foreign key in the “many” side of a one to many relationship (more than one field to be selected requires repeating this process for each field, using separate combo boxes).&nbsp;&nbsp;The ID column will be hidden and the field name with an obvious value will be in the visible column of the box if you accept the default “Key hidden” option.&nbsp;&nbsp;Set the bound column property of the box to “2” as this sends the value that you need for the SQL statement (Access uses the primary key in lookup but this is not how you’ve instructed SQL to compare values).&nbsp;&nbsp;If you want the form to open with a default query use the default value of the combo box to enter one of the field values.&nbsp;&nbsp;Otherwise the query view area will be blank when you open it.&nbsp;&nbsp;It’s essential to have code that updates your query after choosing the parameters: In the code module of the form use the AfterUpdate event of the combo box and enter either (a) form.requery in the case of a subform with a query source or (b) lstMyListBox.requery for a listbox display.&nbsp;&nbsp;This makes for a very nice decision support/ information source form.<br><br>* If you use more than one parameter you should probably place the .requery method in a cmdButton click event with a label that tells the user to click after all choices are made.<br>