Dynamic MultiSearch problem - parameter, forms

[PaulWilson]

Hey,
I use John Big Booty's dynamic multiSearch concept which I found on this forum at:
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

It works well, but I want to extend it.

How it works is a textbox (txtSearchFor) on a form (frmSearchForm) has a number in it like: 12
I go to the query in design view and enter this:
like "*" & forms!frmSearchForm!txtSearchFor & "*"

It works fine when the parameter is any integer.

The problem is I'm now using a multi-select listbox and txtSearchFor says something like: 12 Or 39

When I do a msgbox on txtSearchFor it does give me what I expect but the query doesn't work anymore. I can type in 12 Or 39 and get the right result.

How do I change the parameter syntax so I can use a single number or something like "12 Or 34 Or 33"?

p.s. or for that matter 12 And 34 And 33!

 

[John Big Booty]

What you are attempting to do is beyond the scope of that sample as it currently stands.

To add additional criteria as you are looking to do, you will need to add an additional pair of text boxes for each set of additional criteria that you wish to add, along with the associated code for each of those sets of text boxes.

As for changing between Or and And criteria that is certainly doable, but how you might approach that is going to depend largely the number of text boxes you have.

 

[PaulWilson]

Yes,
Thanks John BB
Warning: This reply may only be logical to those who have worked through your example. I guess it provides an incentive to have a look at your original post.

It seems I can type in the phrase "1 Or 2" in the QBE grid and my query will reflect that I want 1 or 2 giving me both record 1 and record 2. If I create a string elsewhere on a control in some form which says "1 Or 2" and then create a QBE parameter that looks to that control, the QBE parameter will not work. I also tried such things as In(1,2). No dice.

In other words, you can write a snipped of SQL such as "1 or 2" in the QBE grid, but if you can't parameterize it and bring it in from a form dynamically.

 

[John Big Booty]

When you put the criteria 1 Or 2 in the query builder grid it works because Access recognises the Boolean Or operator. It will not however work when you type it into the search form window, because Access is now searching for a record that contains the literal string 1 Or 2.

To be able to do what you want with my dynamic search for you will need to add additional pairs of search boxes and associated code behind them and additional criteria within the underlying query to account for them.

If you pull the sample apart (all aspects, Code, Queries, etc.) and see how it all works, specifically the additional criteria for the Option Groups, you should be able to get it to work. Once you are familiar with the principals used in that sample you should be able to expand it to do what you are after.

I pretty sure everything you need to know is contained in the sample. Post back if you have a specific question. But at this point I have no interest in following you down the path you are currently on.

 

[PaulWilson]

Imagine if you will a table of assets (tblAsset). Each asset record is associated with an asset type (tblAssetType), a location (tblLocation), a department (tblDept) and a status (tblStatus). Each record in tblAsset can be linked to any number of records in tblLocation, tblAssetType, tblDept and tblStatus. We're talking four many-to-many relationships -- four junction tables. As in life, many-to-many relationships abound.

Accordingly, I have four multi-select listboxes. One each for status, location, department and type. I also have a textbox where a user can enter a search string. So, I need to work with any number of selections from each of the listboxes - not just one or two. If a user was limited to one selection from each listbox, I'd be done.

Right now my workaround is to use a form for the results of the search (instead of a listbox) and apply filters to it based on the dept, status, location and type selections using the classic me.filteron idea. The only issue now is that Access doesn't seem to want to requery the form when the filter is cleared. Right now I have to close then re-open the form to get Access to requery the records once the filter is cleared. What is that about? Anyway, I hope to post an example soon.

aaagh!