Subform receiving a value from a combo box value on the main form

[Ms. DE Velop Her]

Hello There!,

I have a main Form (frmClasses) which allows user search via a comboBox
(cbxSearch) on a list of classes from a table (tblClasses)

frmClasses also displays the date/time schedule of the class in other
textboxes (txtDate / txtTime)

Then there is a SubForm(sfClassAttend) which is suppose to display all
employees - including those from the table of scheduled attendees for each
scheduled class (tblSchedAttend)

I need to be able for the user to Search from the Main form (frmClasses) on
the combobox (cbxSearch) and have the SubForm(sfClassAttend) display All
Employees...those from the table(tblEmployes) who may not all be scheduled
for a Class and also those from the table (tblSchedAttend) who are already
scheduled for a Class.
Via the SubForm,
The User should be able to:
See a list of all employees and if they are scheduled for the selected Class
or not

also...

Select the YES/NO button from the (tblSchedAttend) on the subform
(sfClassAttend) and update an employee to the table (tblSchedAttend)as an
employee NOW scheduled to attend that particular class.

Currently, when I launch the Main Form (frmClasses), all employees are
displayed in the (sfClassAttend), when I select a Class from the dropdown
list (cbxSearch)
the subform displays all the employees with the YES/NO button populated if
they are registered for that class, yet if I select another class from the
list, the subform still shows the list with the same employees attendance.

Please help

 

[Michael Gramelspacher]

Hello There!,

I have a main Form (frmClasses) which allows user search via a comboBox
(cbxSearch) on a list of classes from a table (tblClasses)

frmClasses also displays the date/time schedule of the class in other
textboxes (txtDate / txtTime)

Then there is a SubForm(sfClassAttend) which is suppose to display all
employees - including those from the table of scheduled attendees for each
scheduled class (tblSchedAttend)

I need to be able for the user to Search from the Main form (frmClasses) on
the combobox (cbxSearch) and have the SubForm(sfClassAttend) display All
Employees...those from the table(tblEmployes) who may not all be scheduled
for a Class and also those from the table (tblSchedAttend) who are already
scheduled for a Class.
Via the SubForm,
The User should be able to:
See a list of all employees and if they are scheduled for the selected Class
or not

also...

Select the YES/NO button from the (tblSchedAttend) on the subform
(sfClassAttend) and update an employee to the table (tblSchedAttend)as an
employee NOW scheduled to attend that particular class.

Currently, when I launch the Main Form (frmClasses), all employees are
displayed in the (sfClassAttend), when I select a Class from the dropdown
list (cbxSearch)
the subform displays all the employees with the YES/NO button populated if
they are registered for that class, yet if I select another class from the
list, the subform still shows the list with the same employees attendance.

Please help

Normally I might have table like this:

CREATE TABLE Classes (
ClassID TEXT(10) NOT NULL,
ClassName TEXT(100) NOT NULL,
CONSTRAINT pk_Classes PRIMARY KEY (ClassID)
);
CREATE TABLE Employees (
EmployeeID TEXT(10) NOT NULL,
EmployeeName TEXT(50) NOT NULL,
CONSTRAINT pkEmployees PRIMARY KEY (EmployeeID)
);
CREATE TABLE EmployeeClasses (
ClassID TEXT(10) NOT NULL
CONSTRAINT fk_Classes_EmployeeClasses
FOREIGN KEY (ClassID) REFERENCES
Classes (ClassID)
ON UPDATE CASCADE
ON DELETE CASCADE,
EmployeeID TEXT(10) NOT NULL
CONSTRAINT fk_Employees_EmployeeClasses
FOREIGN KEY (EmployeeID) REFERENCES
Employees (EmployeeID)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT pk_EmployeeClasses
PRIMARY KEY (ClassID, EmployeeID)
);

Classes will be the main form.
EmployeeClass will be the first subform linked to main form ClassID/ClassID.
Employees will be the second subform.

Main form Current event changes the recordsource of the
Employees subform to show employees not in the current class.

Dim s As String

s = "Select * From Employees WHERE NOT EXISTS" & _
"(SELECT * FROM EmployeeClasses WHERE EmployeeClasses.EmployeeID = " & _
"Employees.EmployeeID AND EmployeeClasses.ClassID = '" & _
Nz(Me.ClassID) & "');"

Me.Employees.Form.RecordSource = s

Whatever class is current, EmployeeClasses subform shows the roster for that
class, and Employees subform shows all employees not enrolled in that class.

Now you can add a command button to add employees to the class (Insert query)
and a command button to drop students from class (Delete query).

You need to requery the EmployeeClasses subform and the Employees subform
after each insert and delete action.

You can also have a combobox in the form header to select a class and change
the main form recordsource in the AfterUpdate event. You can also have a command
button to clear the combobox and change the main form's recordsource to show
all classes.

 

[Ms. DE Velop Her]

OOOOOOOH another sub Form, brilliant.

I will try this next year...Friday,
Have A Happy New Year

 

[Michael Gramelspacher]

OOOOOOOH another sub Form, brilliant.

I will try this next year...Friday,
Have A Happy New Year

And just to give you something to look at now:
http://www.psci.net/gramelsp/temp/EmployeeClassesDemo.zip

 

[Ms. DE Velop Her]

Okay, I took all of your code and demo suggestions , it works like a charm!!!!
Have a little more tweaking to do but I'm GOOD!

I REALLY appreciate your help !!!!
You ARE the MAN!

Thanks for your valuable time