Query Error Message

[Tom]

Using AccessXP in 2000 mode.

I have the following tables ---
TblLocation
LocationID
PropertyID
StateID
CountyID
CityID

TblState
StateID
State

A Type 1 relationship exists between StateID in both tables. I created a
query based on both tables that includes all the fields in TblLocation and
the State field in TblState. StateID is joined between the two tables in the
query. If I run the query, enter 1 for PropertyID and then try to close the
query, I get the following error message:
"The Microsoft Jet Database Engine can not find a record in the table
'TblState' with key matching field(s) 'StateID'.
When I click OK this message appears:
"You can not save the record at this time".
I get the same behaviour if I delete the relatinship between the tables and
just rely on the join in the query.
Why do I get this error message?

Thanks,

Tom

[Salad]

Tom wrote:

Using AccessXP in 2000 mode.

I have the following tables ---
TblLocation
LocationID
PropertyID
StateID
CountyID
CityID

TblState
StateID
State

A Type 1 relationship exists between StateID in both tables. I created a
query based on both tables that includes all the fields in TblLocation and
the State field in TblState. StateID is joined between the two tables in the
query. If I run the query, enter 1 for PropertyID and then try to close the
query, I get the following error message:
"The Microsoft Jet Database Engine can not find a record in the table
'TblState' with key matching field(s) 'StateID'.
When I click OK this message appears:
"You can not save the record at this time".
I get the same behaviour if I delete the relatinship between the tables and
just rely on the join in the query.
Why do I get this error message?

Thanks,

Tom

Are you adding a new record? Have you entered a StateID? If no StateID
is entered, there is nothing to link to the state table.

[pietlinden]

Why do you need a StateID? I mean, if you're dealing with US states,
then they're already unique, and only 2 characters long. I'd just use
the State field and then use a combobox in my form.

SELECT State
FROM tblState
ORDER BY State

and be done with it. IMO, you're making this more complicated than it
needs to be.

[Tom]

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.

Tom
"Salad" <oi*@vinegar.com> wrote in message
news:uF**************@newsread3.news.pas.earthlink .net...

Tom wrote:

Using AccessXP in 2000 mode.

I have the following tables ---
TblLocation
LocationID
PropertyID
StateID
CountyID
CityID

TblState
StateID
State

A Type 1 relationship exists between StateID in both tables. I created a
query based on both tables that includes all the fields in TblLocation
and the State field in TblState. StateID is joined between the two tables
in the query. If I run the query, enter 1 for PropertyID and then try to
close the query, I get the following error message:
"The Microsoft Jet Database Engine can not find a record in the table
'TblState' with key matching field(s) 'StateID'.
When I click OK this message appears:
"You can not save the record at this time".
I get the same behaviour if I delete the relatinship between the tables
and just rely on the join in the query.
Why do I get this error message?

Thanks,

Tom

Are you adding a new record? Have you entered a StateID? If no StateID
is entered, there is nothing to link to the state table.

[Justin Hoffman]

"Tom" <no***@email.com> wrote in message
news:9R****************@newsread1.news.atl.earthli nk.net...

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.

Tom

That is correct, but the field 'StateID' may have a default value (typically
zero for a long integer) so even though you leave it blank, zero is filled
in and this value does not appear in the related table. Check the design of
the table.

[Tom]

Thanks for your response, Justin!

I deleted the zero default value when I designed TblLocation so the default
value is blank. When I open the table and look at the new record line,
StateID is blank.

Tom
"Justin Hoffman" <[e-mail]> wrote in message
news:dd**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"Tom" <no***@email.com> wrote in message
news:9R****************@newsread1.news.atl.earthli nk.net...

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.

Tom

That is correct, but the field 'StateID' may have a default value
(typically zero for a long integer) so even though you leave it blank,
zero is filled in and this value does not appear in the related table.
Check the design of the table.

[Tom]

I changed StateID in TblLocation to StateInit and StateID in TblState to
StateInit. Made StateInit in TblState the primary key. I joined StateInit in
both tables in the query. Still get the same error message!
<pi********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Why do you need a StateID? I mean, if you're dealing with US states,
then they're already unique, and only 2 characters long. I'd just use
the State field and then use a combobox in my form.

SELECT State
FROM tblState
ORDER BY State

and be done with it. IMO, you're making this more complicated than it
needs to be.

[Justin Hoffman]

"Tom" <no***@email.com> wrote in message
news:56****************@newsread1.news.atl.earthli nk.net...

Thanks for your response, Justin!

I deleted the zero default value when I designed TblLocation so the
default value is blank. When I open the table and look at the new record
line, StateID is blank.

Tom

That's good, isn't it? I'm sure removing the default value of zero is a
useful thing to do anyway - afterall, it serves no purpose - unless you have
a valid state with id=0.
Has that solved your problem or are you still stuck? I am not quite sure of
the purpose of your query. To add new records to TblLocation? To edit
them?

"Justin Hoffman" <[e-mail]> wrote in message
news:dd**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"Tom" <no***@email.com> wrote in message
news:9R****************@newsread1.news.atl.earthli nk.net...

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.

Tom

That is correct, but the field 'StateID' may have a default value
(typically zero for a long integer) so even though you leave it blank,
zero is filled in and this value does not appear in the related table.
Check the design of the table.

[Tom]

I'm still stuck! I was just pointing out that I had previously deleted the
zero when I first designed the table. The query will become the record
source for a form to add/edit records in TblLocation. But before I do that I
need to solve this problem because it will be there when I design the form.

Tom
"Justin Hoffman" <[e-mail]> wrote in message
news:dd**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"Tom" <no***@email.com> wrote in message
news:56****************@newsread1.news.atl.earthli nk.net...

Thanks for your response, Justin!

I deleted the zero default value when I designed TblLocation so the
default value is blank. When I open the table and look at the new record
line, StateID is blank.

Tom

That's good, isn't it? I'm sure removing the default value of zero is a
useful thing to do anyway - afterall, it serves no purpose - unless you
have a valid state with id=0.
Has that solved your problem or are you still stuck? I am not quite sure
of the purpose of your query. To add new records to TblLocation? To edit
them?

"Justin Hoffman" <[e-mail]> wrote in message
news:dd**********@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...

"Tom" <no***@email.com> wrote in message
news:9R****************@newsread1.news.atl.earthli nk.net...
Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the
primary's table's field or be null? StateID is a foreign key.

Tom

That is correct, but the field 'StateID' may have a default value
(typically zero for a long integer) so even though you leave it blank,
zero is filled in and this value does not appear in the related table.
Check the design of the table.

[Larry Linson]

"Tom" wrote

I changed StateID in TblLocation to
StateInit and StateID in TblState to
StateInit. Made StateInit in TblState
the primary key. I joined StateInit in
both tables in the query. Still get the
same error message!

If by "Type 1" join, you mean an inner join, "only records with same
identification in both tables" -- there is no provision in that for "null",
there _must_ be records with the identical value in that field in both
tables for anything to display.

It's not fully clear to me what you are doing with your direct use of
queries, but I can suggest two things...

(1) try a join that only requires a record
in whichever table you are adding
records to (you'll have to use other
means to validate that the state initials
in the added record are valid)

(2) use a default value for the foreign key
field, for whichever state you'll use
most often

Larry Linson
Microsoft Access MVP

[Justin Hoffman]

"Tom" <no***@email.com> wrote in message
news:Tm****************@newsread1.news.atl.earthli nk.net...

I'm still stuck! I was just pointing out that I had previously deleted the
zero when I first designed the table. The query will become the record
source for a form to add/edit records in TblLocation. But before I do that
I need to solve this problem because it will be there when I design the
form.

Tom

The normal way to do this would be to have the form based on TblLocation
only (or a query based on this single location). In order to show the state
rather than just the id, use a combobox to show the state name.
It is not the only one, but I would try this first.

[Tom]

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.

Tom

"Larry Linson" <bo*****@localhost.not> wrote in message
news:hv9Je.6296$Y2.4648@trnddc09...

"Tom" wrote

I changed StateID in TblLocation to
StateInit and StateID in TblState to
StateInit. Made StateInit in TblState
the primary key. I joined StateInit in
both tables in the query. Still get the
same error message!

If by "Type 1" join, you mean an inner join, "only records with same
identification in both tables" -- there is no provision in that for
"null", there _must_ be records with the identical value in that field in
both tables for anything to display.

It's not fully clear to me what you are doing with your direct use of
queries, but I can suggest two things...

(1) try a join that only requires a record
in whichever table you are adding
records to (you'll have to use other
means to validate that the state initials
in the added record are valid)

(2) use a default value for the foreign key
field, for whichever state you'll use
most often

Larry Linson
Microsoft Access MVP

[Larry Linson]

I'm sorry, but I don't understand what you mean.

The "legality" of a Null value in a Foreign Key field means that the Record
can be written. Whether a Null is allowed, or not, in any field, is
determined by the Properties of that Field in the Table's definition. It is
not _necessarily_ so that every Field used as a Foreign Key can be Null. In
fact, unless you create a Relationship, Access will not _know_ that a Field
is to be used as a Foreign Key. FK is a matter of use, not of defining that
a field is an FK.

That "legality" has nothing to do with whether a Query using an Inner Join
(I like better the more descriptive term "Equi-Join") will return a Record.
They are two separate subjects.

Of course, the FK can be Null. And, you can get some results, even with a
Null FK, if you use one of the Outer Join types.

With an Inner Join there must be a matching key in the joined fields of both
tables _for a record to be returned_ by the Query.

And, I seem to recall that you implied by "Type 1" (not an official Access
term, but I assumed it applied to the first of the three possibilities shown
in the Join Type dialog) that you had use the Equ-Join.

Larry Linson
Microsoft Access MVP

"Tom" <no***@email.com> wrote in message
news:57****************@newsread2.news.atl.earthli nk.net...

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.

Tom

"Larry Linson" <bo*****@localhost.not> wrote in message
news:hv9Je.6296$Y2.4648@trnddc09...

"Tom" wrote

> I changed StateID in TblLocation to
> StateInit and StateID in TblState to
> StateInit. Made StateInit in TblState
> the primary key. I joined StateInit in
> both tables in the query. Still get the
> same error message!

If by "Type 1" join, you mean an inner join, "only records with same
identification in both tables" -- there is no provision in that for
"null", there _must_ be records with the identical value in that field in
both tables for anything to display.

It's not fully clear to me what you are doing with your direct use of
queries, but I can suggest two things...

(1) try a join that only requires a record
in whichever table you are adding
records to (you'll have to use other
means to validate that the state initials
in the added record are valid)

(2) use a default value for the foreign key
field, for whichever state you'll use
most often

Larry Linson
Microsoft Access MVP

[Tom]

I tried your suggestion in your first response and found that a right join
solved the problem.

Thanks,

Tom
"Larry Linson" <bo*****@localhost.not> wrote in message
news:mPfJe.3877$2j.3779@trnddc07...

I'm sorry, but I don't understand what you mean.

The "legality" of a Null value in a Foreign Key field means that the
Record can be written. Whether a Null is allowed, or not, in any field, is
determined by the Properties of that Field in the Table's definition. It
is not _necessarily_ so that every Field used as a Foreign Key can be
Null. In fact, unless you create a Relationship, Access will not _know_
that a Field is to be used as a Foreign Key. FK is a matter of use, not of
defining that a field is an FK.

That "legality" has nothing to do with whether a Query using an Inner Join
(I like better the more descriptive term "Equi-Join") will return a
Record. They are two separate subjects.

Of course, the FK can be Null. And, you can get some results, even with a
Null FK, if you use one of the Outer Join types.

With an Inner Join there must be a matching key in the joined fields of
both tables _for a record to be returned_ by the Query.

And, I seem to recall that you implied by "Type 1" (not an official Access
term, but I assumed it applied to the first of the three possibilities
shown in the Join Type dialog) that you had use the Equ-Join.

Larry Linson
Microsoft Access MVP

"Tom" <no***@email.com> wrote in message
news:57****************@newsread2.news.atl.earthli nk.net...

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.

Tom

"Larry Linson" <bo*****@localhost.not> wrote in message
news:hv9Je.6296$Y2.4648@trnddc09...

"Tom" wrote

> I changed StateID in TblLocation to
> StateInit and StateID in TblState to
> StateInit. Made StateInit in TblState
> the primary key. I joined StateInit in
> both tables in the query. Still get the
> same error message!

If by "Type 1" join, you mean an inner join, "only records with same
identification in both tables" -- there is no provision in that for
"null", there _must_ be records with the identical value in that field
in both tables for anything to display.

It's not fully clear to me what you are doing with your direct use of
queries, but I can suggest two things...

(1) try a join that only requires a record
in whichever table you are adding
records to (you'll have to use other
means to validate that the state initials
in the added record are valid)

(2) use a default value for the foreign key
field, for whichever state you'll use
most often

Larry Linson
Microsoft Access MVP

[Salad]

Tom wrote:

Thanks for responding!

I thought null values are allowed in foreign key fields. Doesn't Access
apply the rule that the foreign key must match a record in the primary's
table's field or be null? StateID is a foreign key.
You are using the wrong type of query. You are saying and attempting to
select all records from table1 that have a record in table2. You don't
add a state and then expect the two to be joined.

You are really making a lot of work on this for absolutely no purpose.

Someone mentioned using a combo box for entering the state. That seems
like a suitable solution since it's obvious you don't need a state to be
selected when a record is added.


Tom

"Larry Linson" <bo*****@localhost.not> wrote in message
news:hv9Je.6296$Y2.4648@trnddc09...

"Tom" wrote

I changed StateID in TblLocation to
StateInit and StateID in TblState to
StateInit. Made StateInit in TblState
the primary key. I joined StateInit in
both tables in the query. Still get the
same error message!

If by "Type 1" join, you mean an inner join, "only records with same
identification in both tables" -- there is no provision in that for
"null", there _must_ be records with the identical value in that field in
both tables for anything to display.

It's not fully clear to me what you are doing with your direct use of
queries, but I can suggest two things...

(1) try a join that only requires a record
in whichever table you are adding
records to (you'll have to use other
means to validate that the state initials
in the added record are valid)

(2) use a default value for the foreign key
field, for whichever state you'll use
most often

Larry Linson
Microsoft Access MVP