Building a complex form

[bkelly]

This is a learning vehicle and I am an Access beginner. My book does not go into this depth. An issues database contains the following tables:

Tbl_Issues: The issues that have been created

Tbl_Join_Issues_To_People: A many to many join. One issue will have an opener, assigned to, sometimes a second or more assigned to if the issue is passed around, a closed by, an optional consulted with, and possibly other relationships. It contains a foreign key to Tbl_Issue_People_Relatsionships specifying the type of relationship.

Tbl_People: The people that can be involved with an issue.

Tbl_Issues_People_Relationships: Specifies the relationship between the issue and the person. New reasons may be found so I use a table rather than a hard coded list.

I am using form wizard to build a form that uses the issues as the primary source and the people as a secondary source. The join table should be transparent to the user. The relationship table will be a drop down list so the user can select the type of relationship.

When I create the form with the wizard I get the fields from the issues. That’s good. The join table comes up as the sub form, and I don’t see the people table or the relationships table.

Can this task be written down as a procedure to follow? Or is this too wild and wooly for that?

I can post the database here, or put it on my web site, but that would probably be a bit much. I have written a lot of Excel VBA, but never any Access. If VBA is needed I can go there.

 

[bkelly]

Access 2010 if that helps any.

Maybe if I post my steps someone will tell me what to do different.
1. Create -> Form Wizard
2. Open Tables/Queries drop down, select tbl_Issues
3. Select >> and move all to Selected Fields
4. Open drop down and select tbl_Join_Issues_To_People
5. Select >> and move all to Selected Fields
6. Open drop down and select tbl_People
7. Double click following to move to Selected Fields
a. PK_People
b. Last_Name
c. First_Name
d. Company
8. Click Next and leave default selected: form with subforms(s)
9. Click Next and leave Datasheet selected.
10. Leave defaults for Form and Subform and select Finish
11. Form opens in layout view. The fields from tbl_Issues are in the main form. The subform contains the fields from tbl_join_Issues_To_People. tbl_People is not visible.

I have tried selecting the tables in different order but it always comes up showing issues and the join table. I tried creating a query with all three tables and building a form using the query. It looks the same.