Form Design with Linking Table

[Robert McEuen]

Using A2K3, Windows XP

I'm handling a many-to-many relationship with a linking table
structure as follows (irrelevant fields omitted):

tblIssue
PK_IssueID (autonumber, primary key)
IssueName (text)

tblArea
PK_AreaID (autonumber, primary key)
AreaName (text)

tblIssueArea
PK_IssueAreaID (autonumber, primary key)
FK_IssueID (foreign key linked to tblIssue.PK_IssueID)
FK_AreaID (foreign key linked to tblArea.PK_AreaID)

I'm trying to design a form for adding/editing Issues that will list
all AreaNames with a checkbox next to each, so that the user can mark
which AreaNames are associated with the issue he's adding/editing.
I've tried creating a subform, but with subform Master/Child links
being one-to-one relationships, I can't figure out how I'd be able to
have all AreaNames available.

I know I could do it the hard way without a subform, by dynamically
recreating the main form every time it's opened, looping through each
value in tblArea to create the checkboxes and labels, looping through
tblIssueArea to set the proper value of each checkbox, then running
Append/Delete queries on tblIssueArea for each time one of the
checkbox values changes. But I've gotta think there's an easier way!

Thanks in advance for any suggestions..

[salad]

Robert McEuen wrote:

Using A2K3, Windows XP

I'm handling a many-to-many relationship with a linking table
structure as follows (irrelevant fields omitted):

tblIssue
PK_IssueID (autonumber, primary key)
IssueName (text)

tblArea
PK_AreaID (autonumber, primary key)
AreaName (text)

tblIssueArea
PK_IssueAreaID (autonumber, primary key)
FK_IssueID (foreign key linked to tblIssue.PK_IssueID)
FK_AreaID (foreign key linked to tblArea.PK_AreaID)

I'm trying to design a form for adding/editing Issues that will list
all AreaNames with a checkbox next to each, so that the user can mark
which AreaNames are associated with the issue he's adding/editing.
I've tried creating a subform, but with subform Master/Child links
being one-to-one relationships, I can't figure out how I'd be able to
have all AreaNames available.

I know I could do it the hard way without a subform, by dynamically
recreating the main form every time it's opened, looping through each
value in tblArea to create the checkboxes and labels, looping through
tblIssueArea to set the proper value of each checkbox, then running
Append/Delete queries on tblIssueArea for each time one of the
checkbox values changes. But I've gotta think there's an easier way!

Thanks in advance for any suggestions..

Your statement "I've tried creating a subform, but with subform
Master/Child links being one-to-one relationships..." doesn't compute.

Usually you have a single record associated with the Many...the Many
side displayed in a datasheet or continuous form.

Now you are correct that your can't use the typical property sheet
setting on a many-2-many/continuous-2-continuos form style. This is
where you can use the OnCurrent event of a form (or some other event)
and the use of the Filter/FilterOn keywords.

I'm assuming you have a main form, and 2 subforms containing the
continuous/datasheets.

For example, you might have
Private Sub Form_Current()
Forms!MainForm!OtherFormName.Form.Filter = _
"AreaID = " & Me.AriaID
Forms!MainForm!OtherFormName.Form.FilterOn = True
End Sub

Or you could create a text box called AreaID, visible false, in the main
form. And in the other subform's recordset have something like
Private Sub Form_Current()
Forms!Main!AreaID = Me.AreaID
"AreaID = " & Me.AriaID
Forms!MainForm!OtherFormName.Form.Requery
End Sub

Maybe this will point you to a method you can use.

[Robert McEuen]

On Jun 8, 2:03 pm, salad <[e-mail]:

Robert McEuen wrote:

Using A2K3, Windows XP

I'm handling a many-to-many relationship with a linking table
structure as follows (irrelevant fields omitted):

tblIssue
PK_IssueID (autonumber, primary key)
IssueName (text)

tblArea
PK_AreaID (autonumber, primary key)
AreaName (text)

tblIssueArea
PK_IssueAreaID (autonumber, primary key)
FK_IssueID (foreign key linked to tblIssue.PK_IssueID)
FK_AreaID (foreign key linked to tblArea.PK_AreaID)

I'm trying to design a form for adding/editing Issues that will list
all AreaNames with a checkbox next to each, so that the user can mark
which AreaNames are associated with the issue he's adding/editing.
I've tried creating a subform, but with subform Master/Child links
being one-to-one relationships, I can't figure out how I'd be able to
have all AreaNames available.

I know I could do it the hard way without a subform, by dynamically
recreating the main form every time it's opened, looping through each
value in tblArea to create the checkboxes and labels, looping through
tblIssueArea to set the proper value of each checkbox, then running
Append/Delete queries on tblIssueArea for each time one of the
checkbox values changes. But I've gotta think there's an easier way!

Thanks in advance for any suggestions..

Your statement "I've tried creating a subform, but with subform
Master/Child links being one-to-one relationships..." doesn't compute.

Usually you have a single record associated with the Many...the Many
side displayed in a datasheet or continuous form.

Now you are correct that your can't use the typical property sheet
setting on a many-2-many/continuous-2-continuos form style. This is
where you can use the OnCurrent event of a form (or some other event)
and the use of the Filter/FilterOn keywords.

I'm assuming you have a main form, and 2 subforms containing the
continuous/datasheets.

For example, you might have
Private Sub Form_Current()
Forms!MainForm!OtherFormName.Form.Filter = _
"AreaID = " & Me.AriaID
Forms!MainForm!OtherFormName.Form.FilterOn = True
End Sub

Or you could create a text box called AreaID, visible false, in the main
form. And in the other subform's recordset have something like
Private Sub Form_Current()
Forms!Main!AreaID = Me.AreaID
"AreaID = " & Me.AriaID
Forms!MainForm!OtherFormName.Form.Requery
End Sub

Maybe this will point you to a method you can use.

Thanks for your reply, Salad.

Sorry about the confusion with my Master/Child statement. What I was
getting at is that the link can't be the equivalent of an outer join.
So, in the case of my situation, if Issue 1 is associated with Areas
1, 2, and 5, a subform based on tblIssueArea would only show Areas 1,
2, and 5. What I'm trying to achieve is to have Areas 1, 2, 3, 4, and
5 visible with checkboxes next to each, with the checkboxes next to
Areas 1, 2, and 5 checked and those next to 3 and 4 unchecked.

The setup I initially envisioned was a main form based on tblIssue and
one subform (continuous, linked on PK_IssueID=FK_IssueID) based on
tblIssueArea, or based on a query joining tblIssueArea and tblArea.

Unfortunately, the filtering idea doesn't work for the same reason
subform isn't working - neither allows for Areas 3 and 4 (in this
example) to be available.

Does that make more sense?

[salad]

Robert McEuen wrote:

On Jun 8, 2:03 pm, salad <[e-mail]:

>>Robert McEuen wrote:

>>>Using A2K3, Windows XP

>>>I'm handling a many-to-many relationship with a linking table
structure as follows (irrelevant fields omitted):

>>>tblIssue
PK_IssueID (autonumber, primary key)
IssueName (text)

>>>tblArea
PK_AreaID (autonumber, primary key)
AreaName (text)

>>>tblIssueArea
PK_IssueAreaID (autonumber, primary key)
FK_IssueID (foreign key linked to tblIssue.PK_IssueID)
FK_AreaID (foreign key linked to tblArea.PK_AreaID)

>>>I'm trying to design a form for adding/editing Issues that will list
all AreaNames with a checkbox next to each, so that the user can mark
which AreaNames are associated with the issue he's adding/editing.
I've tried creating a subform, but with subform Master/Child links
being one-to-one relationships, I can't figure out how I'd be able to
have all AreaNames available.

>>>I know I could do it the hard way without a subform, by dynamically
recreating the main form every time it's opened, looping through each
value in tblArea to create the checkboxes and labels, looping through
tblIssueArea to set the proper value of each checkbox, then running
Append/Delete queries on tblIssueArea for each time one of the
checkbox values changes. But I've gotta think there's an easier way!

>>>Thanks in advance for any suggestions..

Your statement "I've tried creating a subform, but with subform
Master/Child links being one-to-one relationships..." doesn't compute.

Usually you have a single record associated with the Many...the Many
side displayed in a datasheet or continuous form.

Now you are correct that your can't use the typical property sheet
setting on a many-2-many/continuous-2-continuos form style. This is
where you can use the OnCurrent event of a form (or some other event)
and the use of the Filter/FilterOn keywords.

I'm assuming you have a main form, and 2 subforms containing the
continuous/datasheets.

For example, you might have
Private Sub Form_Current()
Forms!MainForm!OtherFormName.Form.Filter = _
"AreaID = " & Me.AriaID
Forms!MainForm!OtherFormName.Form.FilterOn = True
End Sub

Or you could create a text box called AreaID, visible false, in the main
form. And in the other subform's recordset have something like
Private Sub Form_Current()
Forms!Main!AreaID = Me.AreaID
"AreaID = " & Me.AriaID
Forms!MainForm!OtherFormName.Form.Requery
End Sub

Maybe this will point you to a method you can use.

Thanks for your reply, Salad.

Sorry about the confusion with my Master/Child statement. What I was
getting at is that the link can't be the equivalent of an outer join.
So, in the case of my situation, if Issue 1 is associated with Areas
1, 2, and 5, a subform based on tblIssueArea would only show Areas 1,
2, and 5. What I'm trying to achieve is to have Areas 1, 2, 3, 4, and
5 visible with checkboxes next to each, with the checkboxes next to
Areas 1, 2, and 5 checked and those next to 3 and 4 unchecked.

The setup I initially envisioned was a main form based on tblIssue and
one subform (continuous, linked on PK_IssueID=FK_IssueID) based on
tblIssueArea, or based on a query joining tblIssueArea and tblArea.

Unfortunately, the filtering idea doesn't work for the same reason
subform isn't working - neither allows for Areas 3 and 4 (in this
example) to be available.

Does that make more sense?

Not really. I envision Main form. It has 2 subforms; subform for area
checking, subform of areas that were checked. I probably would have a
filter on the areas checked set to "" initially (or on Open). Then have
a command button to set the filter once area records are checked.

I do something similar and the filter statement should/will work.