The Microsoft Access database engine cannot find a record in the table 'tblPermitee2'

[smcdaniel]

Hi.



I am a beginner Access user and I have created a form including subforms and when I fill out the form I get the message The Microsoft Access database engine cannot find a record in the table 'lkpPermitee2' with key matching field(s) ''. I get the error when I try to enter data into one of the subforms on the form if I have not entered data into the text field for Permitee2.

My form is intended to be a Permitee Setup form. The form was based on a query and includes three subforms.

The table "lkpPermitee2" is a lookup table where a second permitee, if one exists for that project, is stored. A Permitee2 will not exist in all instances and the form needs to allow for a Permitee to be entered without a Permitee2.

I cannot attach my database because it exceeds the limit, so instead I will try to describe it:
Lookup tables - lkpBasin (pk BasinID), lkpCombo (no pk), lkpMonitoringFrequency (pk MonitoringFrequencyID), lkpPermit (pk PermitID), lkpPermitee (pk PermiteeID), lkpPermitee2 (pk Permitee2ID), lkpProject (pk ProjectID), lkpReport (pk ReportID), lkpReportingFrequency (pk ReportingFrequencyID), lkpReportLocation (pk ReportLocationID).
Tables - tblCompleteBasin (pk CompleteBasinID fk CompletePermiteeID), tblCompletePermitee (pk CompletePermiteeID fk MonitoringPlanID), tblCompleteReporting (pk CompleteReportingID fk CompletePermiteeID), tblMonitoringPlan (pk MonitoringPlanID), tblPermit (pk PermitID fk CompletePermiteeID).
Queries - qryPermitee (tblCompletePermitee, lkpPermitee, lkpProject. pk CompletePermiteeID fk PermiteeID, ProjectID), qryPermitee2 (qryPermitee, lkpPermitee2. pk CompletePermiteeID fk Permitee2ID), qryCompletePermitee (tblMonitoringPlan, qryPermitee2. pk MonitoringPlanID fk MonitoringPlanID)
Form - frmPermiteeSetup (using the form wizard and qryCompletePermitee and also dragging in three subforms sbfmBasin, sbfmPermit, sbfmReport)

Can someone please provide an idea on how I can remedy this?

[June7]

Did you run Compact & Repair and zip db? Up to 2mb zip allowed. Extract the objects involved in issue to another db and provide that. Otherwise, could upload to a fileshare site such as Box.com and post link to the file.

Is the Permittee2 field set as Required in the table?

Issue might be in the JOIN TYPE of the queries. INNER JOIN will require matching records in both joined tables.

I will have to review db to help further.

[Missinglinq]

You're trying to enter a Child Record (in a Subform) before you've entered a Parent Record (in the Main Form) and you cannot do that! How can you have a 'Child' before his 'Parent' exists?

Linq ;0)>

[smcdaniel]

Here is the database: MP2.zip.

I am a pretty new user to access so I apologize in advance if I ask many questions.

[June7]

Cannot use one form to enter data into two datasets.

The issue is the query that frmPermitteeSetup has for RecordSource. It has INNER JOIN of a query and a table. That query has a query with INNER JOIN to lookup table as datasource. Never use INNER JOIN to lookup tables. Why query of query of query as RecordSource? RecordSource for frmPermiteeSetup should just be tblCompletePermittee. Use comboboxes to select values from the lookup tables (lkpPermitee and lkpPermitee2 - should be one table, lkpProject, etc).

If purpose of form is to edit/enter permittee records, use different form for entering monitoring plans. Then a monitoring plan record would be selected from a combobox on frmPermtteeSetup.

What is relationship of tblCompletePermittee to tblMonitoringPlan? Can each permittee record have multiple plans? Can each plan be related to multiple permitees? Current table structure allows for each tblCompletePermittee record to have a single plan but each plan can be related to multiple permittees (many-to-one relationship). If the relationship is actually one-to-one then just combine the two tables into one.