[AccessD] Make Multiple Selections on a Form and Create a Record for Each

Hi Tina,

You will want three tables: tblVolunteers, tblVolunteerSkills, and
tblSkills.

tblSkills will be a lookup table.

tblVolunteers will be a primary table and tblVolunteerSkills will be a
subtable with a one-to-may relationship.

In your form, I would recommend using a dropdown list to select a Skill, and
radio buttons for the Skill Level.  The reason for this is that it's likely
that you'll want to add/change/remove the Skills, and it's less likely that
you'll want to increase or decrease the number of skill levels that you'll
want to record.  To change the Skills, you just need to get into the
tblSkills table, and not make any form or code changes.

You'll also want to have a form for Volunteers (bound to tblVolunteers), and
a subform for the Skills and Levels (bound to tblVolunteerSkills).

Hope this helps!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
tinanfields at torchlake.com
Sent: Friday, February 27, 2009 12:22 PM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Make Multiple Selections on a Form and Create a Record
for Each

I hope the subject line isn't too confusing :)

Here's the situation:  volunteers fill out a paper form indicating which
skills they have and at 
what level (1 to 3, with 1 being high).  When this database was built
(1999), the developer crafted 
a macro that added one record for every possible skill (32, so far) to the
table of volunteers and 
skills.  The data-entry person then went down the rows, clicking the ones
that were to be selected, 
and entering the skill level number.  This results in lots of empty records.
Since then, the 
database crashed and the macro no longer works anyway.

I want to craft a form with a checkbox for each skill and a drop-list of the
skill-levels, so the 
data-entry person can click the checkbox and select the skill level - then
click a button that will 
place one record for each skill selected into the table of volunteers and
skills.  This gives the 
data entry person the same ease of entry, but does not create lots of empty
records.  I saw 
something in the ADH that captures the number of selections made, and I'm
guessing I need to start 
there, but I could sure use some inspiration on this.  If three skills are
checked, I want three 
records entered for that volunteer in the volunteers and skills table - each
one referencing one of 
the selected skills.  

Thanks for any ideas.

Tina


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com