recordset or ??

[karassik]

Access 2000

I have a form that sources a subform for employees hours etc. I would like to total the most recent days hours in the subform in an unbound field. I am having trouble figuring out how to use the current recordset of the subform for this.

dim db as database ,rst as recordset, DayHrs as single, CompDate as date
?set rst = current recordset?

DayHrs=0
CompDate = DMax(rst!HoursDate)
rst.movefirst

Do Until rst.EOF
if rst!HoursDate = CompDate then
DayHrs = DayHrs + rst!HoursDetail
end if
rst.movenext
Loop
Unbound control = DayHrs

Any ideas? Maybe I'm thinking about this wrong.

Nishan

 

[Steve101]

You're not really poviding enough information, so I'll assume that you wish to compute the sum of all the hours linked in the subform associated with the main form. One way to achieve this requires no code; let the Access engine do the work for you (I'm assuming here that the subform produces many records, associated with the main form link field(s)).

(a) In the subform design view, select the View, Form Header/Footer option, to display the Form Header/Footer section. Close up the Header Section (ie. drag up the detail band, so that the header occupies no spaces, as we wont be using it).

(b) In the footer section, add an unbound text control. Call it SumDayHrs. Set its control source property to the following:

=Sum(DayHrs)

I'm assuming here that you have included DayHrs as a control on the subform in the detail section.

This will then allow all entries in the subform to be cumulated and display in the new footer field. I'll assume that you have called this footer field SumDayHrs.

Note that you can access and display the above field in the main form if you like by referencing it by its name from the main form; ie.

YoursubFormName!SumDaysHrs

This is especially useful if your subform is displayed in datasheet view, and the footer does'nt display (although the calculation has happened, and the value is available).

Incidentally, relating to your initial post, and specifically to the line that you have marked:

?set rst = current recordset?

What you want here is the recordset underlying the subform, which is called the recordsetclone

If your code is to be running from the main form, then your set rst statement would simply be:

set rst = Forms!frmMainForm!frmSubForm.Form.RecordsetClone
rst.movefirst

Change the names of the form and subform in the above code as appropriate. After that the rest of what you have should run OK. Check online help to find out more about the recordsetclone object.

Hope this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Steve101]

Well I should have read the question properly in the first place, so I'll correct the first bit of my above post.

The line in the form footer should read:

=Sum(iif(HoursDate=CompDateDayHrs,DayHrs,0))

Providing the Hours are associated with the appropriate date, they should be included in the cumulattion field.

Hope this makes sense,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[karassik]

You helped with the :
=Sum(iif(HoursDate=CompDateDayHrs,DayHrs,0))

But I don't think I stated my question clearly.

So I want to sum only those hours that are the latest day entered, so I have something like:
=Sum(IIf([HoursDate]=DMax("[HoursDate]","qryHoursDetail","[EmployeeID]=" & [Forms]![frmEmployeeHours]![EmployeeID]),[HoursDetail],0))

It is giving me an #error

 

[Steve101]

Unable to get it working with the DMax function incorporated as you have it. Unsure why. You may have to resort to using code. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au