Board Members

[NotGood@All]

I want to set up a database for boards and members of those boards. It is a
many to many relationship so I have 3 tables, Board_Members, Board_Name, and
a table named Junction Table. The Junction Table has a many to 1
relationship with both the Board_Name and Board_Members tables. I’m trying
to create the input screen but can’t get it to work right. I would like a
dropdown box so when I change the name of the board, the members of that
board change with it. Can someone walk me through it??

Thanks

 

[BruceM]

The subform query will also need Board_MemberID from Junction_Table.
Typically the subform will have a combo box bound to Board_MemberID from
Junction_Table. The combo box Row Source will be from Board_Member, with
Board_MemberID the bound column, and then maybe LastName and FirstName
concatenated as the second column. The first column is hidden.

 

[NotGood@All]

Thank you both. This is confusing but fun. I will play with the combo box,
it will be nice to select a board and have all the members show up. One
more question, members can be appointed to a board more than 1 time, so I can
have member 1 appointed to board 1 on 3 different dates and appointed to
boards 2 & 3 on different dates. My thinking is that I need a table of dates
with a 1 to many relationship between the Board_Name, is that correct??

 

[BruceM]

First, be sure to create the relationships between the three tables. If you
need help with that, post again.

This is the basic structure of the forms, assuming the tables and queries
are described:

There is a main form frmBoardName based on the BoardName table, with a
subform fsubJunction (I would call the junction table by a more descriptive
name such as BoardMembership, and likewise the subform, but that's up to
you, as are any of the suggested names).

To build a subform, build it as you would any other form, using the query as
suggested (including my remark to add BoardMemberID). Create a combo box on
fsubJunction. Set its RecordSource to Board_MemberID. Set its Row Source
to a query based on tblBoard_Member. When you build the query, set its
first column to BoardMemberID and its second column to something like:
LastFirst: [LastName] & ", " & [FirstName]
Save the query, giving it a name. Choose the named query as the Row Source.
Set the combo box Bound Column to 1 (the Data tab of the Property Sheet).
On the Format tab of the Property Sheet, set the Column Count to 2 and the
Column Widths to something like 0";1.5".

Open the main form frmBoardName in design view. Use the toolbox to add a
Subform Control. Set the Subform Control's Source Object (Data tab of the
Property Sheet) to fsubJunction. Click into Link Child or Link Master, and
click the three dots on the right. If the tables are properly related
Access should suggest the correct linking fields (BoardNameID).

Note that you will need to build a list of Members, using a separate form
bound to the BoardMembers table, before you can select them from the combo
box on the subform.

An easy way to assure you do not have duplicate names in the listing of
members is to create an index on the combination of BoardMemberID and
BoardNameID in the junction table. See Help on the topic "Prevent entry of
duplicate values (MDB)" to see how to create an index on more than one
field.

Note that with this structure you can see different boards by scrolling
through the main form records, but not by selecting a board name. To do
that you need a separate unbound combo box. The combo box wizard in the
toolbox can help with that. To use the wizard, be sure the magic wand icon
is highlighted before clicking the combo box icon.

If you want to record the date appointed, add a Date/Time field to the
junction table, and to the subform.

I'm not sure what you are asking as regards different dates that a single
member is appointed to a board, but you do not need a table of dates that I
can see.

Thank you both. This is confusing but fun. I will play with the combo
box,
it will be nice to select a board and have all the members show up. One
more question, members can be appointed to a board more than 1 time, so I
can
have member 1 appointed to board 1 on 3 different dates and appointed to
boards 2 & 3 on different dates. My thinking is that I need a table of
dates
with a 1 to many relationship between the Board_Name, is that correct??

 

[BruceM]

Correction:

This line:
This is the basic structure of the forms, assuming the tables and queries
are described

should have been:
This is the basic structure of the forms, assuming the tables and queries
are as described

First, be sure to create the relationships between the three tables. If
you need help with that, post again.

This is the basic structure of the forms, assuming the tables and queries
are described:

There is a main form frmBoardName based on the BoardName table, with a
subform fsubJunction (I would call the junction table by a more
descriptive name such as BoardMembership, and likewise the subform, but
that's up to you, as are any of the suggested names).

To build a subform, build it as you would any other form, using the query
as suggested (including my remark to add BoardMemberID). Create a combo
box on fsubJunction. Set its RecordSource to Board_MemberID. Set its Row
Source to a query based on tblBoard_Member. When you build the query, set
its first column to BoardMemberID and its second column to something like:
LastFirst: [LastName] & ", " & [FirstName]
Save the query, giving it a name. Choose the named query as the Row
Source. Set the combo box Bound Column to 1 (the Data tab of the Property
Sheet). On the Format tab of the Property Sheet, set the Column Count to 2
and the Column Widths to something like 0";1.5".

Open the main form frmBoardName in design view. Use the toolbox to add a
Subform Control. Set the Subform Control's Source Object (Data tab of the
Property Sheet) to fsubJunction. Click into Link Child or Link Master,
and click the three dots on the right. If the tables are properly related
Access should suggest the correct linking fields (BoardNameID).

Note that you will need to build a list of Members, using a separate form
bound to the BoardMembers table, before you can select them from the combo
box on the subform.

An easy way to assure you do not have duplicate names in the listing of
members is to create an index on the combination of BoardMemberID and
BoardNameID in the junction table. See Help on the topic "Prevent entry
of duplicate values (MDB)" to see how to create an index on more than one
field.

Note that with this structure you can see different boards by scrolling
through the main form records, but not by selecting a board name. To do
that you need a separate unbound combo box. The combo box wizard in the
toolbox can help with that. To use the wizard, be sure the magic wand
icon is highlighted before clicking the combo box icon.

If you want to record the date appointed, add a Date/Time field to the
junction table, and to the subform.

I'm not sure what you are asking as regards different dates that a single
member is appointed to a board, but you do not need a table of dates that
I can see.

 

[NotGood@All]

Hi again. I have everything set up but I get a message "Field cannot be
updated" I can click the navigation button and go thru all the boards but
when I try to add a name to the subform I get the error
--
NotGood@All

Correction:

This line:
This is the basic structure of the forms, assuming the tables and queries
are described

should have been:
This is the basic structure of the forms, assuming the tables and queries
are as described

First, be sure to create the relationships between the three tables. If
you need help with that, post again.

This is the basic structure of the forms, assuming the tables and queries
are described:

There is a main form frmBoardName based on the BoardName table, with a
subform fsubJunction (I would call the junction table by a more
descriptive name such as BoardMembership, and likewise the subform, but
that's up to you, as are any of the suggested names).

To build a subform, build it as you would any other form, using the query
as suggested (including my remark to add BoardMemberID). Create a combo
box on fsubJunction. Set its RecordSource to Board_MemberID. Set its Row
Source to a query based on tblBoard_Member. When you build the query, set
its first column to BoardMemberID and its second column to something like:
LastFirst: [LastName] & ", " & [FirstName]
Save the query, giving it a name. Choose the named query as the Row
Source. Set the combo box Bound Column to 1 (the Data tab of the Property
Sheet). On the Format tab of the Property Sheet, set the Column Count to 2
and the Column Widths to something like 0";1.5".

Open the main form frmBoardName in design view. Use the toolbox to add a
Subform Control. Set the Subform Control's Source Object (Data tab of the
Property Sheet) to fsubJunction. Click into Link Child or Link Master,
and click the three dots on the right. If the tables are properly related
Access should suggest the correct linking fields (BoardNameID).

Note that you will need to build a list of Members, using a separate form
bound to the BoardMembers table, before you can select them from the combo
box on the subform.

An easy way to assure you do not have duplicate names in the listing of
members is to create an index on the combination of BoardMemberID and
BoardNameID in the junction table. See Help on the topic "Prevent entry
of duplicate values (MDB)" to see how to create an index on more than one
field.

Note that with this structure you can see different boards by scrolling
through the main form records, but not by selecting a board name. To do
that you need a separate unbound combo box. The combo box wizard in the
toolbox can help with that. To use the wizard, be sure the magic wand
icon is highlighted before clicking the combo box icon.

If you want to record the date appointed, add a Date/Time field to the
junction table, and to the subform.

I'm not sure what you are asking as regards different dates that a single
member is appointed to a board, but you do not need a table of dates that
I can see.

Thank you both. This is confusing but fun. I will play with the combo
box,
it will be nice to select a board and have all the members show up. One
more question, members can be appointed to a board more than 1 time, so I
can
have member 1 appointed to board 1 on 3 different dates and appointed to
boards 2 & 3 on different dates. My thinking is that I need a table of
dates
with a 1 to many relationship between the Board_Name, is that correct??
--
NotGood@All


:

The subform query will also need Board_MemberID from Junction_Table.
Typically the subform will have a combo box bound to Board_MemberID from
Junction_Table. The combo box Row Source will be from Board_Member,
with
Board_MemberID the bound column, and then maybe LastName and FirstName
concatenated as the second column. The first column is hidden.


Your tables should look like this .........

Board_Name
Board_NameID
Board_Name

Board_Member
Board_MemberID
FirstName
LastName
etc

Junction_Table
Junction_TableID
Board_NameID
Board_MemberID

1. Create a query named QrySFrmBoardMembers. Include the
Board_Member
table and the Junction_Table table. Include FirstName, LastName from
Board_Member and Board_NameID from Junction_Table.
2. Create a form/subform. Base the main form on Board_Name table
and
base the subform on QrySFrmBoardMembers. Make the main form single and
the
subform continuous.
3. Open the main form in design view. Open Properties, go to the
Data
tab and set LinkMaster and LinkChild to Board_NameID.

When you open the mainform, you will get a Board Name and a list of
members of that board. You can navigate to ny board name by clicking
on an
appropriate Navigation Button at the bottom left of your screen.

Steve





I want to set up a database for boards and members of those boards.
It is
a
many to many relationship so I have 3 tables, Board_Members,
Board_Name,
and
a table named Junction Table. The Junction Table has a many to 1
relationship with both the Board_Name and Board_Members tables. I'm
trying
to create the input screen but can't get it to work right. I would
like
a
dropdown box so when I change the name of the board, the members of
that
board change with it. Can someone walk me through it??

Thanks

 

[BruceM]

Sounds like the subform query itself cannot be updated. Open the query by
itself and attempt to update it. If you cannot, post its SQL. To do that,
click View >> SQL view. Copy what you see, and paste it into a message.

First, a check list:
1) Did you create the relationships? There is a one-to-many relationship
between BoardNameID in the BoardName table and BoardNameID in the junction
table. Similarly, one-to-many between BoardMemberID in the BoardMember
table and BoardMemberID in the junction table.

2) Are the Link Child and Link Master properties of the subform control (the
"box" containing the subform) set to BoardNameID?

3) Did you add BoardMemberID to the subform query? If so, did you bind a
combo box on the subform to this field? If so, is the combo box Row Source
based on the BoardMember table, with BoardMemberID included in the Row
Source? If so, is BoardMemberID the Bound Column?

Hi again. I have everything set up but I get a message "Field cannot be
updated" I can click the navigation button and go thru all the boards but
when I try to add a name to the subform I get the error
--
NotGood@All

Correction:

This line:
This is the basic structure of the forms, assuming the tables and queries
are described

should have been:
This is the basic structure of the forms, assuming the tables and queries
are as described

First, be sure to create the relationships between the three tables.
If
you need help with that, post again.

This is the basic structure of the forms, assuming the tables and
queries
are described:

There is a main form frmBoardName based on the BoardName table, with a
subform fsubJunction (I would call the junction table by a more
descriptive name such as BoardMembership, and likewise the subform, but
that's up to you, as are any of the suggested names).

To build a subform, build it as you would any other form, using the
query
as suggested (including my remark to add BoardMemberID). Create a
combo
box on fsubJunction. Set its RecordSource to Board_MemberID. Set its
Row
Source to a query based on tblBoard_Member. When you build the query,
set
its first column to BoardMemberID and its second column to something
like:
LastFirst: [LastName] & ", " & [FirstName]
Save the query, giving it a name. Choose the named query as the Row
Source. Set the combo box Bound Column to 1 (the Data tab of the
Property
Sheet). On the Format tab of the Property Sheet, set the Column Count
to 2
and the Column Widths to something like 0";1.5".

Open the main form frmBoardName in design view. Use the toolbox to add
a
Subform Control. Set the Subform Control's Source Object (Data tab of
the
Property Sheet) to fsubJunction. Click into Link Child or Link Master,
and click the three dots on the right. If the tables are properly
related
Access should suggest the correct linking fields (BoardNameID).

Note that you will need to build a list of Members, using a separate
form
bound to the BoardMembers table, before you can select them from the
combo
box on the subform.

An easy way to assure you do not have duplicate names in the listing of
members is to create an index on the combination of BoardMemberID and
BoardNameID in the junction table. See Help on the topic "Prevent
entry
of duplicate values (MDB)" to see how to create an index on more than
one
field.

Note that with this structure you can see different boards by scrolling
through the main form records, but not by selecting a board name. To
do
that you need a separate unbound combo box. The combo box wizard in
the
toolbox can help with that. To use the wizard, be sure the magic wand
icon is highlighted before clicking the combo box icon.

If you want to record the date appointed, add a Date/Time field to the
junction table, and to the subform.

I'm not sure what you are asking as regards different dates that a
single
member is appointed to a board, but you do not need a table of dates
that
I can see.


Thank you both. This is confusing but fun. I will play with the
combo
box,
it will be nice to select a board and have all the members show up.
One
more question, members can be appointed to a board more than 1 time,
so I
can
have member 1 appointed to board 1 on 3 different dates and appointed
to
boards 2 & 3 on different dates. My thinking is that I need a table
of
dates
with a 1 to many relationship between the Board_Name, is that
correct??
--
NotGood@All


:

The subform query will also need Board_MemberID from Junction_Table.
Typically the subform will have a combo box bound to Board_MemberID
from
Junction_Table. The combo box Row Source will be from Board_Member,
with
Board_MemberID the bound column, and then maybe LastName and
FirstName
concatenated as the second column. The first column is hidden.


Your tables should look like this .........

Board_Name
Board_NameID
Board_Name

Board_Member
Board_MemberID
FirstName
LastName
etc

Junction_Table
Junction_TableID
Board_NameID
Board_MemberID

1. Create a query named QrySFrmBoardMembers. Include the
Board_Member
table and the Junction_Table table. Include FirstName, LastName
from
Board_Member and Board_NameID from Junction_Table.
2. Create a form/subform. Base the main form on Board_Name
table
and
base the subform on QrySFrmBoardMembers. Make the main form single
and
the
subform continuous.
3. Open the main form in design view. Open Properties, go to the
Data
tab and set LinkMaster and LinkChild to Board_NameID.

When you open the mainform, you will get a Board Name and a list of
members of that board. You can navigate to ny board name by
clicking
on an
appropriate Navigation Button at the bottom left of your screen.

Steve





message
I want to set up a database for boards and members of those boards.
It is
a
many to many relationship so I have 3 tables, Board_Members,
Board_Name,
and
a table named Junction Table. The Junction Table has a many to 1
relationship with both the Board_Name and Board_Members tables.
I'm
trying
to create the input screen but can't get it to work right. I
would
like
a
dropdown box so when I change the name of the board, the members
of
that
board change with it. Can someone walk me through it??

Thanks

 

[NotGood@All]

Bruce, thanks for responding. I when over your check list and I think I it
it correctly. When I open the subform I cannot update. Here is the code
from qrySFrmBoardMembers

SELECT Junction_Table.Board_NameId, Board_Members.FirstName,
Board_Members.LastName, Junction_Table.RecommendedBy,
Junction_Table.DateAppointed
FROM Board_Members INNER JOIN Junction_Table ON Board_Members.Board_MemberID
= Junction_Table.Board_MemberID
ORDER BY Board_Members.LastName;

--
NotGood@All

Sounds like the subform query itself cannot be updated. Open the query by
itself and attempt to update it. If you cannot, post its SQL. To do that,
click View >> SQL view. Copy what you see, and paste it into a message.

First, a check list:
1) Did you create the relationships? There is a one-to-many relationship
between BoardNameID in the BoardName table and BoardNameID in the junction
table. Similarly, one-to-many between BoardMemberID in the BoardMember
table and BoardMemberID in the junction table.

2) Are the Link Child and Link Master properties of the subform control (the
"box" containing the subform) set to BoardNameID?

3) Did you add BoardMemberID to the subform query? If so, did you bind a
combo box on the subform to this field? If so, is the combo box Row Source
based on the BoardMember table, with BoardMemberID included in the Row
Source? If so, is BoardMemberID the Bound Column?

Hi again. I have everything set up but I get a message "Field cannot be
updated" I can click the navigation button and go thru all the boards but
when I try to add a name to the subform I get the error
--
NotGood@All

Correction:

This line:
This is the basic structure of the forms, assuming the tables and queries
are described

should have been:
This is the basic structure of the forms, assuming the tables and queries
are as described


First, be sure to create the relationships between the three tables.
If
you need help with that, post again.

This is the basic structure of the forms, assuming the tables and
queries
are described:

There is a main form frmBoardName based on the BoardName table, with a
subform fsubJunction (I would call the junction table by a more
descriptive name such as BoardMembership, and likewise the subform, but
that's up to you, as are any of the suggested names).

To build a subform, build it as you would any other form, using the
query
as suggested (including my remark to add BoardMemberID). Create a
combo
box on fsubJunction. Set its RecordSource to Board_MemberID. Set its
Row
Source to a query based on tblBoard_Member. When you build the query,
set
its first column to BoardMemberID and its second column to something
like:
LastFirst: [LastName] & ", " & [FirstName]
Save the query, giving it a name. Choose the named query as the Row
Source. Set the combo box Bound Column to 1 (the Data tab of the
Property
Sheet). On the Format tab of the Property Sheet, set the Column Count
to 2
and the Column Widths to something like 0";1.5".

Open the main form frmBoardName in design view. Use the toolbox to add
a
Subform Control. Set the Subform Control's Source Object (Data tab of
the
Property Sheet) to fsubJunction. Click into Link Child or Link Master,
and click the three dots on the right. If the tables are properly
related
Access should suggest the correct linking fields (BoardNameID).

Note that you will need to build a list of Members, using a separate
form
bound to the BoardMembers table, before you can select them from the
combo
box on the subform.

An easy way to assure you do not have duplicate names in the listing of
members is to create an index on the combination of BoardMemberID and
BoardNameID in the junction table. See Help on the topic "Prevent
entry
of duplicate values (MDB)" to see how to create an index on more than
one
field.

Note that with this structure you can see different boards by scrolling
through the main form records, but not by selecting a board name. To
do
that you need a separate unbound combo box. The combo box wizard in
the
toolbox can help with that. To use the wizard, be sure the magic wand
icon is highlighted before clicking the combo box icon.

If you want to record the date appointed, add a Date/Time field to the
junction table, and to the subform.

I'm not sure what you are asking as regards different dates that a
single
member is appointed to a board, but you do not need a table of dates
that
I can see.


Thank you both. This is confusing but fun. I will play with the
combo
box,
it will be nice to select a board and have all the members show up.
One
more question, members can be appointed to a board more than 1 time,
so I
can
have member 1 appointed to board 1 on 3 different dates and appointed
to
boards 2 & 3 on different dates. My thinking is that I need a table
of
dates
with a 1 to many relationship between the Board_Name, is that
correct??
--
NotGood@All


:

The subform query will also need Board_MemberID from Junction_Table.
Typically the subform will have a combo box bound to Board_MemberID
from
Junction_Table. The combo box Row Source will be from Board_Member,
with
Board_MemberID the bound column, and then maybe LastName and
FirstName
concatenated as the second column. The first column is hidden.


Your tables should look like this .........

Board_Name
Board_NameID
Board_Name

Board_Member
Board_MemberID
FirstName
LastName
etc

Junction_Table
Junction_TableID
Board_NameID
Board_MemberID

1. Create a query named QrySFrmBoardMembers. Include the
Board_Member
table and the Junction_Table table. Include FirstName, LastName
from
Board_Member and Board_NameID from Junction_Table.
2. Create a form/subform. Base the main form on Board_Name
table
and
base the subform on QrySFrmBoardMembers. Make the main form single
and
the
subform continuous.
3. Open the main form in design view. Open Properties, go to the
Data
tab and set LinkMaster and LinkChild to Board_NameID.

When you open the mainform, you will get a Board Name and a list of
members of that board. You can navigate to ny board name by
clicking
on an
appropriate Navigation Button at the bottom left of your screen.

Steve





message
I want to set up a database for boards and members of those boards.
It is
a
many to many relationship so I have 3 tables, Board_Members,
Board_Name,
and
a table named Junction Table. The Junction Table has a many to 1
relationship with both the Board_Name and Board_Members tables.
I'm
trying
to create the input screen but can't get it to work right. I
would
like
a
dropdown box so when I change the name of the board, the members
of
that
board change with it. Can someone walk me through it??

Thanks

 

[John W. Vinson]

Bruce, thanks for responding. I when over your check list and I think I it
it correctly. When I open the subform I cannot update. Here is the code
from qrySFrmBoardMembers

SELECT Junction_Table.Board_NameId, Board_Members.FirstName,
Board_Members.LastName, Junction_Table.RecommendedBy,
Junction_Table.DateAppointed
FROM Board_Members INNER JOIN Junction_Table ON Board_Members.Board_MemberID
= Junction_Table.Board_MemberID
ORDER BY Board_Members.LastName;

Do you really want to be able to update the board members' names in the
subform? It would be a lot more typical to base the subform directly on
Junction_Table, including only Junction _Table.BoardMemberID; on the form you
could bind that field to a Combo Box, storing the ID but displaying the board
member's name.

 

[NotGood@All]

John, Hi. I think you are correct. I want the members info to be the main
form and the subform to be the boards and the dates they were appointed to
those boards. I’m getting an msgbox saying the record cannot be updated.
Can you tell me if the structure of the tables are correct. The
relationships are a 1 to many from the Board_members to the Junction_Table
and a 1 to many from the Board_Name to the Junction_Table, (the many is in
the Junction_Table) I tried using the Wizard but get the same error.

Thank You very much
NotGood@All

Board_Name
Pk-Board_NameId
BoardName

Board_Members
Pk-Board_MemberId
LastName
FirstName
Etc…

Junction_Table
Pk-Junction_TableId
Board_nameId
Board_MemberId
RecommendedBy
DateAppointed

 

[John W. Vinson]

John, Hi. I think you are correct. I want the members info to be the main
form and the subform to be the boards and the dates they were appointed to
those boards. I’m getting an msgbox saying the record cannot be updated.
Can you tell me if the structure of the tables are correct. The
relationships are a 1 to many from the Board_members to the Junction_Table
and a 1 to many from the Board_Name to the Junction_Table, (the many is in
the Junction_Table) I tried using the Wizard but get the same error.

It sounds like you're trying to base one or both of the forms on a Query
joining the tables. The main form should be based on the Board_Name table (or
the Board_Members table if you wish), and the subform on the Junction_Table
only.

What are the Recordsources of these two forms? What is the Master Link Field
and Child Link Field?