error 3341 message when trying to create new record

S

Steve

i have created a form based on the following query. There are two tables,
[Main table] and tblCpap linked by [Main table].ID and tblCpap.mainid in a
one to many relationship. When i attempt to add a new record i get an error
with the folllowing explanation in the help.

The current field must match the join key <name> in the table that serves as
the 'one' side of one-to-many relationship. (Error 3341)
You have tried to enter a value into the join field in the many-only table
of an outer join with no corresponding value in the join field of the "one"
side table.
The message includes the following instruction: Enter a record in the "one"
side table with the desired key value, and then make the entry with the
desired join field in the "many-only" table.

the sql of the query is:

SELECT [MAIN TABLE].ID, [MAIN TABLE].[LAST NAME], [MAIN TABLE].[FIRST NAME],
[MAIN TABLE].MI, [MAIN TABLE].[MSS ID], tblCpap.CpapId, tblCpap.MainId,
tblCpap.OrderDr, tblCpap.TreatDr, tblCpap.SleepLab, tblCpap.SleepLab2,
tblCpap.SetupDt, tblCpap.SetupMktNotifyDt, tblCpap.SetupNotifyBy,
tblCpap.SetupWhoNotify, tblCpap.InitFace2FaceDt, tblCpap.InitF2FMktNotifyDt,
tblCpap.InitF2FNotifyBy, tblCpap.InitF2FWhoNotify,
tblCpap.InitF2FDocsObtainedDt, tblCpap.InitF2FDocsNotifyDt,
tblCpap.InitF2FDocsNotifyBy, tblCpap.InitF2FDocsWhoNotify,
tblCpap.InitF2FAcceptable, tblCpap.InitF2FAcceptableNotifyDt,
tblCpap.InitF2FAcceptableNotifyBy, tblCpap.InitF2FAcceptableWhoNotify,
tblCpap.DiagStudyDt, tblCpap.DiagStudyNotifyDt, tblCpap.DiagStudyNotifyBy,
tblCpap.DiagStudyWhoNotify, tblCpap.TherapStudyDt,
tblCpap.TherapStudyNotifyDt, tblCpap.TherapStudyNotifyBy,
tblCpap.TherapStudyWhoNotify, tblCpap.DnLd1DueDt, tblCpap.DnLd1CompleteDt,
tblCpap.DnLd1Compliant, tblCpap.DnLd1NotifyDt, tblCpap.DnLd1NotifyBy,
tblCpap.DnLd1WhoNotify, tblCpap.DnLd2DueDt, tblCpap.DnLd2CompleteDt,
tblCpap.DnLd2Compliant, tblCpap.DnLd2NotifyDt, tblCpap.DnLd2NotifyBy,
tblCpap.DnLd2WhoNotify, tblCpap.DnLd3DueDt, tblCpap.DnLd3CompleteDt,
tblCpap.DnLd3Compliant, tblCpap.DnLd3NotifyDt, tblCpap.DnLd3NotifyBy,
tblCpap.DnLd3WhoNotify, tblCpap.DnLd4DueDt, tblCpap.DnLd4CompleteDt,
tblCpap.DnLd4Compliant, tblCpap.DnLd4NotifyDt, tblCpap.DnLd4NotifyBy,
tblCpap.DnLd4WhoNotify, tblCpap.[31-90DrVisitBeginDt],
tblCpap.[31-90DrVisitEndDt], tblCpap.[31-90DrVisitNotifyDt],
tblCpap.[31-90DrVisitNotifyBy], tblCpap.[31-90DrVisitWhoNotify],
tblCpap.NextDrAppt, tblCpap.[31-90F2FCompleteDt],
tblCpap.[31-90F2FCompNotifyDt], tblCpap.[31-90F2FCompNotifyBy],
tblCpap.[31-90F2FCompWhoNotify], tblCpap.[31-90F2FDocsObtainedDt],
tblCpap.[31-90F2FDocsObtainedNotifyDt],
tblCpap.[31-90F2FDocsObtainedNotifyBy],
tblCpap.[31-90F2FDocsObtainedWhoNotify], tblCpap.[31-90F2FAcceptable],
tblCpap.[31-90F2FAcceptableNotifyDt], tblCpap.[31-90F2FAcceptableNotifyBy],
tblCpap.[31-90F2FAcceptableWhoNotify], tblCpap.Comments
FROM [MAIN TABLE] INNER JOIN tblCpap ON [MAIN TABLE].ID = tblCpap.MainId;

Thanks for the help.
 
T

tina

well, it might work if you change the INNER JOIN to a LEFT JOIN in the SQL
statement. however, if one Maintable record may have many related tblCpap
records, suggest you use the standard setup for entering records in
one-to-many related tables: a mainform bound to table Maintable (*not*
bound to a multi-table query), and a subform bound to tblCpap (again, *not*
bound to a multi-table query). set the subform control's LinkMasterFields
and LinkChildFields properties as

LinkChildFields = mainid
LinkMasterFields = ID

when you enter a record in the subform, its' foreign key field mainid will
automatically be set to the value of ID in the mainform record - so the
subform record is related to the mainform record without any coding or extra
work on your part.

hth


Steve said:
i have created a form based on the following query. There are two tables,
[Main table] and tblCpap linked by [Main table].ID and tblCpap.mainid in a
one to many relationship. When i attempt to add a new record i get an error
with the folllowing explanation in the help.

The current field must match the join key <name> in the table that serves as
the 'one' side of one-to-many relationship. (Error 3341)
You have tried to enter a value into the join field in the many-only table
of an outer join with no corresponding value in the join field of the "one"
side table.
The message includes the following instruction: Enter a record in the "one"
side table with the desired key value, and then make the entry with the
desired join field in the "many-only" table.

the sql of the query is:

SELECT [MAIN TABLE].ID, [MAIN TABLE].[LAST NAME], [MAIN TABLE].[FIRST NAME],
[MAIN TABLE].MI, [MAIN TABLE].[MSS ID], tblCpap.CpapId, tblCpap.MainId,
tblCpap.OrderDr, tblCpap.TreatDr, tblCpap.SleepLab, tblCpap.SleepLab2,
tblCpap.SetupDt, tblCpap.SetupMktNotifyDt, tblCpap.SetupNotifyBy,
tblCpap.SetupWhoNotify, tblCpap.InitFace2FaceDt, tblCpap.InitF2FMktNotifyDt,
tblCpap.InitF2FNotifyBy, tblCpap.InitF2FWhoNotify,
tblCpap.InitF2FDocsObtainedDt, tblCpap.InitF2FDocsNotifyDt,
tblCpap.InitF2FDocsNotifyBy, tblCpap.InitF2FDocsWhoNotify,
tblCpap.InitF2FAcceptable, tblCpap.InitF2FAcceptableNotifyDt,
tblCpap.InitF2FAcceptableNotifyBy, tblCpap.InitF2FAcceptableWhoNotify,
tblCpap.DiagStudyDt, tblCpap.DiagStudyNotifyDt, tblCpap.DiagStudyNotifyBy,
tblCpap.DiagStudyWhoNotify, tblCpap.TherapStudyDt,
tblCpap.TherapStudyNotifyDt, tblCpap.TherapStudyNotifyBy,
tblCpap.TherapStudyWhoNotify, tblCpap.DnLd1DueDt, tblCpap.DnLd1CompleteDt,
tblCpap.DnLd1Compliant, tblCpap.DnLd1NotifyDt, tblCpap.DnLd1NotifyBy,
tblCpap.DnLd1WhoNotify, tblCpap.DnLd2DueDt, tblCpap.DnLd2CompleteDt,
tblCpap.DnLd2Compliant, tblCpap.DnLd2NotifyDt, tblCpap.DnLd2NotifyBy,
tblCpap.DnLd2WhoNotify, tblCpap.DnLd3DueDt, tblCpap.DnLd3CompleteDt,
tblCpap.DnLd3Compliant, tblCpap.DnLd3NotifyDt, tblCpap.DnLd3NotifyBy,
tblCpap.DnLd3WhoNotify, tblCpap.DnLd4DueDt, tblCpap.DnLd4CompleteDt,
tblCpap.DnLd4Compliant, tblCpap.DnLd4NotifyDt, tblCpap.DnLd4NotifyBy,
tblCpap.DnLd4WhoNotify, tblCpap.[31-90DrVisitBeginDt],
tblCpap.[31-90DrVisitEndDt], tblCpap.[31-90DrVisitNotifyDt],
tblCpap.[31-90DrVisitNotifyBy], tblCpap.[31-90DrVisitWhoNotify],
tblCpap.NextDrAppt, tblCpap.[31-90F2FCompleteDt],
tblCpap.[31-90F2FCompNotifyDt], tblCpap.[31-90F2FCompNotifyBy],
tblCpap.[31-90F2FCompWhoNotify], tblCpap.[31-90F2FDocsObtainedDt],
tblCpap.[31-90F2FDocsObtainedNotifyDt],
tblCpap.[31-90F2FDocsObtainedNotifyBy],
tblCpap.[31-90F2FDocsObtainedWhoNotify], tblCpap.[31-90F2FAcceptable],
tblCpap.[31-90F2FAcceptableNotifyDt], tblCpap.[31-90F2FAcceptableNotifyBy],
tblCpap.[31-90F2FAcceptableWhoNotify], tblCpap.Comments
FROM [MAIN TABLE] INNER JOIN tblCpap ON [MAIN TABLE].ID = tblCpap.MainId;

Thanks for the help.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto create of new record when accessing subform 3
how to create new record automatically 2
error message when adding a record in a subform 1
help! form is trying to update the wrong table 4

Top