Hello. I have a database Ive been asked to revamp which has an input page for data from 3 tables on it.
These 3 tables are all linked over client id. One is Client , one is Referral and one is Session.

Originally the relationships were all 1 to 1 , now they are
Client to Session = 1 to many , Client to Referral = 1 to many.

No direct relationship between Referral and Session.

On the data entry form , Session is a subform linked over client id ( although I have just noticed that the Session client id is Client id and the referral is client id ( Capital C as against a small c , does that make a difference ?) but all the other fields are from the other two tables via a SELECT query for a record source

At present when the client details are entered , a client id is
created and entered into the client.client id control;
this then comes up automatically
on the Session subform , but when I try and enter more details on theSession subform I get the error message ,
the jet database engine cannot find a record in the table Referral with key matching field(s)
'client id'.

Now , when I add the control for Referral.client id into the Select query and then add it to the form ,
I can no longer enter anything into the client.client id control.Nothing allows itself to be typed in.
Im going around in circles. and I dont know why.

Can anyone help, please ?

tblClients
ClientID (PK and auto)
LastName
FirstName

tblSessions
SessionID (PK and auto)
ClientID (FK to tlbClients)
...other necessary fields that relate to the session..

tblReferrals
ReferralsID (PK and auto)
ClientID (FK to tblClients)
...other necessary fields that relate to the Referral...

The Required field should not be set in any of the tables. With the simple setup above all should work, but it sounds like things got a bit mixed up early on so you may have to do a bit of digging to find out what is going on...

By the way, if you create your Referrals and Sessions forms and add them to the Clients form, using the Wizard, you should be good to go, as Access will set the links for you as there is no need for you to enter ClientID anywhere at any time...

hth,
Jack

Thanks for the quick reply. The client ids are already set as text and I have 4,712 of them, roughly in the format HA2345 created by the original designer.I have to keep these . New ones have to be made.
Ive already created a new Referralid and a Sessionid as pks so if I create a new client id in the same mould , PK and auto and hide it on the main form , they can still create their own id cant they ? I can show them how to refernceit later if necessary.
Is that a way forward ?
Thanks

Your client table should look like:

tblClients
ClientID (PK and autonumber)
ClientNumber (Text)<--This is the HA2345 number
LastName
FirstName

ClientNumber should not be in any of the other tables, only ClientID.

You have a few choices, as I see it. Start with this new information and create your forms, etc. All new data entry will be with the new setup. Another choice is to add the autonumber field to the current tblClient and as FK's in the other tables and then write code to match up existing ClientNumbers. Or you can just stick with what you have and find out why you can't add new data...

Not an easy thing to change horses in mid stream...

Jack

I understand Jack. Its a bit like trying to fix a house that is structurally unsound without knocking it down and building it again. However , you have definitely given me a way forward. It is now midnight where i live , so Ill give it a go tomorrow morning. Many thanks.

You are most welcome!

I agree with your analogy as the structure was not sound to begin with so a rework is the most logical as well as the most expensive way to fix it.

Hang in there and with some mind-numbing work you should be back in business by Friday...

Jack