Filter with query

[kitty77]

Is it possible to filter a form based on another query that has the field record ID?

Maybe a command button.

Thanks.

 

[DHookom]

Do you have any other context? You can use VBA behind a button to set the Filter property of a form. Does the other query return one record or more? Can we assume there is a field in the form's record source that relates to a field in the query?

 

[Ken Sheridan]

One way would be to amend the form's RecordSource property in code to join the form's default RecordSource to the query. As a simple example lets say you have a form based on the following query:

SELECT *
FROM Orders
ORDER BY OrderDate;

Rather than a button you could use a check box to toggle between open orders and completed orders if you have query named qryCompletedOrders with columns OrderID and Completed, the latter being a column of Boolean (Yes/No) data type. The code for the check box's AfterUpdate event procedure would be:

If Me.chkCompleted = True Then
    Me.RecordSource = "SELECT Orders.* FROM Orders INNER JOIN qryCompletedOrders " & _
    "ON Orders.OrderID = qryCompletedOrders.OrderID ORDER BY Orders.OrderDate"
Else
    Me.RecordSource = "SELECT * FROM Orders ORDER BY OrderDate"
End If

This would only be worth doing in reality if the query where far more complex of course, as the above could be done very simply by changing the form's Filter and FilterOn properties in code.

 

[kitty77]

So, the other query can return several records. Yes, both the form and query have a field called record ID.

 

[Ken Sheridan]

Rather than a button you could use a check box to toggle between open orders and completed orders if you have query named qryCompletedOrders with columns OrderID and Completed, the latter being a column of Boolean (Yes/No) data type. The code for the check box's AfterUpdate event procedure would be:

I should have said all orders rather than open orders. To return only the latter the form's default RecordSource would be:

SELECT * FROM Orders
WHERE NOT Completed
ORDER BY OrderDate