1 Form - 3 Tables

[sofeeyah]

I have three tables all with a common field linking them relationally and i need to design a form that incorporates fields from all three forms how do I do that? tried the wizard and it did not allow me to have a sub form and access did something really weird, the form just appeared in form view with only the title, if I want to see the fields I have to go to design view.

The tables are as follows:

Address
Invoice
Contract

The contract number is the field that ties the three tables.

 

[MacroAlan]

Try building your subForm before adding it to the main form. After you drag the size of the subform, it will ask if you have a form already and you can specify the subForm AND the tying criteria.


Alan

 

[Dontremb]

Are all these tables in a one to one relationship?

I don't know your data, but it looks like that COULD be the case.

If so, you can just make a query linking all the fields you want together, then make the form from the query, instead of directly from the table.

 

[sofeeyah]

I have one table "Contracts" that has a one-many relationship with a table, "Address" and it also has a one-many relationship with another table, "Invoice".

Since my post, I built a form from scratch. Started with a form based on the table "Contracts" and inserted two subforms, based on the tables "Address" and "Invoice", however when I actually try to enter data, the cursor will not tab to the second subform and goes to the next record in the first subform.

Looking for a better solution or how to address the tabbing issue. Ultimately, after completing the fields in the first subform, I would like the cursor to go to the first field in the second subform and then after completing the final field in the second subform, I would like the cursor to then go back to the first field in the main form to create a new record.