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

Dan Waters dwaters at usinternet.com
Fri Feb 27 16:29:09 CST 2009


Ah Hah!  The classic picky data entry person problem! ;-)

I would still use the form/subform scenario with a few adjustments.

The main form still is used to select or add a Volunteer.  The subform
should show a blank record on first use - Allow Additions = Yes.

In the subform you will have, from left to right, a Skill combobox and a
Level textbox.  The Skill combobox should show maybe 60 in the List Rows
property (for future additions), and its AutoExpand property is set to Yes.
Its row source is a query based on the Skills table.  

Here's what saves time:  The tab index of the Skills combobox is 1 and the
Level textbox is 2.  Also, in the OnEnter event of the Skills combobox add
the code 'cboSkills.Dropdown'.  Now, for the form set the Cycle property to
All Records.

In the AfterUpdate event of the textbox modify the query for the Skills
combobox so that any previously selected Skills are no longer listed.

Once your data entry person selects a Volunteer, he pushes Tab and the list
of Skills is displayed.  He pushes just enough keys to make the selection,
then pushes Tab again.  Then he pushes 1, 2, or 3 on the keyboard.  Now when
he pushes Tab again, the cursor moves immediately to the Skills combobox,
and he can start over.

This is very fast because he's NOT using the mouse, which is slower than the
keyboard because it requires a lot of hand-eye coordination, where using the
keyboard only requires him to select the correct information, and quickly at
that.

You'll also want a Delete button on each row if the data entry person needs
to use it.  The Delete button's Tab Stop property must be = No.

Hope this helps more!
Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Tina Norris
Fields
Sent: Friday, February 27, 2009 3:42 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
>
>   
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list