Automatically filling a form from other tables or fields?

[Sam Zuhbi]

Hi,

I am not sure if anyone has asked such a question but If someone has can I be directed to the thread and if it hasn't can someone help me please as I need advice.

Just before I start I am a novice at Access/VBA so Please take it easy on me.

Here is what I am trying to do. I have attached an Image of the relationship of my databases but I need to make sure I am doing the right thing

I have 2 tables
tblWorkInProgre ss with Primary Key "Job Number"

tblWorkAndSurve y witn Primary Key "Survey Number"

These tables share common information such as
Job Number
Aircraft Registration
Customer Name

The relationship should be that only one Survey can be Created to each Job.

I am trying to create a form for tblWorkAndSurve y that when it is loaded it will retrieve the information related to each job as above. for example if I wanted to create a a Survey for Job number 12 it needs to fill in the Customer Details and AC Registrations along with the Job Number.

I have used the Default Value in form design but I am not sure if this is the correct way of doing it or do I need to create A Query and create a form for that Query? Also I have read about Autolookup and it only works with one to many relationship, I need to maintain a 1 to 1 relationship as I should not be able to create multiple worksheets for 1 job. I hope that explains my delima I must have pulled every hair in my skalp and chewed every nail to figure this one. <-- I am not very Smart.

Can someone help me with this if this is a too long a question I will close the thread and start again.

Thank you for patients and understanding
Sam.

[Mariostg]

If I understand, looks like you have a one to one relationship between between your two tables. This is fairly rare unless you want to hide some information in one of the table.
Also you seem to duplicate information for nothing by looking at your diagram. I refer to fields Aircraft Registration and Customer. These soould go in only one of the two tables because they are linked with a common field (Job number).

Make sure you take the time to really lay down properly your design because when you start building forms and reports it is a nightmare if you need to modify the structure of your database.