Dynamically search multiple fields

[ginabenina]

"access-programmers.co.uk/forums/showthread.php?p=942679"

I am trying to dynamically search multiple fields from a combo box on a form that includes a subform. I am using code from the above referenced link within this forum.

It works without the "setFocus" for the Listbox except it jumps to a record after the very first character is entered instead of narrowing down as characters are typed. If I leave in the "setFocus" I get run-time error 2110.



Any help is greatly appreciated!

 

[CJ_London]

Just to clarify.

instead of narrowing down as characters are typed

Are you saying that what you want is (for example) you have a list of products and suppliers and as you type characters into a control the list reduces

So you type 'a' and the list is reduced to all records which have an a in product and/or supplier

you then type b (so now you have ab) and the list is reduced to those records which have 'ab' in either the product name or supplier name

etc

 

[ginabenina]

Yes, exactly.

 

[CJ_London]

OK - I'll send over a small db with this facility - just need to extract it from a db.

It is good for up to about 50,000 records depending on how many columns you want to search on, after that it slows down a bit although still pretty quick!

 

[CJ_London]

Are you using 2007/2010 or earlier?

 

[CJ_London]

Give this a try - hope it meets your requirements

 

[ginabenina]

Thank you very much, i am trying it now.. I am using 2010

 

[davies107]

does this database of yours CJ_London slows down when there is a calculation involved? because mine has a same function like yours but different approach and it slows down because of the calculations....

 

[CJ_London]

Up to 20-30000 records there is little perceptible slowdown and over that it is acceptable to about 150000 records and above this number perhaps not so great performance.

Calculations will perceptably slow it down from perhaps 5000 records (depends on the calculation). In these situations I try to optimise the source - perhaps create a temporary table for the calculation in the subform load event and modify the recordsource to link to it. Putting the calculation in a subquery can also help.

Something I haven't tried is to load the entire source into memory. Not quite sure how to achieve this or even if it is doable in a form.

 

[davies107]

i have found samples of dynamic search databases also here:

http://www.accessforums.net/queries/true-keyword-search-19909/index4.html#post167355


but, it can only search 1 column/field though it can search even mixed words unlike mine it should be in order. im not also sure that if i will make this as a sample database it wont slow down (worst case) because of the calculation since what i have now is the worst when it comes to searching an item; it somewhat like a slow motion.

i have 5,021 items with 40 fields/columns, so its quite a big database PLUS calculation... sigh :banghead:

the gatsby user in the forum is me.... i posted on the last part.

 

[CJ_London]

You could try changing the recordset type on your form to snapshot - you won't be able to change data but you may find the filter works more quickly.

Also, another thing about my way of working - it blends the quickfind filter with the filter created using the access filtering So say Ii wanted all customers with 'smith' in their name and a postcode beginning 'DE' I can do this by using quickfind for smith and then use the access filter for the postcode. (Of course you can just use the access filter in this scenario)

I can then save the filter string in a table to be called back up as a 'previous filter' or when the form is reloaded.

I'm not saying other routines don't do the same, just this meets my functionality requirements

 

[davies107]

You could try changing the recordset type on your form to snapshot - you won't be able to change data but you may find the filter works more quickly.

Also, another thing about my way of working - it blends the quickfind filter with the filter created using the access filtering So say Ii wanted all customers with 'smith' in their name and a postcode beginning 'DE' I can do this by using quickfind for smith and then use the access filter for the postcode. (Of course you can just use the access filter in this scenario)

I can then save the filter string in a table to be called back up as a 'previous filter' or when the form is reloaded.

I'm not saying other routines don't do the same, just this meets my functionality requirements

Thanks CJ_London... i tried your suggestion about snapshot. when i changed it to my recent database it cant give result i dont know what happened but when i did it on my older database (less function, less data) it is working though still the slow motion is still there.

i dont get about the second suggestion. im not really an expert with Access, im just a beginner. i research and apply the things i learned. im just new to Access. im not even a programmer but i can understand if i can focus and give time to it.

i think there would be no solution for the "slow motion" effect database since i have lots of data stored plus with calculations. what i have in mind right now is to create a BUTTON named SEARCH that the user will click that button once they decided what word to search on the database because right now, once the user key-in or type a letter/word the database start searching (that goes with the calculation also) which makes the database soooo slow. if there is a SEARCH BUTTON i guess it will lessen the problem. this SEARCH BUTTON lets the user type in the complete word/s once done they will press the button and thats the time the database will search, calculate, and give results. unlike what i have now, every key stroke the database will search, calculate, and give results. so, how can i create a SEARCH BUTTON? i know how to create a button but how this button will function or give result, please help.

my database is from John, i just made some changes and additions but most likely the same:

http://www.access-programmers.co.uk/forums/showpost.php?p=1182790&postcount=4


NOTE: John's database works perfectly because it has less data, no calculations, and less functions than mine.

 

[CJ_London]

In your button on click event put the following:

SubformControlName.Form.Filter=YourSearchStr 'as built per my db
SubformControlName.Form.FilterOn=True

Note, If you are going this route, you might find it works quicker if you incorporate your search string into your subform recordsource since this will effectively reduce the number of calculated rows. In this case you would have

SubformControlName.Form.RecordSource="SELECT * FROM YourTbl WHERE " & YourSearchStr

Note: Incorporating it in this way may not be quite as simple as indicated if you already have a where string in your recordsource - easiest way to do this is to create a temporary query based on your recordsouce, add in the searchstr and view the sql code to get it's structure.

With regards you calculations, I'm surprised they are that slow and it could be your query can be optimised. For example, nulls can really slow things down, as can linking to another large table with a small 'lookup footprint' and/or lack of indexing in your tables.

I was once asked to improve the performance of a insert query which was taking 6+ hours to run. It was poorly optimised and I was able to reduce its run time to less than 15 minutes.

 

[Simon_MT]

I have found that qualifying data at the OpenForm is a lot quicker that loading the data and then performing the search. Yes I could make it multi-field but most often or not the users haver a piece of information and what to know what it is.

Secondary I rarely search Subforms instead I create a form based at the lower level and put the Master information in the Header. This just reverses the Parent-Child relationship. This all works off a Menu System and a Search Dialogue form using a SearchCriteria.

Simon

 

[jdraw]

davies107/gatsby,

I wrote the search at .

I left a message in that thread also.

My guess is that the search will be slow when you approach a large number of records * fields. There may be options depending on how much searching is needed in your application; how many new records you get and must search; and the frequency of new record additions.

My search is looking for a string in any position of many fields. Some posters asked for OR and AND logic and that was customized for them.

I find it a little hard to understand dynamically searching 40 fields. I would suggest
some sort of pre-indexing fields/keywords. Do you have any statistics on search terms being sought?

You might want to review the exchange on this thread if you anticipate a large text database and need searching
http://www.accessforums.net/programming/help-modiying-file-searcher-32497.html

 

[BillyBond]

That works a treat - many thanks CJ_London