Simple Solution Needed: Index or Primary Key cannot contain a Null Value.


  • Hello!

    I've run into a snag in my very basic database.

    I get that infamous error message, and I think it has something to do with the source query, which I cant get to show the payments that already exist in the table.

    Here is a link to my access file hosted on dropbox:

    Thanks for your help!

    Monday, April 23, 2012 4:03 AM


  • Not looking at your .accdb for security reasons and time constraints - but I am guessing you have a primary key across two or more fields which may each be primary keys on other tables (so that this "primary key" or other "unique index" in the table you are talking about, constitutes a foreign key) and that one or more of these fields must sometimes be allowed to contain Null values (representing "unknown", "don't care", "applicable to all" or similar).

    In this case the error message is giving you the right guidance. I suggest for you to choose a particular non-Null value that represents "unknown", "don't care" or whatever it is that you want to represent with a Null value; and then set up your Table Definition and Form Design so that this particular "special flag" value is the default for the field, or at least, is handled correctly whenever records are loaded/ saved.

    If you are using a ComboBox (which is generating a Null or an Empty String "" when a value is not chosen), try using a UNION query to set a special Null-representing value (say, the number zero) with a special string for user selection (say, the string "- UNKNOWN -"). Your union query might look like this:

    (SELECT id_field, label_field, 1 AS order_fixer
    FROM table_name)
    UNION (SELECT 0 AS id_field, "- UNKNOWN -" AS label_field, 0 AS order_fixer FROM table_name)
    ORDER BY order_fixer, label_field;

    (You might want to try "ORDER BY order_fixer, id_field" in some cases.)

    This will fix the problem because as far as the database is concerned, you don't have Null values in the database. But you will know that the number 0 represents whatever kind of unspecified value you want to represent.

    ON the other hand, if you are using a TextBox (which is generating a Null or an Empty String "" when a value is not chosen, depending on bound column type); you might want to specify a DefaultValue of "- UNKNOWN -", and use the BeforeInsert, BeforeUpdate, On_Current Events, etc.; to make sure data are entered correctly into the database. I haven't tried this myself but I'm sure this could be done.

    Matthew Slyman M.A. (Camb.)

    Monday, April 23, 2012 12:28 PM