Keep Search Results

[Exodus]

Ok I have an unbound form that is used for searching for records. The results are displayed in an unbound sub form. After the search in done the code then binds the controls and record source for the subform to display the results. All this works great been using it for years. What I want to do now is have a way to keep certain records in the results and look for new searches giving new results ad displaying the selected old results. What would be the best way to implement this?

 

[vbaInet]

Ok I have an unbound form that is used for searching for records. The results are displayed in an unbound sub form. After the search in done the code then binds the controls and record source for the subform to display the results. All this works great been using it for years. What I want to do now is have a way to keep certain records in the results and look for new searches giving new results ad displaying the selected old results. What would be the best way to implement this?

are you using vba to perform your search?

 

[Exodus]

Yes and no. I use code to set the forms record source and the fields control source
the record source that returns the search is a querry in the database.

 

[vbaInet]

You want to save the results of the query or the sqlString built up using vba?

You should be thinking along the lines of creating one or two new tables to store either of the above

 

[Exodus]

You want to save the results of the query or the sqlString built up using vba?

You should be thinking along the lines of creating one or two new tables to store either of the above

Yep I want to temporarely save the results of the querry. I am hoping to avoid using tables for this.

 

[vbaInet]

If you want to save it for a long time then you would need to consider this option. If you wanted to save it for the length of time you had the database opened then you can use a global variable? In that case, all you do is have a command button that you will click to save the sqlString in a listbox/combo box. The listbox or combo box will have two columns, one for the date and time of the search and the other will hold the string. If you don't like the idea of a date/time stamp, then create a textbox next to your command button and enter a unique name for that search string, and this name will become the first column, whilst the sql string will be the second column.

Sounds like a good idea?

 

[Exodus]

This definately sounds better as the save is only for a brief time period being an hour at the most. The thing is we sometimes deal with same records over and over again with sevral minutes. Its just tiresome to keep going back and forth so that is why i am looking for a way to keep certain records displayed in the results area

 

[vbaInet]

So for starters, how many controls do you have displaying the results? And if they are not too many what are the names and types of the controls?

 

[Exodus]

Currently there are five controls all text boxes

Here are a couple of snap shots

Before Search

After search

 

[vbaInet]

Are the headers the exact names of the fields or are they aliases? What's the name of the subform, and the name of the subform control?

 

[Exodus]

Headers are the names
Subform Name and Control name is the same PollingPlaceResults

 

[vbaInet]

From the looks of your form, I think the search is performed using the form's Filter property is that correct? If that's the case then this is the best way to store it in a listbox:

Time of Search | Search String
------------------------------------------
12:00:49 | 31*
12:08:50 | *1002
15:35:21 | 678

If a user wants to run that saved search, they simply highlight anyone, click a button to show the results. Is this what you want? I can quickly implement this if this works for you.

 

[Exodus]

Search runs at the querry level. What i am looking for is to possible some how mark records to stay in the search results so say 3106 is marked to stay in results no matter what the search criteria is. Any help is more than welcome. I really apreciate your time and efforts.

 

[vbaInet]

Almost forgot I hadn't replied to your query. I believe there are a few ways round this. The approaches would depend on what "Poll Details" and how the search is carried out.

Firstly you would create an unbound listbox control (set as hidden) which would hold the IDs of all those poll details you will like to remain in your search. There will be a command button that adds an ID to the listbox when clicked but before adding you will loop through the listbox to ensure that ID hasn't already been added. That's your list of IDs sorted.

For the search bit, you can include the IN statement which is like an array in sql. Do a search on google. The IN statement will be included in the WHERE clause of your query that performs the search. The IN clause looks something like this:

WHERE [PollID] IN (1234, 3016, 453) AND LIKE * 310

Remember, those values are being pulled off your listbox. Actually, I think the listbox should be visible so that users can click and remove the ones they dont want included.

Do you get the idea?

 

[Exodus]

Sorry I have been out sick and have been playing catch up. This sounds like what I am looking for. Just got handed a priority project so my development is on the back burner. I will be working on this when i can and may be posting up some more.
Thanks for your help it is much appreciated.

 

[vbaInet]

Good luck with your new project and you're welcome.