Solved

Multi-Select criteria builder

Posted on 2006-11-07
17
737 Views
Last Modified: 2012-06-27
I understand how to build and apply criteria from multi-select list boxes but what I am noticing is that multi-select list boxeas ... well ... are not user friendly if you have to scroll. It starts to feel bad when you have to scroll a lot so I have started thinking about using tables to collect the criteria so they can use a combobox to select each criteria.  I thought about this a little bit it on the ride in this morning but was wondering if anyone has used this approach and has any practical advice in implemeting this or reasons I should reconsider my approach.

TIA,
Steve
0
Comment
Question by:stevbe
  • 7
  • 7
  • 3
17 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17888606
What do u mean by critera, do u mean data or list of fields?
0
 
LVL 39

Author Comment

by:stevbe
ID: 17888684
Sorry for not being clear, still working on second cup of coffee :-)
This is for user selecting report criteria ... select one or more customers, pick one or more vendors, one or more internal contacts etc.

so I was thinking if I had a seperate table for each type of multi-select criteria I could build a 1 column datasheet for and use them as subforms on my main report selector and criteria form, this way they can type/select from the combobox (auto complete and all :-)) and enter as many as they would like. I would then open a recordset and build the WhereCondition in the usual way for multi-select listbox

Select Vendor              Select Contact
----------------              --------------------
datasheet sub              datasheet sub
*     cbo                      *     cbo
----------------              --------------------
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 17888873
Some of your combo values are probably dependant on other combo's?

Other thing is what if u users to select different vendors, I assume then u have some button somewhere that says "ADD THIS CRITERIA"

I would add in something like "All Vendors" or "All Contacts" (more user friendly than say leaving it blank) into your combos

Could the report u use, make use of a standard query which has joins/inner queries to this temp table? So u dont need a recordset? Im not too familiar with your setup. Actually thinking about it, it may be slow.

If not joins Im thinking take a preexisting query (using Querydefs) get the SQL and add your WHERE bits in (using the recordset as u say) Im assuming this is what u are doing.

I guess u have a number of different reports also

0
 
LVL 39

Author Comment

by:stevbe
ID: 17889133
<Some of your combo values are probably dependant on other combo's?>
no ... straight up ... no dependencies

<I assume then u have some button somewhere that says "ADD THIS CRITERIA">
no ... if they select something it is automatically included

<I would add in something like "All Vendors" or "All Contacts>
I will think about this ... they are use to the idea that selections restrict data but I understand the concept you are presenting and will give it another go around inside my head :-)

<make use of a standard query which has joins/inner queries to this temp table>
I was thinking not because there are actually 4 multi-select fields (which means I will have 4 criteria tables) they may or may not enter criteria in so I would need to have a version for each combination of joins or start writing the crazy Like "'*'" & Forms! ... "'*'" Like "'*'" nonsense that while I have gotten it correct in the past always takes me too long to get up to speed again when I go into maintenance mode :-)

<If not joins Im thinking take a preexisting query (using Querydefs) get the SQL and add your WHERE >
I typically leave the based SQL alone and pass the WHERE in the WhereCondition argument of DoCmd.OpenReport. just like I have in the past when using multi-select listboxes ... you know ... For Each varItem In lstVendor.ItemsSelected ...

<I guess u have a number of different reports also>
yes, I have a based query that includes all of the criteria fields and PK field and then for each report I build another query that joins my criteria query with the approriate table(s) to provide the rest of the field values for display on the report.

Thanks for the ideas, I really want to think about all aspects of rewriting my report criteria methodology before I spend the time. I am optimistic and may use this concept as a standard for all my apps as I try to reuse code, best practice UI, etc. for speeding development and consistency.

Steve
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 total points
ID: 17891553
Hi Steve.
Good questions today - I'm indebted to you :-D

I've used table records for storing criteria before - though to be honest not recently.
(Not a conscious decision at all - just requirements based).
Indeed - have stored permanent parts of queries (Select/From/Group) and separately the selected criteria at runtime - pulling it all together at search/report time.

I think your concept is sound.
As long as you're displaying the criteria next to the selection methods (simply due to the add as you select concept - rather than select an entry and then click an "Add Criteria" button).
Should all work pretty effortlessly.
Of course you then have the age old decision to make.
Which optimizes better - a series of OR's or a couple of IN's  :-)
0
 
LVL 39

Author Comment

by:stevbe
ID: 17891665
I always go with IN's as this is for reporting and the perf should not be too different either way so they can wait :-) and  I don't have to worry about the number or ORs that Access can handle.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17891738
One beauty of a table based search is that it is then trivial to add "saved searches" - and to recall saved searches for use/editing :-)
0
 
LVL 39

Author Comment

by:stevbe
ID: 17892137
"saved searches"  =  v2.0

I am trying to figure out how *generic* I can make this process but I think I am always going to be stuck with modifing tbale names and delimiters per app.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17892207
Yeah I'd imagine you need to.
Some kind of search config table that determines the available options in any given application.
(Available searching fields and their types could be listed in such a table too - if you store the field type it saves hitting the db to determine them at runtime).

You'd just have to set up your search config table when you create each db.
(You'd not want every table to be searchable - as if nothing else - they'd be report dependent won't they?)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17892671
One question, since u say u add the selected criteria straight away

do u give the users the option to combine two combo boxes to make one filter for a report? or is it strictly one combo per table per report kinda thing

Im wondering if u allow multiple combination filters, how then do u handle that
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17892867
Well - I was imagining either one combo for each field you want to filter on - and the relevant entries for each field (in lookup tables?) in each combo.
Select from a combo and an additional criteria added for that field with the selcted value...
or
One combo listing the fields - and a second one that adapts from the selection in the first to show appropriate lists.
Again - one click selection - critieria added based on field and value combos.

Naturally - the latter is more flexible with regards to the number of fields upon which you can limit the results (and hence port this from application to application).
0
 
LVL 39

Author Comment

by:stevbe
ID: 17897361
<I was imagining either one combo for each field you want to filter on>
maybe I did not explain my thoughts clearly ...

take the simple example of selecting multiple criteria for a single field. Normally I use a multi-select listbox but I am thining that I can use a 1 field datasheet subform that has 1 combobox for that field so instead of picking multiple items in the listbox they enter multiple records using their combobox ... I think I am going to give this a whirl, maybe I will make a sample and post it to EE-Stuff.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17897927
So how many fields do you search on?
You did have only one listbox?  And now only one combo - but instead of multi select - it's add to a table for each combo click.

Just on the one field?
0
 
LVL 39

Author Comment

by:stevbe
ID: 17897994
no ...current app is 4 multi-selects and 5 single selects, I have another we use here in IT that has 7 multis and 8 singles :-) Each multi and single is for a different field.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17898541
OK - so you'd then have 4 new combo's to replace each listbox?
So it's a multiple control search form - one per field?
0
 
LVL 39

Author Comment

by:stevbe
ID: 17901247
<OK> -- No, each listbox is replaced by a datatsheet form. Each datasheet form has 1 field and that field's control is a combobox.

<So> -- yes, but I am using a datasheet subform for each field instead of a multi-select listbox.

I should have a sample ready tomorrow so you can see how inept I am at explaining this :-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17901278
Right - with you now.
(There must be a hundred ways of tackling this - hence the difficulty in seeing your particular vision).

I suppose UI requirements (screen real-estate) could determine different methods...

Cool
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now