Hi there - im finalising my new activity system but i have one issue.
on an activity form i have certain fields for information purposes that are non editable - they are 'hours worked with client' , 'hours worked this month' and 'hours worked for the day'. The first 2 fields are populated automatically by opening recordsets on form load or companyname exit and summing up the hours based on the relevant query where the criteria ensures that the query is already populated.
However for the 'hours worked for the day' field the query is a parameter query (qrytodayhours) where activitydate = Forms![frmactivity]![activitydate] and i was then hoping to enter the following code to sum up the hours using a recordset on the on exit of the activitydate field:
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)AS [sumtodayhours] FROM [qrytodayhours] WHERE [leadofficer]='" & loginname & "'")
With rstemp
.MoveFirst
'Me.sumtodayhours = Format(Nz(!sumtodayhours, 0), "#,##0.00") ' or whatever format you prefer
.close
End With
If i dont enter this code in and just enter a date into the form and then open the query then i can see that the parameter query works because i get the correct information in the query. However i cant get it to sum the hours up and enter it into the field 'sumtodayhours' as i can with the other fields mentioned at the start.
The error message i get when i exit the 'activitydate' field is 'Too few parameters. Expected 1'.
Do i need to use a querydef to open the recordset or is there something i am missing
Any ideas would be gratefully appreciated.
Many thanks,
Rhys.
Summing up hours on form based on parameter query
daniel.youlussusskind
Hi ADG, i put the code on the on exit event of activitydate on frmactivity and the focus is set to activitydate on load, so i then enter the date and tab out and then get that error message. Entering a default date value of today also results in the same error. is there something wrong with my recordsetr coding - should i be using querydef
Many thanks,
Rhys
Simon Dahlbacka
Hi ADG, thanks for your help but I couldnt get that to work. However after working on it all day i have managed to almost get it to work(!) with the code below using querydef, however if i have not entered any hours for that day and the query is then empty it doesnt like the .movefirst etc onwards. How can i account for the first entry of the day i.e. when the query has no values
Many thanks,
Rhys.
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Dim qdf As QueryDef
Set qdf = rsdbase.QueryDefs("qrytodayhours")
qdf.Parameters("Date") = Me!activitydate
qdf.Parameters("Officer") = Me!leadofficer
Set rstemp = qdf.OpenRecordset()
With rstemp
.MoveFirst
Me.sumtodayhours = Format(Nz(!sumtodayhours, 0), "#,##0.00") ' or whatever format you prefer
.close
End With
AdamB78
Hi
Is the Form frmactivity open and a date selected If not you will see this error. If the current form is frmactivity make sure that there is an activity date as soon as it opens.
Regards
ADG
Jeff Weber
Hi
I am able to duplicate your problem on my machine, still don't know why the error occurs, but you can work round it. Write the query in one go in the query design, switch the view to SQL the copy the text into a string in your code. Create the recordset from the sql string e.g.
sqlstr = "SELECT TRANSACTIONS.DATE, Sum(TRANSACTIONS.VALUE) AS SumOfVALUE FROM Transactions GROUP BY TRANSACTIONS.DATE " & _
"HAVING (((TRANSACTIONS.DATE)=" & Me.SelectedDate & "));"
Set rs = CurrentDb().OpenRecordset(sqlstr)
ShawnT123
Hi ADG - got it to work this way early this morning but your code would have worked also.
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Dim qdf As QueryDef
Set qdf = rsdbase.QueryDefs("qrytodayhours")
qdf.Parameters("Date") = Me!activitydate
qdf.Parameters("Officer") = Me!leadofficer
Set rstemp = qdf.OpenRecordset()
If rstemp.EOF Then
Me.sumtodayhours = "0" 'Format(Nz(!sumtodayhours, 0), "#,##0.00")
Else
With rstemp
.MoveFirst
Me.sumtodayhours = Format(Nz(!sumtodayhours, 10), "#,##0.00")
.close
End With
End If
Thanks for your help.
Rhys.
adimit99
Hi
Your can use:
If rstemp.recordcount >0 then ....
Note recordcount does not return the true record count until you move to the last record, but if there are records in the recordset it is always greater than zero