How can I get a button to load a query on my form?

[NaomiC]

Hello,

I know how to filter my form so I get the records from a certain query by clicking Advanced>Filter by Form>Load From Query>Selecting the query I want> Toggle filter.

What I want is a button that will do this just by clicking it. By default I have set my form to load a blank record. I also have a button to filter by an exact ID number. What I can't figure out is how to write the expression/macro to acheive this on my "Unprocessed button".

I am not familiar with VBA/SQL, so if anyone can baby talk me I would appreciate it.

The red button is the one I need to apply my query to:


Thank you!

[June7]

Review http://allenbrowne.com/ser-62.html

http://www.datapigtechnologies.com/f...tomfilter.html

[NaomiC]

Thank you! I'll take a look and if I have any problems I'll let you know.

[NaomiC]

Hi June,

This is very helpful information, however, a Combo box will not work with what I want my form to display. From what I understand from the first link is it is not recommended to use a query as a filter. Is that right? If so, I may just continue what I was doing before. As I was trying different things I almost killed the form completely so I may wait until I am better at understanding the language (VBA/SQL)

[June7]

I don't use dynamic parameterized queries. I prefer method from the first link.

[ssanfu]

I also have a button to filter by an exact ID number.

How did you accomplish this? Macro? VBA code?
Would you post the code?

What I can't figure out is how to write the expression/macro to acheive this on my "Unprocessed button".

What are the requirements for the "Unprocessed button"? How do you determine "Unprocessed" records?


Or post the dB. Change any sensitive data.....

[NaomiC]

Hi Steve,

The ID Search button is text box based and I used a macro. A co-worker set it up for me when I was introduced to Access and I duplicated it for one other text based search as well.
I used ApplyFilter Where Condition = [AP_ID] Like "*" & [Forms]![form_name]![txtsearch]

The goal of the "Unprocessed" button is to filter my form to show only records that need to be worked. These records are added by email replies automatically, so I set up a query to find the records with missing information that I enter once I process them. Then I can click the previous/next record button in the header of my form to increase my production time. If I load the query using the filter options I mentioned in my original post the results are wiped out if I have to search by ID number, thus I have to reload the query. I know it sounds lame, but its too many button clicks and I want to be able to alternate my work quickly if necessary.

The Query "AccessDataCollectionRefChk" is designed like this:

Please let me know if you still need a sample of my DB. I would have to remove almost all of my data in order to send or just make up alot.

Thanks!

[ssanfu]

It would help to have a sample of your dB. Maybe a few records that are "Processed" and a couple that are "Unprocessed".

[NaomiC]

Ok I'll get some examples but it will have to wait until Friday. Thanks for your patience and assistance.

[NaomiC]

SampleRefCKDatabase.zip

Hi Steve,

Thanks again for your assistance. The 3 records that come up on my query are the unprocessed records. When the form is opened it does ask for the SR parameters since I deleted alot of info (I'm not sure how that happened). Thanks!

[ssanfu]

I changed a few things. I created a new query and used it as the record source for the form.
RE: the "SR" error.....In the form properties, the data dab has a property "Order By" that is set to include a field "SR". No field "SR"... so the error.
Much easier to set the sort order in a query.

I couldn't get a macro to work, so I converted all macros to VBA.
I also added a button to remove filters.

I created a copy of the form and set the view to continuous. I prefer to see all records, then set a filter to limit the records viewed. (this is just a demo - delete it if you don't like it. )

[NaomiC]

Thank you! I like the idea of the continuous form and the Unprocessed/Show All Records buttons work great.

However, the Ap ID Search does not seem to be functioning properly. It will only return the correct record after clicking the Show All Records button. If it has a filter applied it will not work. Before, it would return the record even if it was filtered down to 1 record. Can you help me fix that one?

Or - What I may do, is duplicate my current form, and set the query as the source for the form like you mentioned but have it show only the unprocessed records. How would I set up the form to load the query once it is opened? Would there be any conflict if I have both forms open and update records with either form?

[ssanfu]

My bad.... I changed the name of the form(s), but didn't update code for the Ap_ID button. (and didn't test enough )
I change [Forms]![Reference Check Form]![txtsearch] to Me.txtsearch (on both forms).. All good now.



Maybe this is wrong, but I changed the AP_ID search. You had * & txtsearch; if you entered 21 into the unbound text box, the result would be any AP_ID that ENDED in 21. I changed it to 21* so that the search would return all records where the AP_ID BEGINS with 21. If you remember that a record begins with 21 but don't want look up the rest of the ID, when you click the search button, two records will be returned (in this dB example).


I forgot to say I changed the event for the first name and last name rename code. The change event fires EVERY character entered into the text box. I change the event to the after update event.

How would I set up the form to load the query

You don't "load" the query. Think of a query as a virtual table. The query is bound to the form. As soon as the form opens the data from the query is there just like a table.

From what I understand of your dB, I would recommend 1 form if the "unprocessed records" button meets your needs.

[NaomiC]

Thank you so much for all your help. My txt search boxes work great along with the unprocessed/show all buttons.


I will keep the one form as you advise.

Does it really matter if I have the form bound to my table or the query then? It appears to work either way...

[ssanfu]

You're welcome

Does it really matter if I have the form bound to my table or the query then? It appears to work either way..

Yes, it works either way.

Maybe it is a carryover from my A97/A2000 days, but I always bind a form to query.
In A97/A2000, you couldn't sort the records if you bound a table to the form. In a query, you would set the sort order to display in the form.

Just my preference... everyone has their quirks....