Managing Many-Many Relationship With a Form

[smartin]

Greets All,

The database at hand is a time-tracking tool to facilitate logging hours
spent doing activities for work projects.

Projects have various activities; Activities can be associated with
various projects. To model this I have three tables: Projects,
Activities, and a junction table ProjAct, which consists of the PKs from
the other two tables.

My question is how to create a form to manage the associations?

The Activities list is relatively static, while the Projects list will
grow over time. So, I have designed a form based on Projects, with a
subform based on ProjAct. The subform is bound to a query that fetches a
description from Activities so the user can see activities associated
with the selected project.

This is where I'm stuck: I would like the user to see /all available/
activities and select the ones they wish to associate with the project.
I'm thinking maybe a multi-select listbox or list of checkboxes. Once
they have completed the selection, click a "commit" button and update
the junction table.

I'm confident I can handle coding to make the "commit" work, but pretty
fuzzy on what to do in the subform as far as what control to use. Maybe
a subform is not the answer at all? Has anyone been down this road?

Your ideas are appreciated.

 

[Brian]

I do this two different ways, depending on the complexity of the forms
involved: two forms or a form/subform. Either way, make sure the primary key
of the ProjAct table is a combination of Project & Activity if you want to
prevent duplicate entries. You can still have an auto-number
ProjectActivityID field that is Indexed (No Duplicates) as a unique key.

Activities form: just to manage your "masterfile" list of activities. Keep
everyone except managers or yourself out of this one if it is important to
keep a pre-configured activity list and do not want everyone creating new
types of activities.

Project form
Single-record view
Bound to Projects table.
Go to a new record on opening. Put this in the form's open event:
DoCmd.GoToRecord , , acNewRec
Combo Box in the form header that allows you to select the project you
want to work on. It will be something like this:
Private Sub Selector_AfterUpdate()
If IsNull([Selector]) = True Then Exit Sub
Me.RecordsetClone.FindFirst "[ProjectID] = '" & Me![Selector] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
ProjectName.SetFocus
Selector = Null
End Sub

Now, for the two-form method:
Make a button on the Project form. On its click event, open a ProjAct form,
including a WHERE string in the OpenForm method to filter it to the current
ProjectID.
ProjAct form
Continuous view
Bound to the ProjAct table and filtered to the current ProjectID
Make a hidden (to keep users from editing it manually) text box for
ProjectID.
Set its default value to =[Forms]![Project]![ProjectID]
Also in the detail section, have a ProjectActivity combo box
Bound to ProjectActivityID
RowSource: Activity table, sorted by ActivityName alphabetically
At least two columns
Hide & bind the first to ProjectActivityID
Show the second (ActivityName)

So, you open the project form, create a new project or navigate to an
existing project, then click the button. The ProjAct form opens with a list
of existing activities (if any) for that project and allowing you to add
activities in subsequent lines/records.

The subform method is the same except that instead of opening a new form on
a button click, you embed the ProjAct as a subform in the Project form and
link it on the ProjectID field. This way, it automatically displays the
ProjectActivity records associated with the Project. You will still need to
hide & set a default for the ProjectID field/text box.

 

[smartin]

Brian,

Thanks much -- this is very helpful. I decided to go with the subform
version and got it working.