If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Form with subform
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? |
#2
|
|||
|
|||
Form with subform
"Moe" wrote in message
... 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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Form with subform
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" wrote: "Moe" wrote in message ... 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. -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
Form with subform
"Moe" wrote in message
... 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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#5
|
|||
|
|||
Form with subform
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 "Rick Brandt" wrote: "Moe" wrote in message ... 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, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Form with subform
Moe wrote:
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?). -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|