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

Hi Dan,

Thanks for your response.  I don't think I did a good job explaining the 
matter.  The three tables exist already - they have horrid names, so I 
will use your table names to explain :)

I want to make a form with the 32 skills all listed - each one followed 
by a yes/no checkbox and a 1 to 3 droplist.  Each skill marked with the 
checkbox should generate one record to go in the tblVolunteerSkills - 
this way I can generate all the needed records for one volunteer and 
his/her skills with one click.  This is only to be used at the initial 
data entry for the volunteer.  After that, the usual form w/subform that 
already exists will be used - there, the skills are in a combo based on 
the tblSkills. 

I had already put together the form and subform system, and to me it 
looked like a good way to enter the data.  The data-entry volunteer 
pointed out that there were many more clicks or keystrokes needed on his 
part to do the data-entry with the form and subform method.  He liked 
having a list he could simply click his way down, even though that left 
bunches of records for skills not used.  So, I am trying to simulate the 
earlier situation for him by putting up a form he can click his way 
through, then click an action button that will generate the individual 
records of skills for the new volunteer and place them in the 
tblVolunteerSkills.  That's the piece I'm struggling with.  Any thoughts 
on that one?

Tina

Dan Waters wrote:
> 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
>
>
>