Adding multiple records to one table with one form

  • Thread starter Thread starter brig.featherstone
  • Start date Start date
B

brig.featherstone

Hi,

I have a database that tracks students and the programs that they
participate in. I have a form(participation) that needs to allow the
users to select the program(from a combo preferrably(yes i have a table
for programs)), time, and date(these must be txt boxes). I would like
to use combo or list box(es) to select the students that participated
and the program and then have a button on the form to add as many
records to the participation table as there are selected students. I
am stuck on the code for adding a record for each student that
participates. Any help on this would be greatly appreciated. Thanks
in advance.

Tables Fields
(Students)
{studentID}
(Participation)
{participationID}
{programID}
{studentID}
{time}
{date}
{participationpoints}
(Programs)
{programID}
Form
(Participation)

P.S.
I am a newbie with VB. I would have posted the VB code I have...but, it
is so screwed up from the things I have tried that I think you would
just laugh...anyhow, thanks again.
 
I would have the Participation form based on your Students table and add a
subform based on your Participation table.
 
Thank you Eric,

I see the logic behind making the participation form a subform in a
student form. I could still use some help with the code if there are
any bored genius types out there.
 
Ok, a little more information may help...

Basically I need to have a form(w/a subform probably) that allows the
user to select the program and staff member from a combo(tblprogram,
tblstaff), enter the date, time, and program points into txtboxes and
then on the subform select students that participated.


FORM
programID(from tblprogram)(combobox)
staffID(from tblstaff)(combobox)
date(txtbox)
time(txtbox)
programpoints(txtbox)
---------------------
SUBFORM
studentID1(from tblstudents)(combobox)
studentID2(from tblstudents)(combobox)
.....
studentID30(from tblstudents)(combobox)

--------------------

So the data entry user will select (programID) and (staffID) from a
combo. Then enter (date),(time), and (participationpoints) into
textboxes. Then on the subform they will select all the students that
participated from comboboxes. Finally they will click a button to add
a record for each student into (tblparticipation). Each record added
to the (tblparticipation) will include:
(programID),(staffID),(studentID),(date),(time),(participationpoints).
The (studentID) part is the tricky part. One program might have 3
students participating, while another might have 30. How would I add
an individual record(in tblparticipation) for each student selected in
the comboboxes?

Hopefully this is enough info.
 
It appears you will want 2 methods of entering a student into a program.
Method 1
A students main form with a program subform
This is used at the student level to add a student to multiple programs

Method 2
A program main for with a students subform
this is used when adding multiple students to a program

Note: To add a subform use the subform wizard on the control box. No vb is
necessary to be added by you.
 
Method 1, although handy, is not really necessary in this case. We
hardly ever, if ever, need to enter multiple programs for the same
student.

I will try the subform wizard today. Thanks Eric.
 
Back
Top