Index or primary key error

[So Call Me Crazy]

I have a form whose record source is a query used simply to retrieve data
from 2 related tables. Table 1 is the main data table. Table 2 is more of a
reference table for an item on Table 1. Table 1 has a code that represents
state and county, Table 2 is the translation for that code.

Everything on this form works UNTIL I try to add a record. I am getting
"Index or primary key cannot contain a Null value".

 

[Klatuu]

What data types are the primary key fields for the two tables? Are either or
both Autonumber fields?
Are the primary key fields in both tables included in the form's record
source?
How are the tables related?

 

[So Call Me Crazy]

Table 1's key field is a serial number. Table 2's key field is the code that
represents a state and county. Table 1 contains that state/county code and
that is how they are related. The primary key for Table 2 is not in the
form's record source.

I think I've figured out that I don't want to be doing this this way. If a
record is added, it's going to try to add a record to Table 2, isn't it... ?
Don't want that happening. Only want to add a new record to Table 1.

 

[Klatuu]

Actually, yes. But to associate the code for the state/county. I would have
a combo on the form that has a row source based on table 2. It would have
two columns, the state/county combination and the code. The code would be
the bound column and the combo control would be bound the the field in Table
1 where the state/count code goes.

 

[John W. Vinson]

I have a form whose record source is a query used simply to retrieve data
from 2 related tables. Table 1 is the main data table. Table 2 is more of a
reference table for an item on Table 1. Table 1 has a code that represents
state and county, Table 2 is the translation for that code.

Everything on this form works UNTIL I try to add a record. I am getting
"Index or primary key cannot contain a Null value".

Please post the SQL view of the query.

You may want to base the form just on Table1, and use a Combo Box on the form
bound to the state/county code; the combo box could store the code while
displaying the state and county, without the need to include the translation
table in your form's query.

 

[So Call Me Crazy]

Yes. This is what I'm trying to accomplish now. Now experiencing other
weirdness.

The Locale Code (that code that is a non-descript state/county code) is a
field on the form.

I need 2 combo boxes on the form. One for the state portion of the Locale
Code, the other for the county portion. They need to reflect the
descriptions stored in Table 2 for the state and county that is present for
each record in Table 1. They also need to enable a user to change the state
and/or county and have that change reflected in the Locale Code that is
stored for that record in Table 1. So, if a user changes the state, the
counties are limited to those counties in that state. And then, on a new
record to Table 1, the user picks a state, then a county for that state, and
the Locale Code for that state/county selection is then stored in the Locale
Code field for that new record.

Ugh. This was working until I ran into the issue when trying to add a new
record.