Using Combo to view info using Subforms

[Guest]

I have two forms. One if the main form and the other is a subform.

How do i make the subform change its information based on a change in the
value in the combo box.

For example:
in my main form, i have a combo box with lists each department.

When a different department is selected i want the information in the
subform to change accordingly.

Accounting Department in main form
List of all employees and employee inof in subform assocaited with Accouting.

MIS Department in main form
Lists all employees in that department along with assocated info.

How do i ties these two forms together to get what i need?
Thanks.

 

[Guest]

The main form is based on this query
SELECT department
FROM Department
ORDER BY department;

The subform is based on this query
SELECT Employee.SSN, Employee.HireDate, sum(sicktime.sicktime) AS
Total_SickTime, sum(personaltime.personaltime) AS Total_PersonalTime,
sum(vacationtime.vacationtime) AS Total_VacationTime
FROM ((Employee INNER JOIN personaltime ON
personaltime.employeeid=employee.employeeid) INNER JOIN vacationtime ON
Employee.EmployeeID=vacationTime.EmployeeID) INNER JOIN SickTime ON
Employee.EmployeeID=SickTime.EmployeeID
WHERE employee.employed=true
GROUP BY employee.SSN, Employee.hiredate;

Do i need to do something different? Thanks!

 

[Guest]

I'll make up some hypothetical names for your objects so my explanation will
be more clear:
-The main form: frmMain
-The subform: frmSub
-The department table: tblDept
-tblDept's primary key: DeptID

What you need first of all is to define which field links the frmMain and
frmSub. Since you want employee information in the subform for employees who
are in the department defined in the main form, the field that links the two
would be DeptID. So drop your subform in and change its Master Link Field
property to DeptID, and change its Child Link Field property to DeptID
(assuming your employee table calls the field that identifies the department
that an employee belongs to the same thing as the department table).

Now you need to be able to select which department to view employees from.
So make a combo box on your main form with its source as the department table.

This SHOULD make the subform update to display only employees who are in the
department selected in the main form's combo box.

Hope that helps.

 

[Guest]

When i try to display DepartmentID, in the master/Child link dialog box, i
don't see it.

So waht i tried to do was add the field to the query in my previous post,
but that doesn't seem to work. What happens is my combo box now contains
numbers instead of departments.

 

[Guest]

Disregard the last message. What i did was switched the field names around in
the query and i had no more problems. I have the Master and Child links
updated accordingly so far.

 

[Guest]

"Now you need to be able to select which department to view employees from.
So make a combo box on your main form with its source as the department
table. This SHOULD make the subform update to display only employees who are
in the department selected in the main form's combo box."

The Master/Child Link works. But when i change the department, it seems to
update the department for those employees. It does not cause different
employees to display their info.

i still have the original combo box which is bound to the query on the main
form.

Select department, departmentID
from Department

How do i let it know that i don't want to change the department but the
information in the subform.

Thanks very much!