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
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