Populate textbox with sum from query 1

[IknowMe]

I have query pulling from several tables in my DB. I have a form which allows the user to select different criteria to perform a sum based on their selections.

ex.

say QueryAll returns the following

Tool Usage Area SubArea UseDate
Awidget 12 UT SLC 01/01/2007
Bwidget 10 CA SF 01/01/2007
Awidget 5 UT SLC 01/12/2007

ect. ect.

On the form I have comboboxes for each criteria and a clander to select a start end date. Say sDate and eDate.

I then have a report Button (still working on report) with vba that I am trying to (for now) program to populate a text box with a sum of usage based on the users choices in the comboboxes

So assuming the textbox is UserSelectionSum what should the vba code to populate it look like? I would post the code I have but it's somewhat more complex than the example and contains information I can't divulge.

The code I have put together that isn't working looks something like this.

If vTool <> "" And vArea <> "" Then
     stReport = "SELECT Sum(Usage) AS [""Total""] FROM QueryAll WHERE Tool='" & vTool & "' AND Area='" & vArea & "'"
     If vSubArea <> "" Then
            stReport = stReport & " and SubArea='" & vSubArea & "'"
     End If

    If Me.stD <> "" And Me.Edat <> "" Then  'Dates selected
        stReport = stReport & " and ((([ComeTogether].Date)BETWEEN #" & Me.sDate & "# AND #" & Me.eDate & "#))"
    End If

Else
    MsgBox "select tool and area"
End If
    
Dim rs As Recordset
Dim db As Database
Set db = CurrentDb
    
Me.UserSelectionSum.Value = stReport

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

Perhaps this ?
Dim rs As DAO.Recordset
Dim db As Database
Set db = CurrentDb
Set rs db.OpenRecordset(stReport)
Me.UserSelectionSum.Value = rs(0)
rs.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IknowMe]

Worked great, well I added a char
Set = rs db.OpenRecordset(stReport)

Thanks again PHV

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.