Linked Tables not cyncing when Data Entered

[Robert M]

I have three tables.

tblClient with Client_ID as the Primary Key field no duplicates
tblAddress with Add_ID as the Primary Key Field no duplicates
tblData with Data_ID as the link field

All above fields contain the Client Number. (listed as text due to some Client Numbers having letters)

At present I have Client_ID linked one to Many with Data_ID
and Add_ID linked to Data_ID as one to many also.

I am unable to enter new clients due to Add_ID and Data_ID containing null values.

My Question is, how do I get my Data_ID and Add_ID to accept the new Client_ID number that I am entering.

I have tried tblClient Client_ID linked to both tblAddress Add_ID and tblData Data_ID with the same results.

I hope you are able to understand what I have done and what my problem is.

Thank you for your time and patience with this project.

Robert M

 

[jzwp22]

...At present I have Client_ID linked one to Many with Data_ID
and Add_ID linked to Data_ID as one to many also.

All above fields contain the Client Number

Based on the above this is how I see your structure

tblClient
-Client_ID primary key

tblAddress
-Add_ID primary key
-Client_ID foreign key to tblClient

tblData
-Data_ID
-Add_ID foreign key to tblAddress
-Client_ID foreign key to tblClient

Is the above how you have your tables structured? If so, it is wrong. You should not have the Client_ID in tblData. tblData is already tied to the client via the address table.

This structure says that client have many addresses and each address has many pieces of data tied to it.

 

[Robert M]

My appologies... What I have is a single client with many data
Single Client with many addresses
I've tried linking it by Client_ID with both Data_ID and Add_ID but with the same inability to add new clients.

I am trying to get the Client_ID data to automatically be added to the Data_ID and Add_ID so that they tables remain in cync.

Hope that helps.

 

[jzwp22]

Then your tables should look like this

tblClient
-Client_ID primary key

tblAddress
-Add_ID primary key
-Client_ID foreign key to tblClient

tblData
-Data_ID primary key
-Client_ID foreign key to tblClient

The client_ID fields in tblData and tblAddress are foreign keys and the value in those fields must match the respective client ID of the client table. The primary keys of the the address and data table (Add_ID and Data_ID respectively) have nothing to do with the clientID--they are separate fields and need not be in synch with the client ID nor with each other.

Make sure to set up the relationships in the relationship window. Then you will need to create a form based on the tblClient; save that form. Then create a form based on tblAddress and another based on tblData. Save both of these forms. Open up the client form in design view and drag the address and data forms into the detail section of the client form. Access should automatically link the two subforms to the main form via the clientID in the respective tables based on the relationships you established in the relationship window.