Access 2007 - Create a command button that will filter my subform?

[Kat7]

Hello out there!

I've turned into an internet junkie trying to find the answer to this seemingly simple question. Every suggestion I try has failed. Form wizard, VBA, macros, modules ... I never wanted to know so much about SQL statements.

If it's not obvious, please be aware I'm not a programmer, just a clerk who happens to know too much about Access for her own good. I AM willing to learn whatever you are patient enough to teach, if needs be.

So ...

I have a form that uses an unbound combo box to filter to my subform. There is a combo box on the subform,[Priority] that has has a Row Source I entered manually. The options are High, Med, Low and Done.

I need a command button to filter out the 'Done' records (and possibly one to show all records).

Thoughts/Questions/Comments?

[liimra]

Solution/

First of all, good on you and welcome to Bytes (I don't know if I am allowed to welcome you since I am new too!). Anyways, it doesn't really matter if you place the button or the combobox on the form or the sub form as both will work.

Of course, the source for the records of the subform is table/query and most probably it will have the priority field, otherwise how would we sort them! If this is the case you can create two buttons. One for showing records that have the selected priority and another for showing all records. The first would have this VB code attached to its OnClick Event:

strSQL = "Select * from TableName where"
strSQL = strSQL & " Priority=" & "Forms!FormName!ComboBoxName"
Me!SubFormName.Form.RecordSource = strSQL

This will show only the records that you want on the subform. If you choose "Done", then only the records that are done will be showing. Suppose that you don't choose any value, then records with no priority set will be showing.

You can use this code for showing all records (No Filter)

strSQL = "Select * from TableName"
Me!SubFormName.Form.RecordSource = strSQL

Please note what I just stated in the attached database. I created it in Acc2007 and coverted it to 2003 (in case you are using it). I just added one more thing there (Will return Message if no value is selected).

Regards,
Ali

[beacon]

Hi Kat,

Welcome to Bytes!

Just to make sure I'm clear on what you're asking, you want a command button on the Main form to filter out the results in the subform if the value of the Priority combo box on the subform has a value of "Done", correct? Also, you are wanting a command button that will basically reset the action of the command button and show all records if the "Done" records were filtered out?

If so, I think you would probably get the most out of your form if you create a command button that serves as a toggle to show/hide the "Done" records.

Ok, assuming that you don't already have the command button on the form, go ahead and add it to the Main form. You'll need to know the SQL for the subform, both with the "Done" records and without. An easy way to do this is to go to the query for the subform and enter

Not "Done"

for the Criteria. Then go to View -> SQL View and copy/paste the SQL into the strRemoveSQL variable in the code I'm about to provide. Then go back to Design view for the query, remove the Not "Done" and go back to SQL View. You will need to copy/paste the SQL for this into the strAllSQL variable in the code I'm about to provide.

Once you've gotten this far, right-click on the command button, click on properties, and scroll down to "On Click" event. Click the dropdown and choose "[Event Procedure]" and then click on the elipsis (...) to the right. The VBA window should open for you with the template for the click event entered for you. In the event, copy/paste the following code:

Private Sub [YourCommandButtonName]_Click()

    Dim strRemoveSQL As String, strAllSQL As String
    
    strRemoveSQL = "SELECT * FROM [YourTable/QueryName] WHERE ((Not ([YourTable/QueryName].[Priority])='Done'));"
    strAllSQL = "SELECT * FROM [YourTable/QueryName];"

    If [YourCommandButtonName].Caption = "Remove Completed Records" Then
        Forms![YourMainFormName]![YourSubformName].Form.RecordSource = strRemoveSQL
        Forms!Forms![YourMainFormName]![YourSubformName].Form.Requery
        [YourCommandButtonName].Caption = "Show All Records"
    Else
        Forms!Forms![YourMainFormName]![YourSubformName].Form.RecordSource = strAllSQL
        Forms!Forms![YourMainFormName]![YourSubformName].Form.Requery
        [YourCommandButtonName].Caption = "Remove Completed Records"
    End If
        
End Sub

You will need to substitute the names of your table/query, form, subform, and command button names in the areas I've identified.

When all is said and done, and you run the form, the command button should start out saying "Remove Completed Records" and should remove all records with "Done" in the [Priority] field when the button is clicked. It should also change the caption of the button to now read "Show All Records". If you click the button again, all records will show and the caption will change back.

Hope this helps,
beacon

[Kat7]

First, thank you for the welcome and for taking on my problem! I got back to it yesterday afternoon and was anxoius to try out the solutions.

liimra's suggestion worked, but didn't really do what I was looking for. I just need to remove the "Done" records (and show all). Thank you though!

Beacon's solution worked best but there is one snag. The form is based on a table, not a query. I created a query to mimic my table, but then the unbound combo box on my form was not filtering the subform.

A little extra info in case that proves confusing. It's a very simple database (just a tool to organize managerial issues). I've got the original table with issue info. A second table I made with list of the managers, specifically for the combo box filter on the form. The form has the combo box manager filter and the subform with all the info.

Really, I thought this was going to be an easy project. Hah! Thanks again for all your help!

Kat

[liimra]

Solution/

You thought this was going to be easy project and yes it is an easy project.

If you want to exclude the selection (done or else) you just change = to <>!

strSQL = strSQL & " Priority<>" & "Forms!FormName!ComboBoxName"

The thing you want is accomplished in this approach (filtering records), the criteria is the last thing to worry about whether it is one or many.



Regards,
Ali

[beacon]

I apologize Kat...I assumed, incorrectly, that you were using queries on your form, not tables.

I'm glad that at least part of what I suggested worked out for you and hopefully Ali's change to my suggestion will help you finish the project.