Sum of hours using select statement & VBA  
Author Message
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Hi - ok here is my question:

I have an activity recording form that records hours logged against a company.

I have added a field called 'sumactivityhours' to the table and the form.  what i would like to do is when a user selects a company from the drop down list (thanks derek and duck thing for your fantastic help with that problem) it automatically populates the 'sumactivityhours' field with the total number of hours worked thus far with that company  so that the user can see the total hours and then writes that information into the 'sumactivityhours' field in the table.

as i gradually begin to learn a little more about VBA im guessing that i should create an on exit event on the companyname combo box that references the sumactivityhours field with a select statement but im unsure as to the correct syntax:

SELECT ((sum)[activityhours] FROM [tblactivity] WHERE [companyname] = the current value entered into the companyname combo box - but im unsure how to write this.

Anyone have any ideas,

Thanks,

Rhys.



Microsoft ISV Community Center Forums2  
 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

You've got it right. Your query will probably look something like

"SELECT SUM(activityhours) FROM [tblactivity] WHERE [companyname] = '" & Cstr(Me.ComboBox1.Value) & "'"



 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

ok - i get the message:

Run Time Error '2113':

The value you entered isnt valid for this field

The code i have is below:

Private Sub companyname_Exit(cancel As Integer)
Me.sumactivityhours.Value = "SELECT SUM(activityhours)FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'"
End Sub

where 'sumactivityhours' is the name of the field im trying to populate with the select statement - the error message tells me to debug the line of code - i take it there is a mistake in the Me.sumactivityhours.value part of the statement.

Any ideas

Thanks,

Rhys.


 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

The SELECT statement doesn't return a number -- it returns a table with one record which contains the SUM you've asked for. So I don't believe you can directly set sumactivityhours.Value to the query string. I believe you'll need to open a recordset using your SELECT query, grab the value from the sum field, then plug that value into the appropriate field.

I apologize for not being able to provide specific code; I don't have access to Access at the moment!



 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Thanks duck thing - i can see what youre saying - ill give it a go and see how far i get.


 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Hi duck thing - ive gotten this far, which im hoping is correct up until now but im now unsure as to how to take the value and place it into the appropriate field - 'sumactivityhours'

Private Sub companyname_Exit(cancel As Integer)
Dim rsdbase As Database
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'")

End Sub

Thanks,

Rhys.


 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Something like this should do it (inserted before your "End Sub") :

With rstemp
.MoveFirst
me.sumactivityhours = Format(CDbl(!activityhours),"#,##0.00") ' or whatever format you prefer
.Close
End With

Does that fly



 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Hi duck thing - i entered as you said so my statement is now:

Private Sub companyname_Exit(cancel As Integer)
Dim rsdbase As Database
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'")
With rstemp
.MoveFirst
Me.sumactivityhours = Format(CDbl(!activityhours), "#,##0.00")

.Close
End With

End Sub

but get the following message:

Run time error '3265':

Item not found in this collection

and it poins to the line: Me.sumactivityhours = Format(CDbl(!activityhours), "#,##0.00")

Any ideas

Thanks,

Rhys.


 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Sure. What's happening is that the new recordset you're viewing with your SELECT statement doesn't have a field called "activityhours". I'm not sure exactly what the field is called once it's summed up. What you can do is change your SELECT statement to include "SELECT SUM(etc...) INTO [SumActivityHours] (etc...)". Then change CDbl(!activityhours) to CDbl(!SumActivityHours) and you should be set.



 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Hi duck thing, i'm getting no joy with this at all! I changed the syntax as you said (se end of thread) but it doesnt like and says theres an invalid operatin on the SELECT line - im assuming its the into part - i didnt realise that it was possible to use INTO in a select statement. If i take the INTO statement out i then get the previous error 'item not found in this collection' and it points to the me.sumactivityhours =format line. I dont know what to do!

For clarity purposes i should explain the scenario a little better: the field 'activityhours' is the field the user enters the number of hours per activity againts a particular company, of which there could be many entries and therefore many hours. 'sumactivityhours' is the field on the same form (and same table) that I want to populate with the sum total of 'activityhours' values when a user selects the appropriate company. Im not very experienced with VBA as you can tell so i dont really know how to go and fix the problem - i understand that movefirst selects the first row in the recordset , and that should be the only row but im unsure as to what the format line does, and what the exclamation mark does

Any ideas

Many thanks for your time,

Rhys.

Private Sub companyname_Exit(cancel As Integer)
Dim rsdbase As Database
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)INTO [sumactivityhours] FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'")
With rstemp
.MoveFirst
Me.sumactivityhours = Format(CDbl(!sumactivityhours), "#,##0.00")
.Close
End With


 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Hi Rhys,

My appologies to duck thing for jumping in here... your SELECT statement is a tad wrong, I think your wanting an alias.

Change the SELECT statement to use AS instead of INTO...

SELECT SUM(activityhours) AS [sumactivityhours] FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'"

also I'd put the code in the companyname combo box AfterUpdate event (if it's driven from a combo box) rather than the Exit event.

The format statement takes a number and formats it into a string based on a format expression. The "#,##0.00" is a format expression... if you have to display currency then you can use "£##0.00" and the number is displayed as £5.90... look into the help for more information here as there is a lot to it.



 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Hi Derek and duck thing - that now works great except for one issue - if no hours have been logged against a company and you try to log an activity for the first time it gives the message 'Error 94: invalid use of null' and points to the Me.sumactivityhours = Format(CDbl(!sumactivityhours), "#,##0.00") - which i assume is because there has been no prior hours entered that can be summed up - where in the statement would i enter an IF statement to overcome this and would it be something like this

IF Me.sumacticityhours = "" Then

Me.sumactivityhours = 0

Thanks a lot guys,

Rhys.


 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

There is a nice obscure function in Access you can use to handle null conditions like the one your getting. It's called NZ()...

It's like an short if null statement... here's how you'd use it

Me.sumactivityhours = Format(Nz(!sumactivityhours, 0), "#,##0.00")

If !sumactivityhours is null then use 0 otherwise use the value of !sumactivityhours.



 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Sum of hours using select statement & VBA Top

Thanks duck thing and derek, that works perfectly - you guys are the best!

Rhys.