[Solved] Error inserting null into non-nullable column

[dazedandconfused]

I added the 2nd sub form DRAWINGS AND DATA and after I enter the date then click on the column below I get the attached error. Why? I've spent hours trying to figure it out myself and I can't. I changed the columns approval, record, etc so that no input is required. I went back to the dates sent table and made sure the format was date. I checked the relations and that looks OK. When I type in job name up top it automatically shows it below which is what I want. I don't know what else to check. What's wrong? I'm thinking it has something to do w/ the relations maybe? Did I do something wrong?

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[Villeroy]

The ID is not an auto-ID. It is the dependent part of the 1-to-1 relation to table "PO Invoice"
So each new record needs to be related to one existing record in "PO Invoice". You achieve that by means of a list box. Showing some invoice number/name and putting the respective ID.

[dazedandconfused]

Thanks. I tried editing the table to set auto value to no. I got an error that it couldn't be changed and would I like it deleted...etc, etc...short story is that I had to delete the table and do it over making sure to set auto value to "no". To sum up a 45 minute story, I deleted the sub form and added it again. I double checked the master/slave, etc but nothing shows up in the Drawings and Data sub form. OK maybe I did something wrong? I double checked and didn't notice anything so I deleted it and did it once again...same result. Smashing the computer is looking like a good idea especially after the weekend I've had lol! What did I do wrong w/ the Drawings and Data form that it won't show anything?

[Villeroy]

Reading your error message and screen shot,
this describes the problem perfectly well:

Attempt to insert null into a non-nullable column.

this specifies the exact column:

column: ID

this specifies even the table:

table: Dates sent

and again, the full SQL statement with the names of 3 columns you did fill out:

in statement: [Insert into "Dates sent"("FK_ID_PO","Job Name", "Record")VALUES(?.?.?)]

Inspecting the table in question, I see that you have only one mandatory (not nullable) field which is the primary key "ID".
As far as I understand what you are trying to do, you have set up a one-to-one relation where each record in the "Dates sent" table is related to exactly one record in the "PO_Invoices" table which makes perfectly sense. Any "PO_Invoice" record without a related record in "Dates sent" has not been sent and each record in "Dates sent" stores details about the shipment. This should work as long as there is only one shipment per invoice. It will cause trouble as soon as you need to split one invoice into more than one shipments.

For the current one-to-one relation, all you need to do is specifying the "PO_Invoice"-ID when you insert a new record into "Dates sent". This single ID number is the primary key and the essence of your shipment and your form does not include this column.
I created a new form where you can pick the "PO_Invoice"-ID from a list box. The list box shows the ID, the invoice date and the customer name and it puts the invoice ID into the ID field which is a primary key and a foreign key at the same time because it specifies a unique invoice and a unique shipment by the same ID number.

[dazedandconfused]

Thanks again for your help. Yes. What I'm trying to do is relate the Job Name from PO_Invoice form to the Dates sent form. You said that my form doesn't include the column with the single ID number, I.e. primary key, but don't I do that w/ the linking of ID from PO_Invoice and Fk_Id_PO in Dates sent?? I'm trying to relate the job name specifically. I can do that on a separate form like you created, however I want it to all appear on the ORDER INFORMATION form.

It's not actually tracking the items shipped. For each job we send 3 different technical drawings or manuals called record, approval and OM. There will, in fact only be one per invoice. It's obviously a simple table and I could have created a separate one just to record this info. However, I wanted to avoid having the re-enter data or even select data (i.e. Job name). I thought it would be easy to just relate Job name in the PO_Invoice table to Job name in the Dates sent table, but I keep getting errors.

I like your table w/ the dropdown, however that adds an extra step of clicking the drop down then finding the job name.

Simply, I just want to have a table where the job name entered in PO_Invoice will be automatically recorded in the Dates sent table and then I can enter the dates for record, approval, OM as necessary. I don't want to select from a dropdown. How do I do that? I really thought I was on the right track.

[Villeroy]

I like your table w/ the dropdown, however that adds an extra step of clicking the drop down then finding the job name.

No, you can simply type the job number into the list box.

[dazedandconfused]

I like your table w/ the dropdown, however that adds an extra step of clicking the drop down then finding the job name.

No, you can simply type the job number into the list box.

Good point. I don't want to sound ungrateful because I really appreciate your help and all the help I've received in this forum. However, my boss would like it on the ORDER INFORMATION table and doesn't want to select from a dropdown in a separate table. He wants the job name entered automatically in the Dates sent table like I have it set up, but I can't get past the errors. What am I doing wrong where I can't get that to work? I have the relationship set properly where when I type job name in PO_Invoice it automatically is stored in the Dates sent form so I'm halfway there.

[Arineckaig]

However, my boss would like it on the ORDER INFORMATION table and doesn't want to select from a dropdown in a separate table. He wants the job name entered automatically in the Dates sent table like I have it set up

Is there any particular reason for the "Dates sent" table's date fields to be stored in a separate table from the "PO INVOICE" table?

It would appear all three date fields are wholly dependent on and determined by the PO INVOICE and could be stored in that table. As you will have seen from the filtering tutorial to which you were referred in your other threads, the row-set to be displayed a sub-from can readily be sourced from the same table as its parent form.

There are occasions, such as the need for preventing access to confidential data, where there is need for a one-to-one relationship between tables. In this particular application it would appear to add complexity for no obvious cause.

[dazedandconfused]

However, my boss would like it on the ORDER INFORMATION table and doesn't want to select from a dropdown in a separate table. He wants the job name entered automatically in the Dates sent table like I have it set up

Is there any particular reason for the "Dates sent" table's date fields to be stored in a separate table from the "PO INVOICE" table?

It would appear all three date fields are wholly dependent on and determined by the PO INVOICE and could be stored in that table. As you will have seen from the filtering tutorial to which you were referred in your other threads, the row-set to be displayed a sub-from can readily be sourced from the same table as its parent form.

There are occasions, such as the need for preventing access to confidential data, where there is need for a one-to-one relationship between tables. In this particular application it would appear to add complexity for no obvious cause.

Thanks for the response. Unlike PO INVOICE and PO ITEMS, Dates sent information isn't added during the same order. Sometimes not until a week after a PO was received and some of the information isn't entered until even longer-maybe a few months down the road. My boss said that he doesn't want to clutter up the other tables and he wants a separate one included in the ORDER INFORMATION form. I understand what you're saying, unfortunately I can't do it that way. What am I doing wrong and how does it need to be changed to work within the ORDER INFORMATION table?

[dazedandconfused]

So I've removed and added the table again for probably the 15th time. The latest problem is that I can't enter text and nothing shows up anymore for job name. Please, why?!

I appreciate the suggestions and comments but this is how I need to do it. If it's not possible just tell me please....I'm begging. I've spent weeks trying to figure this out and I'm at the end of my rope here.

[Arineckaig]

My boss said that he doesn't want to clutter up the other tables and he wants a separate one included in the ORDER INFORMATION form. I understand what you're saying, unfortunately I can't do it that way. What am I doing wrong and how does it need to be changed to work within the ORDER INFORMATION table?

I have attached a demo file that may or may not meet your requirements. If it does, I will do my best to explain what changes I made to the original copy of your database. If it does not, I should apologise for again failing to understand what you looking for.

[Villeroy]

There are occasions, such as the need for preventing access to confidential data, where there is need for a one-to-one relationship between tables. In this particular application it would appear to add complexity for no obvious cause.

Well, you can have one set of mandatory attributes associated with another set of mandatory attributes. In this particular case you have a invoices and one shipment per invoice. First you have the invoices, the shipment record with all its mandatory attributes may follow later. When adding shipment attributes to invoices it is easier to select all invoices that have no shipment record than selecting invoice records with somewhat incomplete attributes.

[Arineckaig]

Villeroy wrote:
When adding shipment attributes to invoices it is easier to select all invoices that have no shipment record than selecting invoice records with somewhat incomplete attributes.

If I had a better understanding of the OP's requirements, I would perhaps dare to wonder why a one-to-one tables relationship necessarily makes that task easier. Meanwhile I respect your experience and verdict.

[dazedandconfused]

My boss said that he doesn't want to clutter up the other tables and he wants a separate one included in the ORDER INFORMATION form. I understand what you're saying, unfortunately I can't do it that way. What am I doing wrong and how does it need to be changed to work within the ORDER INFORMATION table?

I have attached a demo file that may or may not meet your requirements. If it does, I will do my best to explain what changes I made to the original copy of your database. If it does not, I should apologise for again failing to understand what you looking for.

YES!!!!! EXACTLY!!!! THANK YOU!!!!

I think I see what you did. I removed this table in my database and started from the beginning in creating the Drawings & Data table. I added a new subform and called it Dates Subform. For that subform the content is PO INVOICE. I linked the master fields of "ID" for PO INVOICE and PO INVOICE like you did... That confuses me. Why is the master and slave linking the same table? Next I added a table control. I noticed that I had to add Job Name, Record, Aproval, etc...to the PO_Invoice form and then select the corresponding data field. That's probably why I wasn't able to type or select anything