Combo Box default value

[Guest]

I have table that stores group members and their relative information (name,
supervisor, etc). I have created a query to find only the current members.

I also have a table to store meeting attendance. I am developing a form for
this table. I have 10 combo boxed to select a members name. The row source
for these combo boxes is the membername field from the aforementioned query.
Insted of manually selecting each members name in the combo boxes, I would
like to set a default value. For example I would like the default value for
Member1 to be the first person listed in the current member query. The
default for Member2 to be the second person listed in the query. This way
when a person leaves the group I do not have to manually change the default
values. I have not been able to setup the default values here. Is there a
way to set the default value based on a row of a query? Something like:

me.member1=Query!CurrentMember!membername(x)

where x is the row number? Is there a better way to do this?

Thanks,

 

[Graham Mandeno]

Hi Mike

To me this sounds suspiciously like you have 10 fields in your meetings
table for the 10 possible attendees. Is this right? If so then this is not
good table design. What happens if you get 11 people attending a meeting?
How can you easily list what meetings were attended by John Smith or Mary
Brown? How can you easily count the number of attendees at each meeting?

In actual fact, you have a many-to-many relationship between meetings and
people - a meeting can be attended by many people, and a person can attend
many meetings. The ONLY way to store this data reliably is using a third
table, called a "junction table". Let's call it "MeetingAttendance"

Your MeetingAttendance table needs two fields, one to contain the primary
key value of the meeting being attended (let's call this field "MeetingFK")
and one for the PK value of the person attending ("PersonFK"). You must
then set up a one-to-many relationship between the PK field of your Meetings
table and the foreign key field (MeetingFK) in MeetingAttendance. The same
applies between your People table and MeetingAttendance.

You might also like to add some further fields to your junction table - for
example, the Role (chair, secretary, etc) that the attendee performed at the
meeting.

Now, you add and remove attendees at the meeting by creating or deleting
records from your junction table. This can be done with a subform (the
traditional method) or with a listbox, a combo box, a couple of command
buttons and some code.

As far as setting up the default attendees, I suggest you have another table
of "MeetingTypes" (Finance committee, Social club, etc) and another junction
table of DefaultAttendees, with MeetingTypeFK, PersonFK and Role.

When you add a new meeting record and specify its MeetingType, it is then an
easy matter to run an append query to add to the MeetingAttendance table one
record for each record in DefaultAttendees that corresponds to the selected
MeetingType.

 

[Guest]

Graham,

Thanks for the reply. I revised my table structure and used an append query
as you suggested. It works exactly as I wanted.

Mike

Hi Mike

To me this sounds suspiciously like you have 10 fields in your meetings
table for the 10 possible attendees. Is this right? If so then this is not
good table design. What happens if you get 11 people attending a meeting?
How can you easily list what meetings were attended by John Smith or Mary
Brown? How can you easily count the number of attendees at each meeting?

In actual fact, you have a many-to-many relationship between meetings and
people - a meeting can be attended by many people, and a person can attend
many meetings. The ONLY way to store this data reliably is using a third
table, called a "junction table". Let's call it "MeetingAttendance"

Your MeetingAttendance table needs two fields, one to contain the primary
key value of the meeting being attended (let's call this field "MeetingFK")
and one for the PK value of the person attending ("PersonFK"). You must
then set up a one-to-many relationship between the PK field of your Meetings
table and the foreign key field (MeetingFK) in MeetingAttendance. The same
applies between your People table and MeetingAttendance.

You might also like to add some further fields to your junction table - for
example, the Role (chair, secretary, etc) that the attendee performed at the
meeting.

Now, you add and remove attendees at the meeting by creating or deleting
records from your junction table. This can be done with a subform (the
traditional method) or with a listbox, a combo box, a couple of command
buttons and some code.

As far as setting up the default attendees, I suggest you have another table
of "MeetingTypes" (Finance committee, Social club, etc) and another junction
table of DefaultAttendees, with MeetingTypeFK, PersonFK and Role.

When you add a new meeting record and specify its MeetingType, it is then an
easy matter to run an append query to add to the MeetingAttendance table one
record for each record in DefaultAttendees that corresponds to the selected
MeetingType.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have table that stores group members and their relative information
(name,
supervisor, etc). I have created a query to find only the current
members.

I also have a table to store meeting attendance. I am developing a form
for
this table. I have 10 combo boxed to select a members name. The row
source
for these combo boxes is the membername field from the aforementioned
query.
Insted of manually selecting each members name in the combo boxes, I would
like to set a default value. For example I would like the default value
for
Member1 to be the first person listed in the current member query. The
default for Member2 to be the second person listed in the query. This way
when a person leaves the group I do not have to manually change the
default
values. I have not been able to setup the default values here. Is there
a
way to set the default value based on a row of a query? Something like:

me.member1=Query!CurrentMember!membername(x)

where x is the row number? Is there a better way to do this?

Thanks,

 

[Graham Mandeno]

That's great news, Mike. I'm glad you have "seen the light of normalised
design" and that it's working for you
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Thanks for the reply. I revised my table structure and used an append
query
as you suggested. It works exactly as I wanted.

Mike

Hi Mike

To me this sounds suspiciously like you have 10 fields in your meetings
table for the 10 possible attendees. Is this right? If so then this is
not
good table design. What happens if you get 11 people attending a
meeting?
How can you easily list what meetings were attended by John Smith or Mary
Brown? How can you easily count the number of attendees at each meeting?

In actual fact, you have a many-to-many relationship between meetings and
people - a meeting can be attended by many people, and a person can
attend
many meetings. The ONLY way to store this data reliably is using a third
table, called a "junction table". Let's call it "MeetingAttendance"

Your MeetingAttendance table needs two fields, one to contain the primary
key value of the meeting being attended (let's call this field
"MeetingFK")
and one for the PK value of the person attending ("PersonFK"). You must
then set up a one-to-many relationship between the PK field of your
Meetings
table and the foreign key field (MeetingFK) in MeetingAttendance. The
same
applies between your People table and MeetingAttendance.

You might also like to add some further fields to your junction table -
for
example, the Role (chair, secretary, etc) that the attendee performed at
the
meeting.

Now, you add and remove attendees at the meeting by creating or deleting
records from your junction table. This can be done with a subform (the
traditional method) or with a listbox, a combo box, a couple of command
buttons and some code.

As far as setting up the default attendees, I suggest you have another
table
of "MeetingTypes" (Finance committee, Social club, etc) and another
junction
table of DefaultAttendees, with MeetingTypeFK, PersonFK and Role.

When you add a new meeting record and specify its MeetingType, it is then
an
easy matter to run an append query to add to the MeetingAttendance table
one
record for each record in DefaultAttendees that corresponds to the
selected
MeetingType.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have table that stores group members and their relative information
(name,
supervisor, etc). I have created a query to find only the current
members.

I also have a table to store meeting attendance. I am developing a
form
for
this table. I have 10 combo boxed to select a members name. The row
source
for these combo boxes is the membername field from the aforementioned
query.
Insted of manually selecting each members name in the combo boxes, I
would
like to set a default value. For example I would like the default
value
for
Member1 to be the first person listed in the current member query. The
default for Member2 to be the second person listed in the query. This
way
when a person leaves the group I do not have to manually change the
default
values. I have not been able to setup the default values here. Is
there
a
way to set the default value based on a row of a query? Something
like:

me.member1=Query!CurrentMember!membername(x)

where x is the row number? Is there a better way to do this?

Thanks,