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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
Back
Top