Help with filter on form

[tofu]

Hi all,

I'm having trouble getting a filter to work on a form. What I want to do is when I open up the customer order-history form, have it not show any cancelled shipments, but show shipments that are in transit and completed.

My main form is my customer order history form, and the shipments information (which contains the order status) is a subform within the mainform.

I tried using the filter button but I need to toggle it back on each time.

Any help? Thanks!

 

[VilaRestal]

OK, not knowing the names of your tables or fields:


Set the subform's recordsource to a query (make a query) based on the same table that has the where clause:
Shipment <> 'Cancelled'

Or the equivalent for whatever field names, types and values you have.

 

[tofu]

Great, thanks for the tip - didn't know it would be so easy.

So this is what I have:
1) Multiple tables organized by customer info, shipments, products, etc.

2) QueryShippingdetails - fields are (shipment status, date, customerID, Customername, and some others which are sourced from the tables) - I've set filter to <> "cancelled"

3)SubFormCustomerHistoryShipments - fields are drawn from the QueryShippingdetails, indicating all the invoices/shipments customer has ordered EXCEPT cancelled orders

4) FormCustomer - fields are (customer ID, customer name, address, phone, etc.) which lets me find a customer, and see any outstanding shipment orders in the subform above

--

Now, the above works (thanks again); however, I want to know if there's a way to still see the filtered field in the query, but NOT see it in the subform

For example, I use the Query as the recordsource for two forms;
1) the aforementioned SubFormcustomerhistoryshipments sorted by customer
2) Another form listing the history of ALL the invoices (cancelled, completed, in process, etc.)

Is this possible, or would I have to create another query table without the filter for the second form?

Cheers,

 

[VilaRestal]

Yes, you don't have to show fields in forms. Go into the form design and delete the textbox bound to that field.