Form and Subform issue

[Guest]

A form and subform. They are both linked.

I want to use the main form to determine what is to be displayed in the
subform.

For example, if i select a department from a combo box, i want the list of
employees and employee info to display.

Right now, if i change he departmetn in the combo box, it changes the
department for all the active or current employees. THis is not waht i want.

How do i change this behavior? thanks!

 

[Guest]

Create a filter in the SubForm Record source that refer to the combo in the
main form

Select * From TableName Where department = Forms![MainFormName]![department
ComboName]

On the after update event of the department combo you need to refresh the
data in the sub form

Me.[SubFormName].Requery

 

[Guest]

Under Form on the ALL tab i see the word Filter. I don't know if this is what
you mean. However you said to create it in the subform RECORD SOURCE. I have
no idea how to do that unless it is a query. And i already have a query in
the subform Record Source:

SELECT Employee.SSN, Employee.HireDate, Employee.DepartmentID,
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, Employee.DepartmentID;


Is there something this process is called so i can look it up on the
internet. I have a book but it does not go into details about this that i can
tell.

Thanks! Please explain a little more for me. Thanks!

Create a filter in the SubForm Record source that refer to the combo in the
main form

Select * From TableName Where department = Forms![MainFormName]![department
ComboName]

On the after update event of the department combo you need to refresh the
data in the sub form

Me.[SubFormName].Requery

--
Good Luck
BS"D

A form and subform. They are both linked.

I want to use the main form to determine what is to be displayed in the
subform.

For example, if i select a department from a combo box, i want the list of
employees and employee info to display.

Right now, if i change he departmetn in the combo box, it changes the
department for all the active or current employees. THis is not waht i want.

How do i change this behavior? thanks!

 

[Guest]

If this query used for this form only, add a filter to it for the DepartmentID.

SELECT Employee.SSN, Employee.HireDate, Employee.DepartmentID,
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

AND Employee.DepartmentID = Forms![MainFormName]![DepartmentID ComboName]

GROUP BY employee.SSN, Employee.hiredate, Employee.DepartmentID;

==========================
In the combo After update event write the code to refresh the query data

Me.[SubFormName].Requery

--
Good Luck
BS"D

Under Form on the ALL tab i see the word Filter. I don't know if this is what
you mean. However you said to create it in the subform RECORD SOURCE. I have
no idea how to do that unless it is a query. And i already have a query in
the subform Record Source:

SELECT Employee.SSN, Employee.HireDate, Employee.DepartmentID,
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, Employee.DepartmentID;


Is there something this process is called so i can look it up on the
internet. I have a book but it does not go into details about this that i can
tell.

Thanks! Please explain a little more for me. Thanks!

Create a filter in the SubForm Record source that refer to the combo in the
main form

Select * From TableName Where department = Forms![MainFormName]![department
ComboName]

On the after update event of the department combo you need to refresh the
data in the sub form

Me.[SubFormName].Requery

--
Good Luck
BS"D

A form and subform. They are both linked.

I want to use the main form to determine what is to be displayed in the
subform.

For example, if i select a department from a combo box, i want the list of
employees and employee info to display.

Right now, if i change he departmetn in the combo box, it changes the
department for all the active or current employees. THis is not waht i want.

How do i change this behavior? thanks!

 

[Guest]

Thanks but i'm still trying to make this work and it is not working.

instead of putting the code directly in the Record source field, i have it
saved as a query first.

The main form's name is fDepartment
The subform's name is fsubEmployeeTotals
The combo name seems to be Department, the same as the DB field name.

here is what is in the query:

SELECT Employee.SSN, Employee.HireDate, Employee.DepartmentID,
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

And Employee.DepartmentID=Forms!fDepartments![DepartmentID department]
GROUP BY employee.SSN, Employee.hiredate, Employee.DepartmentID;

This doesn't work though. Noting appears in the subform and i get error
messages if i try to move the recordset arrows at the bottom. Personally, i
don't see where the recordset arrows should be there.

Thanks!

 

[Guest]

How can i learn more on how to do this. Because i will not know why I'm
doing this or how to duplicate teh results with something similar. It may
also help explain what i need to be doing.

Thanks!