Dreaded "Microsoft Jet database engine" error

  • Thread starter Thread starter Richard Coutts
  • Start date Start date
R

Richard Coutts

I have a Continuous form with several combo boxes, one of which is
dependant on the contents of another. Everything works great except
when I only try to leave a combo box empty. The typical scenario is I
start to fill in fields of the record when I realize I need some info
from another record or form. When, whenever I click anywhere outside
of the record, I get the error

"Microsoft Jet database engine cannot find a record in the table
'tblSchSubcats' with key matching field(s) 'Item'."

A really annoying feature of this error is it won't let me do
*anything* until I finish filling in the combo boxes -- even changing
to Design View. I'm finding this error especially hard to debug
because it's not giving me much indication as to where the error's
coming from, which query, etc. So, I'm a bit at a loss as to how to
proceed. Any pointers would be much appreciated.

Thanks,
Rich
 
This is a referential integrity error. It is telling you that the value
that you entered into a control on your form (Item) does not have a matching
value in the related table (tblSchSubcats). Now you will respond that you
HAVEN'T added anything to that field--that's the problem.

Ah, but you have!

Access has an annoying habit (at least to me it's annoying) of adding a
default value of 0 (zero) to numeric fields. This is fine except when you
are storing the foreign key to another table (as with a combobox to values
in a related table). By putting a default value of zero in the field, if
you DON'T select a value in the combo, it enters zero automatically.
However, your reference table DOES NOT have a value of zero in it. (If the
field you are referencing is an autonumber, you *cannot* have a zero value.)
This has the effect of making the combobox "Required" and you have to fill
the combo or cancel the record before you can do anything else.

The solution is to simply remove the default value for that (those) field(s)
in the main table that are foreign keys to related tables.
 
Back
Top