Populating a "join table"

[MikeB]

There is a proper word for it, but I cannot right now think of it. If
I want to create a many-many relationship in Access, I have to create
a join table and then join the records in this table by creating two
one-many relationships.

Eg. If I have many students and many sections and each student can
take more tha none section, then I have to create a table
tblStudentSection that has a foreign key field for the StidentID and
the SectionID.

Suppose now I want to create records in this table by assigning
students to a section. Let's call this the Roster form.

I'm looking at the Roster form and I have already the fk_SectionID in
another form that is open. How can I populate each new record I create
in Roster with the fk_SectionID without having the user fill in the
sectionID?

Thanks.

PS. Last question for the day, I promise!

 

[KARL DEWEY]

Set one-to-many relationships from Student table to tblStudentSection and
from Section to tblStudentSection.

Use a form/subform for student/StudentSection with Master/Child links on
StidentID. Use a combo box to select section.

Second form/subform for section/StudentSection with Master/Child links on
SectionID. Use a combo box to select student.

 

[MikeB]

I remember now, the correct word is "junction table."

Set one-to-many relationships from Student table to tblStudentSection and
from Section to tblStudentSection.

Yes, I have this.

Use a form/subform for student/StudentSection with Master/Child links on
StidentID.  Use a combo box to select section.

Second form/subform for section/StudentSection with Master/Child links on
SectionID.  Use a combo box to select student.

This is the part I don't get.

It seems I've made this complicated, so perhaps I can get some
guidance here.

I have a set of relationships as per this screenshot:
http://i42.tinypic.com/2wrno6o.jpg

I created a form for Professors (really anybody teaching a course),
with a subform for assigning Course sections to each professor.

On the subform for Sections, I put a button to open the Roster form
(based on tblSectionRosters) for that particular Section.

So I already have the predetermined fk_sectionID. What I'd like to do
now is to be able to place a single combobox on this form to select
the students and populate the tblSectionRosters with the right
fk_SectionID and fk_studentid without having any user interaction with
the fk_sectionID. I'd he happy to have a hidden field on the form that
has fk_sectionID, but I just don't know how to populate this field
with the predetermined fk_SectionID value.

Thanks

 

[Armen Stein]

So I already have the predetermined fk_sectionID. What I'd like to do
now is to be able to place a single combobox on this form to select
the students and populate the tblSectionRosters with the right
fk_SectionID and fk_studentid without having any user interaction with
the fk_sectionID. I'd he happy to have a hidden field on the form that
has fk_sectionID, but I just don't know how to populate this field
with the predetermined fk_SectionID value.

Hi Mike,

As Karl said, the Master & Child properties do this for you. They are
in the property sheet of the control that holds your SectionRoster
subform.

Specify SectionID as Master, and fk_SectionID as Child. Then the
fk_SectionID will be populated from the SectionID automatically, and
it doesn't have to be visible on the subform. Then all you have to do
is have a combobox that selects a Student for each row.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[MikeB]

Armen and Karl, Thank you so much. I think I'm getting there, but I'm
having one more difficulty.

Please bear with me. I'm so close, but not getting the banana yet.

I have a form/Subform for Courses/Sections. On each section record in
the subform I have a button to open the Roster form. The Roster form
has as its data source the sections table (tblSections). The subform
has record source tblSectionRosters.

The button has an embedded macro (I used the wizard to construct it).
The parameters for the macro is:

Form name: Roster
View: Form
Filter condition: "[SectionID]=" & [SectionID]
Data Mode:
Window Mode: Normal

Regardless on which section on the Courses/Sections subform sectionID
I press the button, form Rosters always opens up on the first record
in the Sections database.

It seems that I'm missing an important piece of how to pass along or
identify which SectionID caused the button to be pressed.

Any more advice would be appreciated.

Thanks,
Mike

 

[Armen Stein]

The button has an embedded macro (I used the wizard to construct it).
The parameters for the macro is:

Form name: Roster
View: Form
Filter condition: "[SectionID]=" & [SectionID]
Data Mode:
Window Mode: Normal

Regardless on which section on the Courses/Sections subform sectionID
I press the button, form Rosters always opens up on the first record
in the Sections database.

It seems that I'm missing an important piece of how to pass along or
identify which SectionID caused the button to be pressed.

Hi Mike,

I'm not sure what could be going wrong with that Macro. Are you sure
you don't need fk_SectionID as one of your filter variables, instead
of just SectionID?

We don't use macros in our shop, we're strictly VBA. If you want to
try a bit of VBA, here's some that should work. Just switch the On
Click property of your button to [Event Procedure], then click the
[...] button and paste it in.


On Error GoTo Error_Handler
Dim strLinkCriteria as String

strLinkCriteria = "[SectionID]=" & Me![SectionID]
DoCmd.OpenForm FormName:="Roster", WhereCondition:=mstrLinkCriteria

Exit_Procedure:
On Error Resume Next
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Err.Description
Resume Exit_Procedure
Resume
End Sub

Armen Stein
Microsoft Access MVP
www.JStreetTech.com