Form generates.. Microsoft Jet database engine cannot find a record in the table error

[Fred Boer]

Hello:



Well, I've had a problem that had me screaming silently at the walls over
the last day or so. I know how it is caused; I can reproduce the problem. I
don't know *why* it happens. I think this is a case of "Doctor, it hurts
when I put my arm this way". with the expected response, but I'd be curious
if someone could explain the *why* of this problem.



Warning: This is fairly involved, and involves what will be quickly obvious
is a rather bizarre way of creating a form with a combobox to do a lookup to
a lookup table. I'm not even sure *why* I did things this way. (I'm not
likely to do it again!) However, what interests me is that *if* you did
this, you end up with something that *looks* completely correct, but still
causes an unexpected error.



Ok, take a deep breath! <g>



It has to do with the use of the wizard in creating forms.



Imagine the following:



tblName (NameID;Name;CityID - lookup to tblCity)

tblCity (CityID;City)



Create a form using wizard. Include tblName.NameID; tblName.Name. Then,
manually add a combobox to perform the lookup to tblCity, bound to
tblName.CityID. Create a new record, leaving the combobox empty. Access
saves record without protest.



Now, if you do it this way (I *know* this is a bit odd.):



Create a form using the wizard. Include tblName.NameID;tblName.Name;
tblCity.City.

The new form will have a textbox bound to tblCity.City. Change this to a
combobox. Change all of the appropriate properties so that the combobox does
a correct lookup to tblCity.CityID, and is bound correctly to
tblName.CityID. Adjust the recordsource of the form so that it contains only
the appropriate fields.



Now, even though everything *looks* perfectly correct - Access will not let
you save a record with the combobox empty. It throws the following error
message:



The Microsoft Jet database engine cannot find a record in the table
'tblCity' with key matching field(s) 'PlaceID'.



Ok, ok, it is a completely wonky way to create a form. But if I did it,
someone else might - and it isn't easy to see what the problem is. I
*imagine* that Access has created hidden relationships that prevent the
record from being saved. But I don't know..



Cheers!

Fred Boer

 

[Dirk Goldgar]

[...]

Now, if you do it this way (I *know* this is a bit odd.):

Create a form using the wizard. Include tblName.NameID;tblName.Name;
tblCity.City.

Presumably the wizard created a recordsource query that joined the two
tables.

The new form will have a textbox bound to tblCity.City. Change this
to a combobox. Change all of the appropriate properties so that the
combobox does a correct lookup to tblCity.CityID, and is bound
correctly to tblName.CityID. Adjust the recordsource of the form so
that it contains only the appropriate fields.

But did you remove tblCity and simplify the query?

Now, even though everything *looks* perfectly correct - Access will
not let you save a record with the combobox empty. It throws the
following error message:

The Microsoft Jet database engine cannot find a record in the table
'tblCity' with key matching field(s) 'PlaceID'.

PlaceID? What's PlaceID?

Ok, ok, it is a completely wonky way to create a form. But if I did
it, someone else might - and it isn't easy to see what the problem
is. I *imagine* that Access has created hidden relationships that
prevent the record from being saved. But I don't know..

Would you mind posting the SQL of the recordsource?

 

[Fred Boer]

Hi Dirk:

Recordsource for combobox:

SELECT tblCity.PlaceID, tblCity.Place
FROM tblCity
ORDER BY tblCity.Place;

Recordsource for form:

SELECT tblName.NameID, tblName.Name, tblName.PlaceID
FROM tblCity INNER JOIN tblName ON tblCity.PlaceID = tblName.PlaceID;

Perhaps I am completely confused? The SQL for the recordsource of the form
says "From tblCity", but in the design grid, all of the fields are listed as
being from tblName?

Cheers!
Fred

 

[Dirk Goldgar]

Hi Dirk:

Recordsource for combobox:

SELECT tblCity.PlaceID, tblCity.Place
FROM tblCity
ORDER BY tblCity.Place;

Recordsource for form:

SELECT tblName.NameID, tblName.Name, tblName.PlaceID
FROM tblCity INNER JOIN tblName ON tblCity.PlaceID = tblName.PlaceID;

Okay, so you said CityID before, when you meant PlaceID. I have to
watch you like a hawk, don't I?

Perhaps I am completely confused? The SQL for the recordsource of the
form says "From tblCity", but in the design grid, all of the fields
are listed as being from tblName?

That's a perfectly legitimate query, but not what you want in this case.
What the SQL says is, "Join tblCity and tblName together by matching on
the PlaceID field they have in common; then, from the joined records,
select just the NameID, Name, and PlaceID fields from tblName." This is
a technique you might use, for example, to limit a set of tblName
records to just those records that have a match in tblCity, excluding
any that don't.

The reason you're getting an error with this recordsource when you leave
the combo box blank (so the PlaceID field is Null) and try to save the
record, is that the record being updated question isn't just the record
from tblNames. It's the *joined* record, which implies that there must
be a record in tblCity that has a matching PlaceID. It doesn't matter
that you haven't included any of the fields from that tblCity record;
it's still required as a participant in the join. Since you've left the
PlaceID blank, there is no such record, and therefore the form's
"joined" record can't be updated.

 

[Fred Boer]

Dear Dirk:

Okay, so you said CityID before, when you meant PlaceID. I have to
watch you like a hawk, don't I?

Luckily you are incredibly sharp-eyed! My apologies; I created several
versions of this database in my struggles... I got confused.

Thanks for the clear and helpful explanation, Dirk. As always, it seems
simple after listening to you!

Cheers!
Fred