Subform receiving a value from a combo box value on the main form [Office Newsgroups Reader]

> On Wed, 31 Dec 2008 09:32:05 -0800, Ms. DE Velop Her <MsDEVelopHer[ at ]discussions.microsoft.com> wrote:
>
> >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.
>