Subform not visable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a subform Frm_Sub_Hol_Cancel_Stat that gets its value from a query
Qry_Hol_Cancel_Stat. The query selects cancelled holiday's from Tbl_Hol_Taken
for a selected employee and returns SumOfHol_Hours. If the employee has not
cancelled any holidays the form is not visable but it displays the correct
number of hours when cancelled holiday's are found.

I would like the sub form to display 0 where no cancelled holiday's are found.

Is this possible ?

Thanks in advance for your help.
 
Kevin,

Yes, it is possible. The specifics of how to do it will depend on the
details of what you've got there. Can you post back with the SQL view
of your Qry_Hol_Cancel_Stat query, and also let us know whether the
subform only shows the total hours, or whether it lists all cancelled
holidays for the employee and calculates the total hours in the form
footer of the subform? Thanks.
 
Hi Steve,

Here is the code from the query,

SELECT Sum(Tbl_Holidays_Taken.Hol_Hours) AS SumOfHol_Hours,
Tbl_Holidays_Taken.Hol_Type, Tbl_Holidays_Taken.Hol_Cancel,
Tbl_Holidays_Taken.Hol_Emp_ID
FROM Tbl_Holidays_Taken
GROUP BY Tbl_Holidays_Taken.Hol_Type, Tbl_Holidays_Taken.Hol_Cancel,
Tbl_Holidays_Taken.Hol_Emp_ID
HAVING (((Tbl_Holidays_Taken.Hol_Type)=2) AND
((Tbl_Holidays_Taken.Hol_Cancel)=Yes) AND
((Tbl_Holidays_Taken.Hol_Emp_ID)=[Forms]![Frm_Holiday_Summary]![Employee]));

As far as the sub form goes, all it display's is the SumOfHoliday_Hours.

Thanks in advance
 
Kevin,

Thanks for the further information.

One simple approach would be to trash the subform altogether, and put an
unbound textbox on the form, with its Control Source set to...
=Nz(DLookup("[SumOfHol_Hours]","Qry_Hol_Cancel_Stat"),0)

Is that acceptable?
 
Steve,

This works but it takes a long time to display the figures after I select
the employee.

Is there another solution I can try ?

Thanks in advance
Steve Schapel said:
Kevin,

Thanks for the further information.

One simple approach would be to trash the subform altogether, and put an
unbound textbox on the form, with its Control Source set to...
=Nz(DLookup("[SumOfHol_Hours]","Qry_Hol_Cancel_Stat"),0)

Is that acceptable?

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

Here is the code from the query,

SELECT Sum(Tbl_Holidays_Taken.Hol_Hours) AS SumOfHol_Hours,
Tbl_Holidays_Taken.Hol_Type, Tbl_Holidays_Taken.Hol_Cancel,
Tbl_Holidays_Taken.Hol_Emp_ID
FROM Tbl_Holidays_Taken
GROUP BY Tbl_Holidays_Taken.Hol_Type, Tbl_Holidays_Taken.Hol_Cancel,
Tbl_Holidays_Taken.Hol_Emp_ID
HAVING (((Tbl_Holidays_Taken.Hol_Type)=2) AND
((Tbl_Holidays_Taken.Hol_Cancel)=Yes) AND
((Tbl_Holidays_Taken.Hol_Emp_ID)=[Forms]![Frm_Holiday_Summary]![Employee]));

As far as the sub form goes, all it display's is the SumOfHoliday_Hours.

Thanks in advance
Click to expand...
Click to expand...
 
Kevin,

This is surprising. It shouldn't take any longer than the subform
approach, as far as I can see. I would try putting a line of code like
this on an event associated with however it is you select an employee...
Me.Recalc

What is the Record Source of the main form here? Is this form just for
display of data, or do you use it for entry/editing of data? If you
don't require it to be updateable, you could incorporate the totals
query into the query that the main form is based on.

Another approach would be to make a subform, but based on a query like
this...
SELECT Tbl_Holidays_Taken.Hol_Hours, Tbl_Holidays_Taken.Hol_Emp_ID
FROM Tbl_Holidays_Taken
WHERE ((Tbl_Holidays_Taken.Hol_Type=2) AND
(Tbl_Holidays_Taken.Hol_Cancel=Yes))

Make it a continuous view subform. Set the subform's Link Master Fields
and Link Child Fields ptroperties to EmployeeID (or your equivalent).
In the Footer section of the subform, put an unbound textbox, and set
its Control Source property to...
=IIf(Count(*)=0,0,Sum([Hol_Hours]))
Set the Visible property of the Detail section and the Header section to No.

Another approach would be to make another query, something like this...
SELECT Tbl_Emloyees.EmployeeID, Nz([SumOfHol_Hours],0) As HolTotal
FROM Tbl_Employees LEFT JOIN Qry_Hol_Cancel_Stat ON
Tbl_Employees.EmployeeID = Qry_Hol_Cancel_Stat.Hol_Emp_ID
WHERE Tbl_Emloyees.EmployeeID=[Forms]![Frm_Holiday_Summary]![Employee]

.... and then use this query as the basis of the subform similar to your
original form design.
 
Steve,

I inserted the code Me.Recalc and the values appear instantly.

Thanks very much for your help.
Kevin

Steve Schapel said:
Kevin,

This is surprising. It shouldn't take any longer than the subform
approach, as far as I can see. I would try putting a line of code like
this on an event associated with however it is you select an employee...
Me.Recalc

What is the Record Source of the main form here? Is this form just for
display of data, or do you use it for entry/editing of data? If you
don't require it to be updateable, you could incorporate the totals
query into the query that the main form is based on.

Another approach would be to make a subform, but based on a query like
this...
SELECT Tbl_Holidays_Taken.Hol_Hours, Tbl_Holidays_Taken.Hol_Emp_ID
FROM Tbl_Holidays_Taken
WHERE ((Tbl_Holidays_Taken.Hol_Type=2) AND
(Tbl_Holidays_Taken.Hol_Cancel=Yes))

Make it a continuous view subform. Set the subform's Link Master Fields
and Link Child Fields ptroperties to EmployeeID (or your equivalent).
In the Footer section of the subform, put an unbound textbox, and set
its Control Source property to...
=IIf(Count(*)=0,0,Sum([Hol_Hours]))
Set the Visible property of the Detail section and the Header section to No.

Another approach would be to make another query, something like this...
SELECT Tbl_Emloyees.EmployeeID, Nz([SumOfHol_Hours],0) As HolTotal
FROM Tbl_Employees LEFT JOIN Qry_Hol_Cancel_Stat ON
Tbl_Employees.EmployeeID = Qry_Hol_Cancel_Stat.Hol_Emp_ID
WHERE Tbl_Emloyees.EmployeeID=[Forms]![Frm_Holiday_Summary]![Employee]

.... and then use this query as the basis of the subform similar to your
original form design.

--
Steve Schapel, Microsoft Access MVP

Steve,

This works but it takes a long time to display the figures after I select
the employee.

Is there another solution I can try ?
Click to expand...
Click to expand...
 
Back
Top