Attempt to insert null into a non-nullable column

[deKay]

I have a database I’ve used in Access for many years but have decided to convert to Base. I’ve set it up again from scratch, importing the data tables and recreating the main query, relationships and data input form.

I can edit existing records via my main query or form (that links to the main query), but whenever I insert a new record either in the form or the query, I get this message:

Attempt to insert null into a non-nullable column: column: System table: Main in statement [INSERT INTO “Main” ( “Box”, “Manual”, “Name”, “Players”, “Type”, “Year”) VALUES ( ?, ?, ?, ?, ?, ? )]

Not only have I set the table to allow null entries, these fields aren’t null! If I enter the data directly into the main table, that the query references, it works.

Any ideas? The database is here if anyone minds having a look:

https://dl.dropboxusercontent.com/u/50053/Games%20TEST%20FORM%203.odb

Using Base in LibreOffice 5.0.2.2, Java 8U65, on Windows 7. I have also tried it on the same versions of LO/Java on OS X El Capitan with the same issue.

[peterwt]

There are a number of problems in the way you have designed your database.

1. The Table Condition has four entries where the Condition is “Mint” ID’s 5,6,7,8. It is not good practice to do this but it may be a consequence of problem 2.

2. You state that you can update the database using the Query or Form. I have found that this does not work correctly. The first record in the Form, ID 4 has the Region set to Europe ID 3. If you change this in the Form or Query to Asia ID 0 it appears to work but if you look at the Main Table the Region is still set to 3. Looking at the Table Region though it has changed Region ID 3 to Asia from Europe so you have Asia in ID’s 0 and 3. So instead of changing The Main Table it is changing the Region Table.

You are basing the Form source on a complex Query which is not needed. As all the data that needs to be changed/added resides in Table Main it is better to have the Form source as the Table Main.

I am uploading a modified version. All the objects have 2 added to the names.

The changes are:-

1. The linked Fields have the names changed so they are the same in the linked fields.
   
2. The Form main2 has the source Table main2.
   
3. The Combo Boxes on the Form changed to List Boxes. A List Box can get its source from two Fields. It displays the Text but links on the ID.
   

Games TEST FORM 3MOD.odb