Hello,
I have a main form called "F_Invoices" and a subform named "SubF_Orders" which has to show the orders corresponding to the current invoice in the main form. The forms are linked through the field "Invoice_ID".
All invoice data is collected in table "T_Invoices" (primary key: Invoice_ID), and all orders data is collected in table "T_Orders" (primary key: order_ID").
Since 1 invoice can include items from many orders and 1 order can be invoiced several times due to different payment installments, I`ve inserted another table called "T_InvoiceOrders" that includes the fields: Invoice_ID, Order_ID and InvoiceOrderID (autonumber). The primary key for this table is a combination of these three fields together. That was a new thing for me, since I usually try to define a one-field primary key, but I wasn`t able to input data into the form otherwise. I`ve tried to set up my form in exactly the same way, that Pat Hartman did with his many-to-many example(http://www.access-programmers.co.uk/forums/showthread.php?t=153458 ) here. The only difference is that the third field in his table "tblBookings" is not an Autonumber field, but a Date field, where the user inputs data. This makes sense for the bookings sample, but would not make sense for invoices-orders processing.
My problem is that now I`m getting the "Index or Primary Key cannot contain a Null Value" error.
I am filling out the field "Orders_ID", "Invoice_ID" must be filled out automatically since the forms are linked, so I figured that the problem lies with the Autonumber field.
I hope that you have some simple advice for me on how to workaround this problem.
Added three hours later::
Ok, I`ve found part of the problem. It seems that when I redefined the primary key in the table "T_InvoiceOrders", from the autonumber field "InvoiceOrders_ID" to the combined primary key, Access has changed the data type for the field "InvoiceOrders_ID" to Double and not indexed. That is why it was not inserted automatically, when I input the orders data in the "F_Invoice" form.
I`ve tried to change it back, but it doesn`t give. Error message is along the lines, that it can`t be done due to repeating values in the index or primary key, which is bull, since the field was previously an autonumber field. And even if there would be no autonumber field, the primary key being only the two foreign keys from the tables "T_Invoices" and "T_orders", there would still be no repeating combinations.
I really hope that someone can help me with this one. Thank you in advance!
Best regards,
Alexandra
I have a main form called "F_Invoices" and a subform named "SubF_Orders" which has to show the orders corresponding to the current invoice in the main form. The forms are linked through the field "Invoice_ID".
All invoice data is collected in table "T_Invoices" (primary key: Invoice_ID), and all orders data is collected in table "T_Orders" (primary key: order_ID").
Since 1 invoice can include items from many orders and 1 order can be invoiced several times due to different payment installments, I`ve inserted another table called "T_InvoiceOrders" that includes the fields: Invoice_ID, Order_ID and InvoiceOrderID (autonumber). The primary key for this table is a combination of these three fields together. That was a new thing for me, since I usually try to define a one-field primary key, but I wasn`t able to input data into the form otherwise. I`ve tried to set up my form in exactly the same way, that Pat Hartman did with his many-to-many example(http://www.access-programmers.co.uk/forums/showthread.php?t=153458 ) here. The only difference is that the third field in his table "tblBookings" is not an Autonumber field, but a Date field, where the user inputs data. This makes sense for the bookings sample, but would not make sense for invoices-orders processing.
My problem is that now I`m getting the "Index or Primary Key cannot contain a Null Value" error.
I am filling out the field "Orders_ID", "Invoice_ID" must be filled out automatically since the forms are linked, so I figured that the problem lies with the Autonumber field.
I hope that you have some simple advice for me on how to workaround this problem.
Added three hours later::
Ok, I`ve found part of the problem. It seems that when I redefined the primary key in the table "T_InvoiceOrders", from the autonumber field "InvoiceOrders_ID" to the combined primary key, Access has changed the data type for the field "InvoiceOrders_ID" to Double and not indexed. That is why it was not inserted automatically, when I input the orders data in the "F_Invoice" form.
I`ve tried to change it back, but it doesn`t give. Error message is along the lines, that it can`t be done due to repeating values in the index or primary key, which is bull, since the field was previously an autonumber field. And even if there would be no autonumber field, the primary key being only the two foreign keys from the tables "T_Invoices" and "T_orders", there would still be no repeating combinations.
I really hope that someone can help me with this one. Thank you in advance!
Best regards,
Alexandra
Last edited:
