Best way to create attendance form to insert multiple records

  • Thread starter serviceman via AccessMonster.com
  • Start date
S

serviceman via AccessMonster.com

Hi All,
I have three tables EVENTS,STUDENTS,ATTENDANCE. The STUDENTS table stores all
of the student info, with STUDENT_ID as the (Autonumber) primary Key. The
EVENTS table stores info about different classes that go on at our school,
such as start & stop times, what day(s) this event happens, and if it is a
one-time event, the date of that event. The primary key of EVENTS is EVENT_ID
(autonumber). What I would like to do is create a form where I can look up an
event from the EVENTS table, add the date that the event occured, select many
students from the STUDENTS table, and then save all of these records in the
ATTENDANCE table. The ATTENDANCE table has 3 columns; EVENT_ID,STUDENT_ID,
EVENT_DAT.
I'm new to Access, learning by leaps and bounds. My background is SQL, so I
can handle the table and query stuff, but I can't figure this Form out to
save my life! It would be good if this form opened in a way that the user
could see a table view of all the students to select from, and then just
click to select each one, but any ideas would be greatly appreciated!!
Andy
 
K

Klatuu

Since you know your SQL, I will leave that to you. What you want is a
Form/Sub Form configuration.
Your main form should have the EVENTS table as its recordsource. I would
suggest a combo box to select an event. The sub form would be based on a
query that would join the attendance table with the student table. When you
set up the relationship of the main and sub forms, you can link them on the
event table's primary key so that only rows for that event will show in this
sub form. Make this sub form in data sheet view.

Now, if you want to select a student from the sub form and be able to look
at his information in the student table, you could do one of two things. A
sub form can have a sub form, so you could have a sub form to your subform
that would be based on the student table. so each time you position on a row
in the first sub form, the student's data will show in the second sub form.

If you only want to see the student data on demand, create a separate form
based on ht student table. Put a command button on your form that will open
the student info form filtered for the student in the current row of the sub
form.

If this has not confused you enought, I will be happy to provide additional
obsfucation on request.
 
S

serviceman via AccessMonster.com

Klatuu!
You and I are going to be family before this is done! Yup, you confused the
hell out of me, but that doesn't mean I won't give it a shot! I'm all proud
of myself after writing the calendar module for this app; Input your
beginning fiscal month and day, click the button, and the code populates a
calendar table with the date, numeric day of the year, day name, fiscal month,
fiscal day of the year, boolean flag for workdays, and boolean flag for
holidays. Cool Stuff!
I THINK I get what you are talking about, but I need to try it to see. I will
be out on the road today, but I will be heavy into it tonight. I will let you
know how I make out each step, and I'm sure with your un-obsfucated
directions and my ability to speak SQLeasian we should be able to get it...
Talk to you later! :)
Andy
Since you know your SQL, I will leave that to you. What you want is a
Form/Sub Form configuration.
Your main form should have the EVENTS table as its recordsource. I would
suggest a combo box to select an event. The sub form would be based on a
query that would join the attendance table with the student table. When you
set up the relationship of the main and sub forms, you can link them on the
event table's primary key so that only rows for that event will show in this
sub form. Make this sub form in data sheet view.

Now, if you want to select a student from the sub form and be able to look
at his information in the student table, you could do one of two things. A
sub form can have a sub form, so you could have a sub form to your subform
that would be based on the student table. so each time you position on a row
in the first sub form, the student's data will show in the second sub form.

If you only want to see the student data on demand, create a separate form
based on ht student table. Put a command button on your form that will open
the student info form filtered for the student in the current row of the sub
form.

If this has not confused you enought, I will be happy to provide additional
obsfucation on request.
Hi All,
I have three tables EVENTS,STUDENTS,ATTENDANCE. The STUDENTS table stores all
[quoted text clipped - 13 lines]
click to select each one, but any ideas would be greatly appreciated!!
Andy
 
S

serviceman via AccessMonster.com

I'm Baaaaaack.....
I'm in the neighborhood on this:
I have a Form based on the table EVENTS, with a subform based on a
students/attendance query. I can look up an Event, then select students and
save the relevant data to the ATTENDANCE table. So far so good. What I would
like to do is add a second subform that lists the students that have been
scheduled for that event/date. I have added a subform in datasheet view, but
I cannot get the damned thing to refresh each time I add a student. It keeps
telling me the Form is closed and needs to be opened first. I'm sure that
there is a simple way to do this.....
Feeling-less-than-simple Andy
Klatuu!
You and I are going to be family before this is done! Yup, you confused the
hell out of me, but that doesn't mean I won't give it a shot! I'm all proud
of myself after writing the calendar module for this app; Input your
beginning fiscal month and day, click the button, and the code populates a
calendar table with the date, numeric day of the year, day name, fiscal month,
fiscal day of the year, boolean flag for workdays, and boolean flag for
holidays. Cool Stuff!
I THINK I get what you are talking about, but I need to try it to see. I will
be out on the road today, but I will be heavy into it tonight. I will let you
know how I make out each step, and I'm sure with your un-obsfucated
directions and my ability to speak SQLeasian we should be able to get it...
Talk to you later! :)
Andy
Since you know your SQL, I will leave that to you. What you want is a
Form/Sub Form configuration.
[quoted text clipped - 24 lines]
 
K

Klatuu

What are you doing when it tells you the form is closed? This seems
strange. I am guessing your subform that is giving you problems is a sub
form of the EVENTS form? When this happens, are you in the other subform
where you add a student?

The only thing I an think of right now is that it may be how you are
addressing the other subform, but I think that would give you a different
error. Are you addressing it as Forms!MainFormName!OtherSubFormName.Requery?

If this is what you are doing in the subform where you add a student and it
is not working, try moving the requery to some event in the main form. You
may have to take some action that will make the main form the active form
before you do the requery.

Be advised this is all speculation. Without it in front of me, I can only
suggest possibilities.

If we are to be family, I will need your SSN for tax reporting purposes :)

serviceman via AccessMonster.com said:
I'm Baaaaaack.....
I'm in the neighborhood on this:
I have a Form based on the table EVENTS, with a subform based on a
students/attendance query. I can look up an Event, then select students and
save the relevant data to the ATTENDANCE table. So far so good. What I would
like to do is add a second subform that lists the students that have been
scheduled for that event/date. I have added a subform in datasheet view, but
I cannot get the damned thing to refresh each time I add a student. It keeps
telling me the Form is closed and needs to be opened first. I'm sure that
there is a simple way to do this.....
Feeling-less-than-simple Andy
Klatuu!
You and I are going to be family before this is done! Yup, you confused the
hell out of me, but that doesn't mean I won't give it a shot! I'm all proud
of myself after writing the calendar module for this app; Input your
beginning fiscal month and day, click the button, and the code populates a
calendar table with the date, numeric day of the year, day name, fiscal month,
fiscal day of the year, boolean flag for workdays, and boolean flag for
holidays. Cool Stuff!
I THINK I get what you are talking about, but I need to try it to see. I will
be out on the road today, but I will be heavy into it tonight. I will let you
know how I make out each step, and I'm sure with your un-obsfucated
directions and my ability to speak SQLeasian we should be able to get it...
Talk to you later! :)
Andy
Since you know your SQL, I will leave that to you. What you want is a
Form/Sub Form configuration.
[quoted text clipped - 24 lines]
click to select each one, but any ideas would be greatly appreciated!!
Andy
 
S

serviceman via AccessMonster.com

Ah,
You didn't need it in front of you; I wasn't addressing it properly. Ok, so
now I have things almost working. I have on my schedule form ATTENDANCE_HDR
two subforms: ATTENDANCEENTRY and ATTENDANCELIST. ATTENDANCEENTRY is a single
form to select a student and add them to the schedule. ATTENDANCELIST is a
continuous form that lists each student as they are entered. I have on this
form a deleterecord button to remove a student added in error. What I need to
do now is get it so when the user clicks the delete button it deletes the
record and then sets the focus to the control Combo10 (forgot to rename that
one!) in subform ATTENDANCEENTRY. Can this be done?
Now, about that SSN...
Andy
What are you doing when it tells you the form is closed? This seems
strange. I am guessing your subform that is giving you problems is a sub
form of the EVENTS form? When this happens, are you in the other subform
where you add a student?

The only thing I an think of right now is that it may be how you are
addressing the other subform, but I think that would give you a different
error. Are you addressing it as Forms!MainFormName!OtherSubFormName.Requery?

If this is what you are doing in the subform where you add a student and it
is not working, try moving the requery to some event in the main form. You
may have to take some action that will make the main form the active form
before you do the requery.

Be advised this is all speculation. Without it in front of me, I can only
suggest possibilities.

If we are to be family, I will need your SSN for tax reporting purposes :)
I'm Baaaaaack.....
I'm in the neighborhood on this:
[quoted text clipped - 28 lines]
 
S

serviceman via AccessMonster.com

Hey Klatuu!
Got it!! All is working well now. I posted another thread about enabling a
button when data exists, and I'm sure I will have a couple other little
things as I go, but for the most part everything is running. Thanks again so
much for your help. I may have to send you that SSN after all.... :)
Andy
Ah,
You didn't need it in front of you; I wasn't addressing it properly. Ok, so
now I have things almost working. I have on my schedule form ATTENDANCE_HDR
two subforms: ATTENDANCEENTRY and ATTENDANCELIST. ATTENDANCEENTRY is a single
form to select a student and add them to the schedule. ATTENDANCELIST is a
continuous form that lists each student as they are entered. I have on this
form a deleterecord button to remove a student added in error. What I need to
do now is get it so when the user clicks the delete button it deletes the
record and then sets the focus to the control Combo10 (forgot to rename that
one!) in subform ATTENDANCEENTRY. Can this be done?
Now, about that SSN...
Andy
What are you doing when it tells you the form is closed? This seems
strange. I am guessing your subform that is giving you problems is a sub
[quoted text clipped - 20 lines]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top