Filter records with one to many in form, still edit data

[marv v]

Order information is downloaded from our web host. Credit card charge
information is downloaded from another vendor. The requirement is to display
all orders that are still “open†in a form and be able to update the order
data. An order is closed if it has a date shipped and amount paid (sum of
matching payment records) is equal to the amount owed on the order. All
other orders are “openâ€. Credit card charge information may contain zero to
several entries.

I created a form and subform using the order table and payment table which
displays all orders. I am able to edit the data in any order. How can I
filter out (don't show) closed orders and still allow the data in the order
table to be updated?

 

[Allen Browne]

It is probably the bit that sums the detail records that is making your form
read only.

Try setting the Record Source of the main form to a query that has just the
Orders table, but has a subquery in the WHERE clause. Using Northwind, this
recordset is updatable:

SELECT Orders.OrderID, Orders.OrderDate
FROM Orders
WHERE (Orders.ShippedDate Is Null) AND
((SELECT Sum(Quantity * UnitPrice) AS Amount
FROM [Order Details]
WHERE [Order Details].OrderID = Orders.OrderId) > 100);

By replacing the 100 with the name of your Payment Received field, you
should have the result you need.