I've been trying to create a search form for my database for the past two weeks with little success. My latest incarnation is attached. I have a form (frmSearch) that has 4 listboxes on it, along with a Display button and a sub form (subSearch). There is code in the OnClick property of the Display button that I had hoped would look at each of the selections made in each listbox and return the appropriate record(s) in the sub form. It started working for two and even three listboxes but when I put in the fourth it stopped working. I wanted to get nine listboxes on the form to select from in all. Any ideas what I'm doing wrong? Thanks.
Initialy, I found that your provided code did indeed work. Your seach engine is very Field Specific. If any one item is missing from any of the four existing List Boxes (which would be contained within one or more specific records), then the search would fail. The engine worked fine....it just could'nt find a match. This would then be, I suppose, an Accurate Record Search. Only Records which contain the selected items will be displayed. I see nothing wrong with this scheme.
I have though, taken the liberty to change your code somewhat in order to make it more compact and flexible (located within the OnClick event of the Display button.
Now, you can have either 1 or 50+ List Boxes if you like and you don't have to modify the code at all to accomodate them. The key here is to make sure that the true Table Field Name is placed into the Tag property for the corresponding ListBox (see what I have done to your ListBoxes Tag properties).
Also, if a Listbox is contains no selected item(s) then the engine ignores it and carrys out the search with field items that are selected within other List Boxes. A match can be found if only 1 item is selected from one of the many ListBoxes.
Remember, if a ListBox contains a selected item, that item is added to the search stream. If it can't be found then there will be no match. The search will fail.
Your AddToWhere funtion is still used as is. No modifications was done here since it works fine as it stands now.
Thank you so much for the re-work! I see what you've done both with the code and with the listboxes. I can never thank you enough. This is not only going to assist with this project but with others I have coming up as well! I'm new to the coding process but with this kind of help I hope to find my way through a bit easier. Thanks again!