Combos in Subform & adding multiple records

[brig.featherstone]

Ok, I am trying to track members(youth) participation in programs. In
order to keep our users from hurting me or themselves I have basically
recreated a paper form we have used for the past few years. So i have
a form with a combo box to select the program, a combo to select the
staff that taught the program, and textboxes for date, time, and
participation points. Then I have 30 combo boxes to select each member
that participated in the program. So after they have entered/selected
all the information, I'll need some code(probably an ()onclick) to add
a record in the participation table for each member that participated.)
Each of the records added would have:
(participationID(auto#)), (staffID(from combo)), (date(txtbx)),
(time(txtbx)), (participationpoints(txtbx)), and (youthID(one of the 30
combos)).

Sadly I am new to VB and programming in general. I believe this will
require a loop because sometimes there will be 10 youth and sometimes
15.... However I am stumped. Any help would be fantastic.

Oh here's some table structure, maybe that will help.

TABLE FIELD
--------------------------------------------------------------
tblparticipation---------------------------------
participationID
staffID
youthID
programID
date
time
participationpoints

tblyouth-------------------------------------------
youthID
firstname
lastname

tblprograms------------------------------------
programID
programname

tblstaff---------------------------------------------
staffID
firstname
lastname

Thanks in advance.

Brig

 

[Damon Heron]

You're kidding about the 30 comboboxes, right? Why not have one combobox
with the row source the Youth table? The form is tied to the table
Participation. As you navigate thru the records, Access will save each
entry automatically. If your problem is repeating the same info (the
program, staff, date, time, and points), then you could have something like
this in the form's AfterInsert event:
Private Sub Form_AfterInsert()
Const cQuote = """"
Me!txtDate.DefaultValue = cQuote & Me!txtDate.Value & cQuote
'repeat this line for each of the controls that will repeat data....

 

[John Vinson]

Ok, I am trying to track members(youth) participation in programs. In
order to keep our users from hurting me or themselves I have basically
recreated a paper form we have used for the past few years. So i have
a form with a combo box to select the program, a combo to select the
staff that taught the program, and textboxes for date, time, and
participation points. Then I have 30 combo boxes to select each member
that participated in the program. So after they have entered/selected
all the information, I'll need some code(probably an ()onclick) to add
a record in the participation table for each member that participated.)
Each of the records added would have:
(participationID(auto#)), (staffID(from combo)), (date(txtbx)),
(time(txtbx)), (participationpoints(txtbx)), and (youthID(one of the 30
combos)).

You're going about this in the wrong way.

Rather than 30 combo boxes to select members, consider using a Subform
bound to the participation table. You can see 20, or 30, or 18 or
however many *ROWS* you need to enter the record.

If you are trying to design your user interface to match a paper
form... just bear in mind that paper forms are rarely good guides to
computer interface design!

John W. Vinson[MVP]