Using Combo boxes

[Guest]

I have two tables...Classes and Rooms. I have a form where I can add new
classes and assign it a room. The rooms never change, so I am using a combo
box to assign the classes to a room. But when I add a new class and select
a room from my combo box, it adds a new RoomID into my Rooms table along with
adding my new class.

How do I JUST add the class, and use the Room table without updating it??

 

[John Vinson]

I have two tables...Classes and Rooms. I have a form where I can add new
classes and assign it a room. The rooms never change, so I am using a combo
box to assign the classes to a room. But when I add a new class and select
a room from my combo box, it adds a new RoomID into my Rooms table along with
adding my new class.

How do I JUST add the class, and use the Room table without updating it??

Base the Form on your Classes table, not joining the Room table to it;
the Room field should be updated using a combo box based on Rooms, but
you needn't include the Rooms table on the form's recordsource in
order to do this!

John W. Vinson[MVP]

 

[Guest]

Okay, now I have a form where I assign the student to a class. I pass over
the name of the student from the AddStudent form.

I want to display all the classes that student is enrolled in, plus give the
option to add more classes.

I'm trying to do this using a subform with the ClassName in a combo box, but
I can't seem to get it to work....any ideas??

Thank you,
ivalum21

 

[John Vinson]

Okay, now I have a form where I assign the student to a class. I pass over
the name of the student from the AddStudent form.

I want to display all the classes that student is enrolled in, plus give the
option to add more classes.

I'm trying to do this using a subform with the ClassName in a combo box, but
I can't seem to get it to work....any ideas??

I'm assuming you have the traditional three tables - Students,
Classes, and the many-to-many resolver table Enrollment, right?

If so, use a Form based on the Student table; put on it a Subform
based on the Enrollment table. On this subform you'll want the combo
box based on Classes. Make the subform a Continuous Form so you can
see multiple rows for each class.

It's possible to do this using VBA code to open other forms, but it's
a LOT more work and harder on the user than a simple subform.

John W. Vinson[MVP]

 

[Guest]

Okay, now I want to create a form where I can go in and list if a student was
present during a particular day. So what I'm thinking is I create a form
with the date that is inputted by the user, then the user selects the
appropriate class. When the class is selected, all the students signed up
for that class appear. And next to them is a check box verify if they are
present on that day.

I think I have my thought process correct, but I don't know how to store the
individual dates in the database along with if the students were present?
Any more help you can provide me would be much appreciated...Mr. Vinson, you
have done a fantastic job thus far, thank you very much.

 

[John Vinson]

Okay, now I want to create a form where I can go in and list if a student was
present during a particular day. So what I'm thinking is I create a form
with the date that is inputted by the user, then the user selects the
appropriate class. When the class is selected, all the students signed up
for that class appear. And next to them is a check box verify if they are
present on that day.

I think I have my thought process correct, but I don't know how to store the
individual dates in the database along with if the students were present?
Any more help you can provide me would be much appreciated...Mr. Vinson, you
have done a fantastic job thus far, thank you very much.

Yet another table, Attendance, of course! Fields for StudentID,
ClassID, and date (defaulting to Date() perhaps) would work. Use a
Subform on the Class table, and a combo box for the studentID. You can
get a bit fancier and base the combo on a query joining the Students
table to the Enrollment table for that class to present a list of only
the enrolled students.

John W. Vinson[MVP]

 

[Guest]

Okay, now I have a new problem. When I go into my "Assign Student to a
Class" form, I have the student's name and the classes he/she is signed up
for. The classes are displayed in a combo box, but now I now have the same
class, offered at many different times. I tried adding the field
"SessionDate" (the start date of the class), but then I run into problems.
How do I distinguish between my class sessions when I go to assign a student
to a class?

 

[John Vinson]

Okay, now I have a new problem. When I go into my "Assign Student to a
Class" form, I have the student's name and the classes he/she is signed up
for. The classes are displayed in a combo box, but now I now have the same
class, offered at many different times. I tried adding the field
"SessionDate" (the start date of the class), but then I run into problems.
How do I distinguish between my class sessions when I go to assign a student
to a class?

A couple of ways to do this:

- Two tables, Classes and Sessions, in a one to many relationship;
you'ld enrol a student in a Session rather than a Class

- Add a Session field in the Enrollment table

John W. Vinson[MVP]

 

[Guest]

I'm having troubles with my "Assign A Student to a Class" form...

I have a form based on the Student table, and I need the subform to display
the classes that student is signed up for, along with the session. I want
the user to select the class, then given that information, have the Session
combobox populate only those times that class is offered. Right now my
subform is based on my Sessions table and my Classes table.

In my Sessions table I have the following fields: SessionID, DateOfSession,
TimeOfSession, ClassID and RoomID (both foreign keys). My Classes table has
ClassID and ClassName.

Thank you for your help,
ivalum21

 

[Guest]

Okay, now I have my subform based on the Sessions and Enrollments tables.
Then I have a combo box based on the Classes table. It displays all the
classes each student is signed up for, but when I go to add a new class, I
can't get it to display the sessionDates and sessionTimes given which classes
is selected in cboClassName.

 

[John Vinson]

Okay, now I have my subform based on the Sessions and Enrollments tables.

That's not what I suggested.

Then I have a combo box based on the Classes table. It displays all the
classes each student is signed up for, but when I go to add a new class, I
can't get it to display the sessionDates and sessionTimes given which classes
is selected in cboClassName.

"It"? the combo? the subform?

What's the rowsource of the combo? What's the Recordsource of the
subform?

John W. Vinson[MVP]

 

[Guest]

My form's record source is Students, my subform's record source is Sessions
and Enrollments. My combo box's control source is ClassID.

Sessions table's fields are SessionID, DateOfSession, TimeOfSession,
ClassID, and RoomID. My Enrollements table's fields are SessionID and
StudentID.

Right now this form displays the classes each student is signed up for. I
just need to add in the session variable to distinguish one class from the
rest.

- ivalum21

 

[Guest]

Okay, I added another combo box that displays the session date, but I need it
to display the correct session dates given what is selected in the ClassName
combo box...so Class A is offered at noon and 2:00, when I select Class A, I
want the Date combo box to only display 12:00 PM and 2:00 PM. Right now it
displays every date and time.

- ivalum21

 

[John Vinson]

Okay, I added another combo box that displays the session date, but I need it
to display the correct session dates given what is selected in the ClassName
combo box...so Class A is offered at noon and 2:00, when I select Class A, I
want the Date combo box to only display 12:00 PM and 2:00 PM. Right now it
displays every date and time.

Base the Sessions combo box on a query referencing the Class combo
box: use a criterion like

=[Forms]![MainFormName]![subformname].Form![ClassName]

using the name property of the Subform Control (which may or may not
be the same as the name of the form within that control).

You'll need to Requery the session combo in the AfterUpdate event of
the Class combo.

John W. Vinson[MVP]

 

[Guest]

Mr. Vinson -

My form named frmAssignStudentsToClass has a subform named
sfrmAssignStudentToClass. Inside my subform I have a combo box named
cboClass with the row source based on the following query:

SELECT Classes.ClassID, Classes.ClassName FROM Classes ORDER BY [ClassName];

The other combo box is named cboDay and its row source is based on this query:

SELECT Sessions.SessionID, Sessions.Day, Sessions.ClassID FROM Sessions
WHERE
(((Sessions.ClassID)=Forms!frmAssignStudentsToClass!sfrmAssignStudentsToClass!cboClass)) ORDER BY Sessions.Day;

I think I am following your suggestion correctly, but when I run my form, I
get an input box asking me to type in a value for
"Forms!frmAssignStudentsToClass!sfrmAssignStudentsToClass!cboClass"

Okay, I added another combo box that displays the session date, but I need it
to display the correct session dates given what is selected in the ClassName
combo box...so Class A is offered at noon and 2:00, when I select Class A, I
want the Date combo box to only display 12:00 PM and 2:00 PM. Right now it
displays every date and time.

Base the Sessions combo box on a query referencing the Class combo
box: use a criterion like

=[Forms]![MainFormName]![subformname].Form![ClassName]

using the name property of the Subform Control (which may or may not
be the same as the name of the form within that control).

You'll need to Requery the session combo in the AfterUpdate event of
the Class combo.

John W. Vinson[MVP]

 

[Guest]

I just caught the fact that I forgot the ".Form" part of your suggestion...I
added that in and I am still getting the same input box error.

Mr. Vinson -

My form named frmAssignStudentsToClass has a subform named
sfrmAssignStudentToClass. Inside my subform I have a combo box named
cboClass with the row source based on the following query:

SELECT Classes.ClassID, Classes.ClassName FROM Classes ORDER BY [ClassName];

The other combo box is named cboDay and its row source is based on this query:

SELECT Sessions.SessionID, Sessions.Day, Sessions.ClassID FROM Sessions
WHERE
(((Sessions.ClassID)=Forms!frmAssignStudentsToClass!sfrmAssignStudentsToClass!cboClass)) ORDER BY Sessions.Day;

I think I am following your suggestion correctly, but when I run my form, I
get an input box asking me to type in a value for
"Forms!frmAssignStudentsToClass!sfrmAssignStudentsToClass!cboClass"

Okay, I added another combo box that displays the session date, but I need it
to display the correct session dates given what is selected in the ClassName
combo box...so Class A is offered at noon and 2:00, when I select Class A, I
want the Date combo box to only display 12:00 PM and 2:00 PM. Right now it
displays every date and time.

Base the Sessions combo box on a query referencing the Class combo
box: use a criterion like

=[Forms]![MainFormName]![subformname].Form![ClassName]

using the name property of the Subform Control (which may or may not
be the same as the name of the form within that control).

You'll need to Requery the session combo in the AfterUpdate event of
the Class combo.

John W. Vinson[MVP]