Filter records in same form

[aymanmb]

Dear all,

I have a form with a subform that is linked to a query. the current record Source is as follows:

SELECT tbl_Orders.OrderID, tbl_Orders.PurchaseNo, tbl_Orders.CustomerID, tbl_Orders.StartDate, tbl_Orders.DueDate, tbl_Orders.EndDate, tbl_Orders.InovoiceNr, tbl_Orders.Notes, tbl_Orders.StartDate, tbl_Orders.StartDate, tble_Customers.CompanyName, tbl_Orders.TaxFromOrigin
FROM tble_Customers INNER JOIN tbl_Orders ON tble_Customers.CustomerID = tbl_Orders.CustomerID;

I want to add a button that upon click will filter the recrds in same form to show all records where tbl_Orders.EndDate = Null

How can do that

 

[Moniker]

In the button's OnClick event, put this:

YourFormName.RecordSource = "SELECT tbl_Orders.OrderID, tbl_Orders.PurchaseNo, tbl_Orders.CustomerID, tbl_Orders.StartDate, tbl_Orders.DueDate, tbl_Orders.EndDate, tbl_Orders.InovoiceNr, tbl_Orders.Notes, tbl_Orders.StartDate, tbl_Orders.StartDate, tble_Customers.CompanyName, tbl_Orders.TaxFromOrigin
FROM tble_Customers INNER JOIN tbl_Orders ON tble_Customers.CustomerID = tbl_Orders.CustomerID
WHERE tbl_Orders.EndDate Is Null;"

YourFormName.Requery
YourFormName.Refresh

 

[aymanmb]

thanks a lot, I did as you said, but it gives a syntex error and when debug you see attached photo

 

[Moniker]

Change "YourFormName" to the name of your form. Additionally, you can't have breaks (carriage returns) in the code line (without using underscores to connect them).

YourFormName.RecordSource = "SELECT tbl_Orders.OrderID, tbl_Orders.PurchaseNo, tbl_Orders.CustomerID, tbl_Orders.StartDate, tbl_Orders.DueDate, tbl_Orders.EndDate, tbl_Orders.InovoiceNr, tbl_Orders.Notes, tbl_Orders.StartDate, tbl_Orders.StartDate, tble_Customers.CompanyName, tbl_Orders.TaxFromOrigin FROM tble_Customers INNER JOIN tbl_Orders ON tble_Customers.CustomerID = tbl_Orders.CustomerID WHERE tbl_Orders.EndDate Is Null;"

 

[aymanmb]

Ok I did, as per below:

Frm_Orders.RecordSource = "SELECT tbl_Orders.OrderID, tbl_Orders.PurchaseNo, tbl_Orders.CustomerID, tbl_Orders.StartDate, tbl_Orders.DueDate, tbl_Orders.EndDate, tbl_Orders.InovoiceNr, tbl_Orders.Notes, tbl_Orders.StartDate, tbl_Orders.StartDate, tble_Customers.CompanyName, tbl_Orders.TaxFromOrigin FROM tble_Customers INNER JOIN tbl_Orders ON tble_Customers.CustomerID = tbl_Orders.CustomerID WHERE tbl_Orders.EndDate Is Null;"

Frm_Orders.Requery
Frm_Orders.Refresh

Still gives an error, object required and when debug goes to 1st line of the code: Frm_Orders.RecordSource

 

[Moniker]

Is Frm_Orders a subform? If so, you have to address it as such.

Me.Frm_Orders.Form.RecordSource

The same will have to be done to the requery and refresh is Frm_Orders is a subform.

 

[aymanmb]

no Frm_Orders is the main form with a subform sFrm-OrderDetails

 

[aymanmb]

I managed to do what I want by attaching to the button a macro that will run the query as a filter.

now how can I reset the form to show back all records. tried to do another button/macro but could not figure it out