Form with subform

[Moe]

I have a form, with a subform, to enter payments for invoices. Main form has
customerID and InvoiceID fields (and some other fields) and subform has the
payment information fields to be saved in the "Payments" Table. My subform
and main forms are related through the "InvoiceID" and "CustomerID" fields.
The subform is built on a query that has columns "InvoiceID" and "CustomerID"
from "Invoices" table and some other columns from "Payments" table. The main
form query is partialy built from the same "Invoices" table. Both queries
have the same values in the "invoiceID" and "CustomerID' columns. However,
the "Payments" table remains blank and when I try to add values to the fields
of the subform I get an error that "Primary key can not be null." Looks like
the subform query does not pass the "OrderID" and "CustomerID" values to the
"Payments" table. How can I solve this problem?

 

[Rick Brandt]

I have a form, with a subform, to enter payments for invoices. Main form has
customerID and InvoiceID fields (and some other fields) and subform has the
payment information fields to be saved in the "Payments" Table. My subform
and main forms are related through the "InvoiceID" and "CustomerID" fields.
The subform is built on a query that has columns "InvoiceID" and "CustomerID"
from "Invoices" table and some other columns from "Payments" table. The main
form query is partialy built from the same "Invoices" table. Both queries
have the same values in the "invoiceID" and "CustomerID' columns. However,
the "Payments" table remains blank and when I try to add values to the fields
of the subform I get an error that "Primary key can not be null." Looks like
the subform query does not pass the "OrderID" and "CustomerID" values to the
"Payments" table. How can I solve this problem?

First lets clarify your table structure. I would expect the InvoiceID to be the
Primary Key of the Invoices table, not the combination of InvoiceID and
CustomerID. The composite key would be fine if you want to restart invoice
numbers per-customer, but that would be somewhat unusual.

Either way the Primary Key in the invoice table whether it consists of just the
InvoiceID or both InvoiceID and CustomerID should ALSO exist in the payments
table as foreign key fields. Do you have this? If so, you do NOT need to use a
query for your paymants form that includes these fields from the Invoices table.
The subform should be bound strictly to the payments table and the MasterLink
and ChildLink properties of the subform control will take care of propogating
the key values from the main form into new records created in the subform.

 

[Moe]

Thanks Rick. I'll try your recommendations and will let you know. However,
what if a customer pays an order in two different payments? If invoiceID is
the only primary key, which it is now, how can I handle multiple payments on
the same order? I also like to know how in "Northwind Traders" DB the "Order
Details Extended" query can update the "Orders Detail" table? I was trying to
implement some thing like that when I got into trouble.

 

[Rick Brandt]

Thanks Rick. I'll try your recommendations and will let you know. However,
what if a customer pays an order in two different payments? If invoiceID is
the only primary key, which it is now, how can I handle multiple payments on
the same order? I also like to know how in "Northwind Traders" DB the "Order
Details Extended" query can update the "Orders Detail" table? I was trying to
implement some thing like that when I got into trouble.

InvoiceId would only be the PK in the Invoices table. What I said was that the
Payments table needs a foreign key field that contains the related InvoiceID
that each payment applies to. The Payments table would still use another field
or fields as its Primary Key.

 

[Rick Brandt]

Rick
Got it. Thanks. It works (I still don't know why it did not work when
the subform was built on a query.) In order to have multiple payments
for the same record I added an AutoNumber field in the Payments
table. Now, it has three keys: customerID, InvoiceID and
PaymentNumber. Why I have the CustomerID as a key? Because the
Customers table has a "Balance" field which shows the balance on the
Main form. Now that the subform is built on the Payments table I need
to know how to apply the new Balance to the Customers table (update
"Balance" in "Customers") after a payment is applied? Still got time
to help me? Aslo, do you think having an AutoNumber field as PK can
cause problems?
Moe

You should not save this (or any other) calculated value. You should
calculate the total on the fly as needed. Even if you insist on trying to
do that you still would not need a CustomerID field in the payments table
because you can figure out the customerID by going through the Invoice table
(I assume the invoice table includes a field for the CustomerID?).