Help: Main Form linked to Multiple Tables

[bendy]

Hi
As I am fairly new to Access DB, I would be grateful for some help on an access DB that I created. The database function reasonably well and in the original version I had subforms attached to main forms to complete joins. The problem is that I seem to have too many individual forms which would complicates matters for data entry and you would have to input data in a certain order.
I would like to have a main form where almost everything is inputted but I don’t fully understand the concept of having a main form which is linked to several tables. Is this possible and how is it achieved. There are two scenarios. Thanks in advance for any advice. I have attached a copy of the DB.




1)A Vehicle Stop is Conducted (Vehicle Stop Table), Employee in Vehicle is imputed (Employee Table), Vehicle Detail inputted (Vehicle Table), Employee in Vehicle is imputed (Employee Table), Vehicle Detail inputted (Vehicle Table), Driver Detail Inputted (Driver Table). The Driver, Vehicle and Employee might already be in the system, having being encountered before.

2)An Enforcement Visit is made to either a Company or LabUs (Enforcement Visit Table). Company (Company Table) and LabUs (LabUs Table) inputted. Employee in Vehicle is imputed (Employee Table), Vehicle Detail inputted (Vehicle Table), Driver Detail Inputted (Driver Table). The Company, LabUs, Driver, Vehicle and Employee might already be in the system, having being encountered before.

[llkhoutx]

Building a query to display all the field you specify on one form will create a world of grief for you in that you'll probably end up with a nonupdatable recordset, that is, new records cannot be ctreaed nor will existing records be updatable.

For starters:
1. Create relationships for among all you tables with primary and foreign keys.
2. Your main form should have your primary table as it's recordsource.
3. Other tables should be one or more related subforms on the main form.
4. One or more subsubforms, a (subsub) form related to a subform, may be applicable.

Note that forms may be relationally nested 3 deep easily. Any deeper nesting required added code.

[bendy]

Thanks for your reply. I have created relationship between tables. i regard Enforcement Visit as my main table but information can be gained and imputted to other tables such as Employee, Vehicle, Labus and and Company without making an Enforcement Visit.

Enforcement visit has a one to many relationship with the four tables through joins. The four tables all have relationship with other tables. I believe you can only successfully create a subform with only one of the tables.

Have i made a mistake with the relationship? Please have a look at my attached Relationship Diagram. I have created the database which works but because of the design will quickly cause problem with data input. The aim is to create a form which can input, update and edit related data.

I have spent so many hours looking at this but i just cannot get my head around this so any help is gratefully received

[llkhoutx]

You say

Enforcement visit has a one to many relationship with the four tables  through joins. The four tables all have relationship with other tables. I  believe you can only successfully create a subform with only one of the  tables. 

Your relationships snapshot is a bunch of jibberiish to me. I don't have time to create and redraw all the relationships.

You can creaste a subform with multiple related tables in a query as a subform recordsource - without proper relationships the suform may be non-updatable. A form can have multiple subforms.

Can you post your database tables with data?