<b>Make New Recordset based on a Query when you hit a button?</b>?

[Nelz]

I have a form with contacts and a subform with calls and notes. I want to make a button that prompts for a keyword(more if possible) from the notes field on the subform and then opens those records.

I made a query (keyquery) that brings up the prompt, and finds the correct records. What I want to be able to do is have a form with all 72,000 records open, then hit the button, thereby filtering out only the appropriate records. Then I'd like to go back to all records again if possible....

Any ideas? I've been stumped for days. Seems to me I have done more complicated stuff, but I cant get this. Right now I made it so the form opens with the query as its recordset, but theres no way to get back to all the records again. I made a button that makes it so you can keep typing key searches, but thats not quite it. How do you do a simple recordset change like that?

My knowledge of VB Coding is limited. I basicaly cut and paste from stuff that works, or try stuff endlessly till I get it to work....Help???

 

[ds2728]

First, is the subform linked to the contacts form? Second is the list you wish to create generated from the subform list or another table?

You can add a "SEARCH" button to your form that would make visible a previously hidden "OK" button and a combo box that would allow for searching on a predetermined number of words. Once you you select the word to search for, click the ok button. In the sub ok_button_clicked you build a sql string that represents your query. then depends if data is same as your existing list box you can force in the list box properties box "rowsoucre" to equal your new sql string and perform a requery on the list box. If your data requires another list box then you would need to display it and do the same with rowsource.

Dave

 

[Nelz]

Thanks, Dave. I hope I'm not making this more complicated sounding than it is. I tried a couple of things. I had an unbound text box to enter the key words, with a button with this event:
Private Sub Command53_Click()
Me.RecordSource = "SELECT * FROM [Calls]" & "WHERE [Notes] LIKE *" & txtFind & "*"

End Sub
That actually worked...but I want to be able to unfilter...and just removing the filter with the menu button didnt do it. I suppose I could build another button that has a different query that brings up all records and call it "Unfilter" or something....

Then I just tried building a query with the criteria under the notes field like "[enter keyword]". The Query is called keyquery. That way I dont need an unbound textbox, since the query prompts for the keywords. What I really need to do is something that will basically turn the query on and off from the contact form itself. Like if I open the form to show all Contacts...then hit the button to apply the query, then turn it off again.... I'm assuming that having that [enter keyword] criteria will still function to prompt for the keywords.
Thanks in advance..going nuts because I have a feeling this is pretty basic stuff.. X-)

 

[ds2728]

You can use the same button and use and if statement to detect which state the button is in by using the .caption

sub command53_click()

if me!listbox.cation = "Filter" then
me!listbox.rowsource = filter_sqlstr
me!command53.caption = "Unfilter"
me!listbox.requery
else
me!listbox.rowsource = fulllist_sqlstr
me!command53.caption = "Filter"
me!listbox.requery
endif

you can use the parameter to ask for the search string if you want. The easy way to use the above is to build your working query in create a query interactively and once you have it working go to "view" select SQL view and copy then paste into your sub.

The problem I have found with using queries with built in parameters is that you can not control the users input search string to known inputs. You can control this by using a combo box tied to a table that has all search options in it. If this is not important than the query with a parametr is fine.

 

[ds2728]

Sorry I meant:

if me!command53.caption = "Filter"

 

[Nelz]

Well....since I'm searching on a "notes" field....a text input is fine. Like they can search for the letter "p" if they want..lol ...
But I guess whats confusing me is how to turn the filter on and off. I see what you're doing there seems to be making a comboWhere in the above example would I type the filter string? I'm really not that familiar with coding...I usually just try a million things until it works, then use what works the next time...not exactly proud of it but..I need a bit more help if possible..

It looks like what youre showing me is how to toggle a lits box and a button? Where you have listbox I would replace with the name of the list box? and add two fields filter and unfilter?

 

[ds2728]

As I said above you do not need to use the combo box for the search request, You can continue to use the parameter setting in your query.

make one button which you have said is named command53, on your form and in the caption properties enter "Filter" for the caption option. Your list box also has a name assigned to it. open the properties box for the list box click on the "all" tab and the first option in the list is name.

then in your sub in the form code add the following

Private sub command53_click()
dim full_list as string
dim filter_sqlstr as string

full_list_str = "SELECT * FROM [Calls]"

filter_sqlstr = "SELECT * From [Calls] _
WHERE ((([Calls].yourfieldhere)= _
[Your searh prompt here]));"

if me!listboxname.cation = "Filter" then
me!listboxname.rowsource = filter_sqlstr
me!command53.caption = "Unfilter"
me!listboxname.requery
else
me!listboxname.rowsource = fulllist_sqlstr
me!command53.caption = "Filter"
me!listboxname.requery
endif

end sub

listboxname = comes from the list box properties name option.

full_list_sqlstr = equal your sql string to query for all records

filter_sqlstr = your sql string to query for partical list

yourfieldnamehere = replace this with your field name from your table you wish to do the search from.

Your search prompt here - replace this with a prompt you desire.

This procedure will programatically change the row source with your desired query string and requery will replace the item list within the listbox to meet the new row source criteria.

 

[Nelz]

Thanks! I have this so far. I changed the textbox input field I had to be a list box...since it seems like thats what you said to do....and named it the same - [b}txtFind[/b}, and named the button Filter.Here's the onclick code for the button.

Private Sub command53_click()

Dim full_list As String
Dim filter_sqlstr As String

full_list_str = "SELECT * FROM [Contacts]"

filter_sqlstr = "SELECT * From [Calls] _
WHERE ((([Calls].Notes)= _
[Your searh prompt here]));"

If Me!txtFind.Caption = "Filter" Then
Me!txtFind.RowSource = filter_sqlstr
Me!Command53.Caption = "Unfilter"
Me!txtFind.Requery
Else
Me!txtFind.RowSource = fulllist_sqlstr
Me!Command53.Caption = "Filter"
Me!txtFind.Requery
End If

End Sub


It stops with an error and full_list_str is highlighted and it says "Variable not defined"

Sorry to be such a pain...????

 

[ds2728]

the following are two lines from your code:


Else
Me!txtFind.RowSource = fulllist_sqlstr


Change "fulllist_ssqlstr" to "full_list_str"

also in your if statement:

If Me!txtFind.Caption = "Filter" Then

change it to:

if me!command53.caption = "Filter" then

the if statement is checking the caption of the filter button you added.

I think both of these error were my mistake, hope this helps you.

 

[Nelz]

OK...I finally got it to work, although I pretty much gave up on getting it to work the way we have been discussing. There are too many things I don't know that are getting screwed up, so I did it a different way, although admittedly not as good...

I made 2 macros that use the apply filter on the form...
Then I made 2 querys - The first is the regular query for the form that shows all records, and the second added the criterea Like "*" & [Forms]![Contacts]![TextFind] & "*" under the notes field.

Then I added my text box on the form and called it TextFindand added one button that's called FIND and another called SHOW ALL. The SHOW ALL runs the macro that uses the full query and the find runs the macro that uses the criterea.

I know programmers can figure out better ways to do this...but it works beautifully, and after 2 days ...I'm relieved. Thank you so much for all your help!!! I'm sorry to be so dense when it comes to the coding.Hopefully someone may get something from this that helps them too.

 

[Nelz]

Not to make this a marathon...but I just saw your last reply and havent tried it yet. I am going to try it since I saved what I did before in a copy of the database I was working on....I really like the idea of the changing caption on the button!!!Thanks for the correction...