Entering Data into Related Tables Using a Form

[toppock]

Hi All,

I have a form within my database in which the user will enter data which will go into 2 separate tables. These 2 tables (Job and Client) are related. At the moment I have a subform in which the user enters Job information, and the main form where the user will enter client information.

The problem being is that the 2 sets of data do not associate themselves with each other, despite being related (The Client will be related to a job number. A client can have many jobs but a job can only have one client etc). It has to be done manually in the table which is not ideal as the DB will be split and rolled out to users via Access Runtime. I have been working on this DB for a while now and the problem is most likely right in front of me but I cannot see it!

Any help will be much appreciated. I have basic knowledge of SQL and VBA so these can be incorporated into the solution if required.

Thanks in advance!!

 

[toppock]

Is that supposed to help or...?

 

[pr2-eugin]

I apologize toppock, terrysoper is a SPAM user, he/she will be banned from the site soon. So please disregard the post terrysoper made.

 

[toppock]

No problem, thank you pr2-eugin for clarifying!

 

[pr2-eugin]

Okay, now coming to your problem. Could you show the table structure, how the two tables are related?

Normally your table shoule look like.

tblClients
clientID - PK
clientName
clientAddress
'...etc.

tblJobs
jobID - PK
FK_clientID - FK
jobName
jobDuration
'...etc.

So based on the above structure you will have One client can have Many Jobs.. If this is not the case, change it as such and you will be able to link the Master and Child fields for the Main and Sub Form, thus gathering your information automatically..