Jet database engine cannot find......

[dskillingstad]

I'm building a tracking system and I'm having some problems. I thought
this was relatively easy, but....

I have the following tables and fields (abbreviated):
tblPermitMain
PermitID - pk
ApplicantID (from tblApplicant)
PropertyID (from tblProperty)
PermitNumber
PermitApprovalDate
PermitResidential
PermitCommercial

tblApplicant
ApplicantID - pk
ApplicantFname
ApplicantLname
ApplicantCity
ApplicantState

tblProperty
PropertyID - pk
ParcelNumber
PropertyAddress

The tables above are related by the ID fiels with one to many
relationships. I use a query which includes all of the fields above
and have made a form based on this query.

When I create a new record in the form and I only input information
contained in PermitMain table and the Applicant table, I recieve an
error message when I close the form, or create a new record. The error
message is

"The microsoft Jet database engine cannot find a record in the table
tblProperty with key matching field(s) tblPermitMain.propertyID."

Hopefully this is just a minor glitch in my table setup
(relationships?). Any help on this would be appreciated.

I'm an obvious newbie to access so please excuse the ignorance.

Thanks!!

[pietlinden]

ds***********@gmail.com wrote:

I'm building a tracking system and I'm having some problems. I thought this was relatively easy, but....

I have the following tables and fields (abbreviated):
tblPermitMain
PermitID - pk
ApplicantID (from tblApplicant)
PropertyID (from tblProperty)
PermitNumber
PermitApprovalDate
PermitResidential
PermitCommercial

tblApplicant
ApplicantID - pk
ApplicantFname
ApplicantLname
ApplicantCity
ApplicantState

tblProperty
PropertyID - pk
ParcelNumber
PropertyAddress

The tables above are related by the ID fiels with one to many
relationships. I use a query which includes all of the fields above
and have made a form based on this query.

When I create a new record in the form and I only input information
contained in PermitMain table and the Applicant table, I recieve an
error message when I close the form, or create a new record. The error message is

"The microsoft Jet database engine cannot find a record in the table
tblProperty with key matching field(s) tblPermitMain.propertyID."

Hopefully this is just a minor glitch in my table setup
(relationships?). Any help on this would be appreciated.

I'm an obvious newbie to access so please excuse the ignorance.

Thanks!!

If you're entering data into the PermitMain Table, then the data you
want to enter for applicant and property must already exist before you
can enter data for that. That's what the message means. JET is trying
to enforce referential integrity (which says there must be a record in
one of your tables with the same value in the Primary key field as
there is in the join field in that table....) Oh, the joys of set
math. You may need to force a save of your main table record first.
then you can add children to it.

[Tom van Stiphout]

On 26 Mar 2005 21:12:27 -0800, ds***********@gmail.com wrote:

Access has a cool feature that was specifically designed to enter 1:M
information: the form/subform combination.
Take a look at the Northwind sample application, the Order form.
Observe master information on the main form, and detail information in
the subform.
At runtime, the user enters data in the main form first, and when he
sets focus to the subform that information is automatically saved. The
PK information is available, and entered in a (typically hidden) field
in the subform (as given by subform.linkmasterfields and
subform.linkchildfields).
Study this design pattern, and you will find MANY uses for it.

-Tom.

I'm building a tracking system and I'm having some problems. I thought
this was relatively easy, but....

I have the following tables and fields (abbreviated):
tblPermitMain
PermitID - pk
ApplicantID (from tblApplicant)
PropertyID (from tblProperty)
PermitNumber
PermitApprovalDate
PermitResidential
PermitCommercial

tblApplicant
ApplicantID - pk
ApplicantFname
ApplicantLname
ApplicantCity
ApplicantState

tblProperty
PropertyID - pk
ParcelNumber
PropertyAddress

The tables above are related by the ID fiels with one to many
relationships. I use a query which includes all of the fields above
and have made a form based on this query.

When I create a new record in the form and I only input information
contained in PermitMain table and the Applicant table, I recieve an
error message when I close the form, or create a new record. The error
message is

"The microsoft Jet database engine cannot find a record in the table
tblProperty with key matching field(s) tblPermitMain.propertyID."

Hopefully this is just a minor glitch in my table setup
(relationships?). Any help on this would be appreciated.

I'm an obvious newbie to access so please excuse the ignorance.

Thanks!!