First, there are only a few times that you should need one-to-one tables.
Five of them is definitely unusual. It is usually better to include the
fields in a single table. Next, to get around the error, I've seen a couple
of options. 1) (not 100% sure about this one, but it shouldn't hurt
anything) Enforce referential integrity between the one-to-one tables and
set it to Cascade Updates. 2) Have a default value set for one of the fields
(and for any Required fields) in each of the secondary tables to create a
record in them for each record you create in the main table.

--
Wayne Morgan
MS Access MVP


"Don" wrote in message
...
I have 5 tables all on a 1 to 1 with the link being the PK on all plus 2
additional tables 1 to many with the link being PK to FK_Id on both.
I then created a qryMain to hold all of the fields and this is used as the
source for the form, the 2 1 to many tables are sub forms on the main
form.

The five 1 to 1 tables are all 1 record with no repetition and are as
normalised as I can make them, an alternative would be one table with 90
plus fields and two 1 to many.

Problem: Using this as described, either I can enter no data at all or by
removing 1 of the 1 to many tables enter data in part and then it
complains that a record cannot be found in tblContacts which is one of the
1 to many tables. I am missing something fundamental here, but surely the
record isnt created until I save the form by going to the next record.
I need to get this sorted quickly as it is stopping me from completing the
work by the end of this week.
Any help in the design of the relationships would be gratefully recieved.

A simplified schema is below:

tblClient:
ID_Client PK Autonumber linked 1 to 1 to tblProgress PK Number

tblClient:
ID_Client PK Autonumber linked 1 to 1 to tblProject PK Number
and so on for two additional tables

tblClient:
ID_Client PK Autonumber linked 1 to many to tblContacts FK_ID_Client

tblClient PK Autonumber linked 1 to many to tblSupplier FK_ID_Client

The relationships have been created in the main relationship window and
then recreated in the qryMain used to supply the form.
I have ensured that no records exist in any tables (causing violation).
The sql for the qryMain is below.

SELECT tblClient.ID_Client, tblClient.txt_Cnc, tblClient.txt_Company,
tblClient.txt_Acc_Name, tblClient.txt_Acc_Num, tblClient.txt_SortCode,
tblClient.opt_Stage_Reject, tblClient.Notes, tblClient.txt_Assessor,
tblClient.txt_Country, tblClient.dte_Loan_Agg_Sent,
tblClient.dte_DD_Debit_Rec, tblProgress.dte_First_Contact,
tblProgress.dte_Elig_Complete, tblProgress.dte_Cat,
tblProgress.dte_Reject, tblProgress.dte_Reject_Letter,
tblProgress.dte_Pat_Sent, tblProgress.dte_Pat_Rec,
tblProgress.dte_quote_Rec, tblProgress.dte_Cons_Appoint,
tblProgress.dte_Cons_Verify, tblProgress.dte_Comp_Acc_Ordered,
tblProgress.int_Number_Accounts, tblProgress.dte_Comp_Acc_Rec,
tblProgress.txt_Cashlflow_req, tblProgress.dte_Credit_Check_Start,
tblProgress.int_Delay_Period, tblProgress.dte_Int_Credit_Comp,
tblProgress.dte_Ext_Credit_Check, tblProgress.dte_Ext_Credit_Check_Rec,
tblProgress.dte_Rec_Sent_Ct, tblProgress.dte_Appro_Rec,
tblProgress.txt_Approver, tblProgress.txt_Pcg_Required,
tblProgress.dte_Client_Notify, tblProgress.dte_Loan_Agg_Rec,
tblProgress.dte_ProForma, tblProgress.dte_Pcg_Rec,
tblProgress.dte_Deminimus_Rec, tblProgress.dte_DD_Debit_Bank,
tblProgress.bool_Bank_Object, tblProgress.dte_Advance_Notice,
tblProgress.dte_AllDocs_Received, tblProject.curr_Request_Loan,
tblProject.curr_Proj_Cost, tblProject.txt_Consultant,
tblProject.opt_Reccomend, tblProject.curr_Ann_Energy_Saving,
tblProject.lng_Co2Tonnes, tblProject.num_Score,
tblValidate.txt_Employee_Num, tblValidate.num_Trading_history,
tblValidate.txt_Status, tblValidate.bool_Turnover,
tblValidate.bool_Assets, tblValidate.bool_Non_Sme,
tblValidate.bool_Clear_Idea, tblValidate.bool_Exact_Project,
tblValidate.bool_Project_Cat, tblValidate.bool_Tel_Advice,
tblValidate.bool_Supp_Contacted, tblValidate.bool_Supp_Chosen,
tblValidate.bool_No_Assist, tblValidate.opt_Project_Cat,
tblValidate.txt_Category, tblValidate.dte_AnticipateAppDate,
tblValidate.txt_Market_Code, tblValidate.int_ClientCategory,
tblValidate.curr_Est_Energy_Saving, tblValidate.txt_Origin_App,
tblValidate.txt_App_Source, tblValidate.txt_Bus_Sector,
tblValidate.opt_Org_Type, tblValidate.curr_Est_Project_Cost,
tblValidate.curr_Ave_Ann_Energy, tblLoan.curr_Loan_Amount,
tblLoan.int_Term, tblLoan.int_Installment, tblLoan.dte_First_Inv_Rec,
tblLoan.dte_Pay_First_Inv, tblLoan.int_Inv_Rec, tblLoan.int_Payment_Made,
tblLoan.dte_Repay_Sched, tblContacts.Fk_ID_Client,
tblContacts.txt_Contact, tblContacts.txt_Position,
tblContacts.txt_Address1, tblContacts.txt_Address2,
tblContacts.txt_Address3, tblContacts.txt_Town, tblContacts.txt_County,
tblContacts.txt_Post_Code, tblContacts.txt_Phone, tblContacts.txt_Fax,
tblContacts.txt_Email, tblValidate.ID_Validate, tblLoan.ID_Loan,
tblContacts.Id_Contacts, tblProgress.ID_Progress, tblProject.ID_Project,
tblSupplierChosen.Supplier
FROM (((((tblClient LEFT JOIN tblContacts ON tblClient.ID_Client =
tblContacts.Fk_ID_Client) LEFT JOIN tblLoan ON tblClient.ID_Client =
tblLoan.ID_Loan) LEFT JOIN tblProgress ON tblClient.ID_Client =
tblProgress.ID_Progress) LEFT JOIN tblProject ON tblClient.ID_Client =
tblProject.ID_Project) LEFT JOIN tblValidate ON tblClient.ID_Client =
tblValidate.ID_Validate) LEFT JOIN tblSupplierChosen ON
tblClient.ID_Client = tblSupplierChosen.Fk_ID_Client;


Many thanks for taking the trouble to wade through this problem so far.

Don