Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Question Unanswered: I Need to Total a group of cells from a user selectable query

    Ok, I'll try to explain this. I was able to get the query working with the help of Ghozy.
    The query runs through a report that allows the user to enter a serial number and select from a combobox one or more "years" in which to query. The query currently lists every record for each of the years selected. Now I need to be able to Total one of the columns (Total of Calls) so I get a grand total of money spent for the serial number on calls for the period selected.

    I've been able to use this code:
    SELECT Sum(tblBilling1999.[Total Cost of Call]) AS [Total 1999]
    FROM tblBilling1999;


    To total all records in a yearly table but want to add it to the function of the execute button on the form. The current button code is:

    Private Sub btnFireQuery_Click()
    Dim FilterStr As String
    'if serialnumber is empty, don't run query
    If IsNull(txtSerialNumber) Then Exit Sub
    'start creating SQL str
    FilterStr = Listbox2SQL(Years)
    'if any years selected then add AND keyword to SQL string
    If FilterStr <> "" Then FilterStr = "([Year] " + FilterStr + ") AND "
    'add serialnumber to SQL string
    FilterStr = FilterStr + "([InmarsatSerialNumber] LIKE '*" & CStr(txtSerialNumber) & "*')"
    'open our form with the filter
    DoCmd.OpenForm "frmQueryForm", , , FilterStr

    End Sub


    Thanks in advance.
    kc

    P.S. Ghozy, if you are ever in VA holler, I owe ya at least a couple of cold ones...
    Last edited by kccpo; 10-06-04 at 12:10.

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    here is my solution briefly, if you are interested I will explain it more tomorrow (not today because I'm at work at the moment). we could use frmQueryForm as a subform in the diaolog box form. instead of opening frmqueryform, we can just set filter for the subform. you may ask why go that way. simply that way you can add extra controls to show your sums and stuff.
    P.S.: I might be visiting Alexandria next month
    Last edited by ghozy; 10-06-04 at 12:30.
    ghozy.

  3. #3
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Ghozy,

    Definitely interested and tomorrow sounds great!!

    Thanks again for all of your help and patience.

    kc

  4. #4
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Ghozy,

    Alexandria isn't far from where I work. I don't know how familar you are with VA but I work in Falls Church and live about an hour south of there.

    Give me a holler if you are around.

    kc

  5. #5
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    ---------Duplicate Post Removed----------
    kc
    Last edited by kccpo; 10-06-04 at 13:07.

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    ok first of all we we will add frmQueryForm as a subform into our Dialog box. You can do that by adding a subform control and selecting an existing form for the control.

    and change btFireQuery code to this:
    Code:
    Private Sub btFireQuery_Click()
    Dim FilterStr As String
    	'if serialnumber is empty, don't run query
    	If IsNull(txSerialNumber) Then Exit Sub
    	'start creating SQL str
    	FilterStr = Listbox2SQL(lstYears)
    	'if any years selected then add AND keyword to SQL string
    	If FilterStr <> "" Then FilterStr = "([Year] " + FilterStr + ") AND "
    	'add serialnumber to SQL string
    	FilterStr = FilterStr + "([SerialNumber] LIKE *'" & txSerialNumber & "'*)"
    	'open our form with the filter
    	'DoCmd.OpenForm "QueryForm", , , FilterStr
     
    	'set filter options for the query subform and activate it
    	frmQueryForm.Form.Filter = FilterStr
    	frmQueryForm.Form.FilterOn = True
    End Sub
    what we do here setting filter options for our new subform, instead of opening it in a new window. at this point we are able to run our union query in a subform. now it is time to add some calculation stuff. I will calculate a total for Payment field in my query, you should change it whatever you want.

    now add a textbox into footer of the query subform. name it as TempPayment. Enter
    =Sum([Payment])
    into its Control Source. now we have a control that calculates Sum of Payment field. but it still won't show up in our form because it is in the footer section.

    To show it to user we will add another textbox into main form, and set its control source property to
    =[frmQueryForm].[Form]![TempPayment]

    now it will calculate the sum in footer and show in the main form. I will attach jpeg of my example, so you can figure where to put all controls.

    if you have any questions, just ask. because it is important you understand what I have done here.
    Attached Thumbnails Attached Thumbnails subformsnapshot.jpg  
    ghozy.

  7. #7
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Ghozy,

    Thanks again for the help. I'll run through this when I can. Stuff is really nuts today.

    kc

    P.S. Don't forget to let me know if you'll be in there area...I can pass on my email via PM if you want.

  8. #8
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Ghozy,

    Thanks for this. Got the time to get it together this morning and it looks great. Only drawback I see is that the subform query (frmQueryForm) runs as the main form is opened and returns all the records because the search parameters from the main form haven't been entered yet. Is there a way to stop this action from happening?

    Also is there a way to return an error code if no years are selected in the form (right now it lists all for that particular serial number). This isn't that big of deal but just need to try and make it so they can understand no matter what they do.

    Another thing I notice is that it opens the frmQueryForm when the main query is initiated just as if it were opened separately without the main form being run (full screen). Any way to turn this off.

    Thanks again for all of the help. The thing works and all I'm doing now is polishing so if you don't have time for this, no problem.

    kc
    Last edited by kccpo; 10-08-04 at 09:44.

  9. #9
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    #1 goto your subform's open event and add this line:
    Code:
    me.filter="[Year] is Null"
    me.filteron=true
    This will set filter to show only null Year records, which you would never have. Therefore it will show no data.

    #2 Replace btFireQuery click event code with this for warning user if no years selected.
    Code:
    Private Sub btFireQuery_Click()
    Dim FilterStr As String
    	'if serialnumber is empty, warn user
    	If IsNull(txSerialNumber) Then
    		MsgBox "Please enter a serial number."
    		Exit Sub
    	End If
    	'start creating SQL str
    	FilterStr = Listbox2SQL(lstYears)
    	'if no years selected warn user.
    	If FilterStr = "" Then
    		MsgBox "Please choose according years."
    		Exit Sub
    	End If
    	'add the field name and the AND keyword to SQL string
    	FilterStr = "([Year] " + FilterStr + ") AND "
    	'add serialnumber to SQL string
    	FilterStr = FilterStr + "([SerialNumber] LIKE *'" & txSerialNumber & "'*)"
    	'set filter options for the query subform and activate it
    	frmQueryForm.Form.Filter = FilterStr
    	frmQueryForm.Form.FilterOn = True
    End Sub
    if Listbox2SQL function returns an empty string that means no years selected, we inform the user and exit procedure.

    I hope this helps.
    Last edited by ghozy; 10-08-04 at 09:53. Reason: typo
    ghozy.

  10. #10
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98

    Talking

    Ghozy,

    I'll go give it a run. Thanks again for all of the help and instruction. This actually makes sense. Thanks for the patience.

    kc

  11. #11
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Ghozy,

    Still making changes but noticed the line of code:
    'DoCmd.OpenForm "QueryForm", , , FilterStr

    Was missing from the latest solution. Why don't we need it now?

    Thanks again.

    kc

  12. #12
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    because it is a remark line, we don't need it anymore. it was for opening old form. ' sign does same thing with REM keyword in visual basic and will be painted to green if you notice. basicaly anything starts with ' sign is actually a comment line.
    ghozy.

  13. #13
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    DUH!! I didn't even realize it was just a remark!!

    Going for more coffee.

    Works fantastically now. Again, thanks for the patience and help on this.

    If I don't talk to you again today, have a great weekend.

    kc

  14. #14
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you are welcome. you have a great weekend too. see you in Alexandria
    ghozy.

  15. #15
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    Ghozy,

    Now I get an "#Error" in the from textbox that is populated by the "TempPayment" subform.

    =Sum([Payment])


    =[frmQueryForm].[Form]![TempPayment]

    I'm sure it is because the value of input is Null because the search was disabled in the beginning of the procedure now.

    So in the Textbox that displays the total after the query and in the subform footer text box I tried =(Null) as an expression in the before update event and it still gives the error. Do I need to build an If statement instead? If so where do I put it?
    I'm thinking the statement would be similar to:
    If [TempPayment] = "" Then
    [TempPayment] = NULL
    Exit Sub
    End If

    Am I making Sense?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •