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
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