Open another form if field checked in one form

[CRBI]

I have 2 tables I have created forms from. One is a Membership table, one a
Volunteers table. Some Members volunteer, but not all volunteers are
members. Here's what I'm trying to accomplish:

On the Member form, I have a Yes/No field to be checked if the person is
also a volunteer. When this field is checked, I would like for it to open a
form for me to put that person's volunteer info in (Area of Interest, Date,
Adult or Junior, etc). I can actually get that to work. When I check the box
it does open the Volunteer Form I created from the Volunteer table. Here's
the problem though:

When I do get the Volunteer Form open based on checking the box on the
Member Form, it does let me put in info and pulls the member's ID number.
However, when I try to add another member (by checking the box on the Member
form), it does open the Volunteer form, but has whatever previous volunteer
data I have entered in it. It will somehow keep only one record when I enter
like this, so if I enter 10 volunteers and go view the Volunteers table based
on those entries, I only have one entry in the table. Is there any way to:

A. Have it pull the Member's ID number automatically to the Volunteer form
by checking the Volunteer box? (ID could be entered manually if not).

B. How can I get it to consider each one an individual entry, tied back to
the person by ID number?

Basically, in short form, I want to be able to check the box that says the
member is a volunteer also on the member's form and have it open the
volunteer's form, automatically putting the member's ID number in, then
manually filling in the rest of the volunteer form, then having a close
button that will close and return the member form.

Any ideas? Thanks for all suggestions!

 

[Jeanette Cunningham]

If I may, I would like to suggest a slightly different table structure.
(Because a more flexible table structure will make your job of creating
forms that work will be made easier).

tblMember - MemberID - primary key
FirstName
LastName
other fields

tblInterestArea - InterestID - primary key
InterestAreaName


tblAgeGroup - AgeGroupID - primary key
AgeGroupName

tblMemberVolunteer
MemberVolID - primary key
MemberID - foreign key from member table
InterestAreaID - foreign key from interest area table ( 1 to many)
AgeGroupID - foreign key from age group table
VolDate - date volunteered


tblMember is related 1 to many to tblMemberVolunteer

tlbInterestArea is a lookup table for tblMemberVolunteer
tblAgeGroup is a lookup table for tblMemberVolunteer


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia