Accommodating several one to many relationships in a single form.

[Jerry Maiapu]

This is simple question but I know if solved could greatly assist many data entry clerk today, in the way they do data entry.

I have about 13 tables and are married to each other with one to many relationships.
Ok about 12 of them are basically tables containing certain list of values.

So basically I have a circular type relationship where there a table1 in the middle with the rest connected to table 1 having a one to many relationship. Table 1 is the many side while the rest are the one side. Because of this conglomerate of tables having a many side to table 1, most have right-joined- type of one to many relationship.

Now, to make data entry easy, I was asked weather or not it was possible to create a single form and to enter data instead of sub forms. It was also suggested if it was possible to pick from combo list values that is sourced from the other tables within a single form bound to table 1. Does anyone have any ideas on how to achieve this?

It really challenges me especially for those tables that have a further one to many relationships after table 1.

So the idea is:

1. Single form to be used comprising of combo boxes where data source of combos will be the other tables connected to table 1(no sub form /data sheet view required)

2. All other table has definite list values (No entry required) except for table 1 which is the many side and the centre point of all tables’ relations. That is where data entry is done and form is bound to.

3. Other tables have a further one to many relationships. For example: Table1 (many side) connected to table2 (one side) - further connected to table 3 (many side).
Table 1 requires data entry table 2 and 3 doesn’t. They have definite values that will be inputted.

4. Combo filtering based on combo will be required as when a value in combo1 sourced from say table 2 is selected combo2 from table 3 should be filtered based on combo1 value.

This has become quite a puzzle to me and I am trying to figure it out.
Does anyone have any brilliant idea? I would very much appreciate.

JM

[mshmyob]

This is a confusing post but I will make a stab at it.

Your question if a single form can access data from multiple tables is answered with a YES it can easily be done.

You can use combo boxes or list boxes obviously (these would be used to populate the foreign keys). To get access to other fields in other tables you can simply create a query that uses as many tables and fields you want from each table and then bind the form to the query instead of a table and you therefore have access to all the fields you need.

Combo box "filtering" based on values from other combo boxes can be done. It is called cascading combo boxes. There is an article on Bytes about it here.

cheers,

[Jerry Maiapu]

Thanks did that..I am sorry for posting this qusetion I think this was such a simple qiuestion. I would have figure it out myself.
Thnaks anyway..