Results 1 to 4 of 4
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Calculated Total from Report selection

    I have a report generator form that one of the many geniuses on here helped (did for me) me put together. (and I am embarrassed and apologize that I do not remember who it was that created this report generator)
    The form allows me to pick a from and To date and select the spending categories I want to look at and it pulls a report with totals spent for those categories between the dates (see images below)

    now the problem comes when I tried to make it into a Monthly financial report generator with the beginning and ending account balances
    For the beginning account balance I just took the total of all transactions for all dates before the "from" date for the report selection

    What I need is an "Adjusted Account Balance" which would be the total of all transactions for the categories selected in the report generator form



    please see images and code below

    Click image for larger version. 

Name:	Capture.PNG 
Views:	24 
Size:	88.3 KB 
ID:	47675


    Code:
    Function ClearList(lst As ListBox) As Boolean
    On Error GoTo Err_ClearList
        'Purpose:   Unselect all items in the listbox.
        'Return:    True if successful
        'Author:    Allen Browne. http://allenbrowne.com  June, 2006.
        Dim varItem As Variant
    
    
        If lst.MultiSelect = 0 Then
            lst = Null
        Else
            For Each varItem In lst.ItemsSelected
                lst.Selected(varItem) = False
            Next
        End If
    
    
        ClearList = True
    
    
    Exit_ClearList:
        Exit Function
    
    
    Err_ClearList:
        'Call LogError(Err.Number, Err.Description, "ClearList()")
        Resume Exit_ClearList
    End Function
    Code:
    Public Function SelectAll(lst As ListBox) As Boolean
    On Error GoTo Err_Handler
        'Purpose:   Select all items in the multi-select list box.
        'Return:    True if successful
        'Author:    Allen Browne. http://allenbrowne.com  June, 2006.
        Dim lngRow As Long
    
    
        If lst.MultiSelect Then
            For lngRow = 0 To lst.ListCount - 1
                lst.Selected(lngRow) = True
            Next
            SelectAll = True
        End If
    
    
    Exit_Handler:
        Exit Function
    
    
    Err_Handler:
        'Call LogError(Err.Number, Err.Description, "SelectAll()")
        Resume Exit_Handler
    End Function
    Code:
    Private Sub Command33_Click()
     On Error GoTo Err_cmdOpenReport_Click
    
    
      Dim strWhere      As String
      Dim ctl           As Control
      Dim varItem       As Variant
    
    
      'make sure a selection has been made
      If Me.TrAccList.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 Item"
        Exit Sub
      End If
    
    
      'add selected values to string
      Set ctl = Me.TrAccList
      For Each varItem In ctl.ItemsSelected
        strWhere = strWhere & ctl.ItemData(varItem) & ","
      Next varItem
      'trim trailing comma
      strWhere = Left(strWhere, Len(strWhere) - 1)
      'open the report, restricted to the selected items
      DoCmd.OpenReport "rpt_FinRptGenTOTAL", acPreview, , "[TransAccountID] IN(" & strWhere & ")"
    
    
    Exit_cmdOpenReport_Click:
      Exit Sub
    
    
    Err_cmdOpenReport_Click:
      MsgBox Err.Description
      Resume Exit_cmdOpenReport_Click
    End Sub
    Code:
    Private Sub GenReportBtn_Click()
     On Error GoTo Err_cmdOpenReport_Click
    
    
      Dim strWhere      As String
      Dim ctl           As Control
      Dim varItem       As Variant
    
    
      'make sure a selection has been made
      If Me.TrAccList.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 Item"
        Exit Sub
      End If
    
    
      'add selected values to string
      Set ctl = Me.TrAccList
      For Each varItem In ctl.ItemsSelected
        strWhere = strWhere & ctl.ItemData(varItem) & ","
      Next varItem
      'trim trailing comma
      strWhere = Left(strWhere, Len(strWhere) - 1)
      'open the report, restricted to the selected items
      DoCmd.OpenReport "rpt_FinRptGen", acPreview, , "[TransAccountID] IN(" & strWhere & ")"
    
    
    Exit_cmdOpenReport_Click:
      Exit Sub
    
    
    Err_cmdOpenReport_Click:
      MsgBox Err.Description
      Resume Exit_cmdOpenReport_Click
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The simplest way would be for the form to put each selected item into a table,say: tPicked.
    then all queries join this table to the main data table.

    thus the only code needed is to loop thru the list to add the items to this table.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi James,

    How do you get the Beginning Balance (upper right) on the report now? Is it a Dlookup (or DSum) in the textbox' control source property or is it calculated in the report's record source? If the former you can pass the strWhere variable to the report in its OpenArgs and add code in the report Open event to use it in the domain function's criteria. Or if the later you can save it into a tempvar or a global variable (returned by a function) and use it in your query.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Hi Gicu,

    the beginning Balance in the upper right corner is generated using

    =DLookUp("[SumOfTransAmount]","[qry_FinRptGenSUMBefore]").

    an you lost me at

    "you can pass the strWhere variable to the report in its OpenArgs and add code in the report Open event to use it in the domain function's criteria."

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-01-2020, 10:48 AM
  2. Calculated Field with Running Cummulative Total!
    By countingguru in forum Access
    Replies: 4
    Last Post: 06-22-2020, 08:36 AM
  3. Replies: 2
    Last Post: 09-17-2019, 08:38 AM
  4. Replies: 1
    Last Post: 10-12-2013, 04:06 PM
  5. Calculated Total with beginning Value
    By mcahal in forum Access
    Replies: 1
    Last Post: 01-03-2012, 11:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums