G
Guest
I am adding a checking account register to a database I’m creating. I want to update the Ending Balance on one record and copy it to the Beginning Balance on the next record.
I also want to have any changes on existing records to update the ending and beginning balances on all the records that come after them (e.g. Editing a transaction from earlier this month should change all the balances that follow that transaction.)
*Question: I think I should be incorporating a loop into this code to go to EOF, but I'm sure which type of loop or where to place it. Can someone help me with that, please?
Currently, the code below copies whatever is the ending balance on the current record to the beginning balance on a new record, unless a new record is not created (see section at end of code following recordset creation and reading). I would like the ending balance to update on each subsequent screen and then give the final total to the next record for the beginning balance.
Private Sub UpdateBalances()
Dim rstChecking As DAO.Recordset
Dim frmChecking As Access.Form
Dim strTransactionType As String
Dim strTransactionTypeLast As String
Dim curBegBal As Currency
Dim curTransAmt As Currency
Dim curEndBal As Currency
Dim blnIncrease As Boolean
Dim blnDecrease As Boolean
On Error Resume Next
blnIncrease = cbxIncrease
blnDecrease = cbxDecrease
strTransactionType = Me.cboTransactionType
curBegBal = Me.txtBeginningBalance
curTransAmt = Me.txtTransactionAmount
Select Case strTransactionType
Case "Bank Fee", "Check", "Debit Card", "Withdrawal"
curEndBal = curBegBal - curTransAmt
Case "Adjustment"
If blnIncrease = True And blnDecrease = False Then
curEndBal = curBegBal + curTransAmt
ElseIf blnDecrease = True And blnIncrease = False Then
curEndBal = curBegBal - curTransAmt
ElseIf blnIncrease = False And blnDecrease = False Then
MsgBox "Please mark ""Increase Account Balance"" or ""Decrease Account Balance."""
Exit Sub
ElseIf blnIncrease = True And blnDecrease = True Then
MsgBox "You cannot increase and decrease your account balance in the same transaction. Please choose only ""Increase Account Balance"" or ""Decrease Account Balance."""
Exit Sub
End If
Case "Deposit", "Interest Earned"
curEndBal = curBegBal + curTransAmt
End Select
Set frmChecking = Forms!frmChecking
Set rstChecking = frmChecking.RecordsetClone
rstChecking.Edit
rstChecking![EndingBalance] = curEndBal
frmChecking![EndingBalance] = curEndBal
DoCmd.GoToRecord acActiveDataObject, Record:=acLast
strTransactionTypeLast = Me.cboTransactionType
If Not strTransactionTypeLast = "" Then
rstChecking.AddNew
rstChecking![BeginningBalance] = curEndBal
End If
rstChecking.Update
Set rstChecking = Nothing
Set frmChecking = Nothing
curEndBal = 0
curBegBal = 0
I also want to have any changes on existing records to update the ending and beginning balances on all the records that come after them (e.g. Editing a transaction from earlier this month should change all the balances that follow that transaction.)
*Question: I think I should be incorporating a loop into this code to go to EOF, but I'm sure which type of loop or where to place it. Can someone help me with that, please?
Currently, the code below copies whatever is the ending balance on the current record to the beginning balance on a new record, unless a new record is not created (see section at end of code following recordset creation and reading). I would like the ending balance to update on each subsequent screen and then give the final total to the next record for the beginning balance.
Private Sub UpdateBalances()
Dim rstChecking As DAO.Recordset
Dim frmChecking As Access.Form
Dim strTransactionType As String
Dim strTransactionTypeLast As String
Dim curBegBal As Currency
Dim curTransAmt As Currency
Dim curEndBal As Currency
Dim blnIncrease As Boolean
Dim blnDecrease As Boolean
On Error Resume Next
blnIncrease = cbxIncrease
blnDecrease = cbxDecrease
strTransactionType = Me.cboTransactionType
curBegBal = Me.txtBeginningBalance
curTransAmt = Me.txtTransactionAmount
Select Case strTransactionType
Case "Bank Fee", "Check", "Debit Card", "Withdrawal"
curEndBal = curBegBal - curTransAmt
Case "Adjustment"
If blnIncrease = True And blnDecrease = False Then
curEndBal = curBegBal + curTransAmt
ElseIf blnDecrease = True And blnIncrease = False Then
curEndBal = curBegBal - curTransAmt
ElseIf blnIncrease = False And blnDecrease = False Then
MsgBox "Please mark ""Increase Account Balance"" or ""Decrease Account Balance."""
Exit Sub
ElseIf blnIncrease = True And blnDecrease = True Then
MsgBox "You cannot increase and decrease your account balance in the same transaction. Please choose only ""Increase Account Balance"" or ""Decrease Account Balance."""
Exit Sub
End If
Case "Deposit", "Interest Earned"
curEndBal = curBegBal + curTransAmt
End Select
Set frmChecking = Forms!frmChecking
Set rstChecking = frmChecking.RecordsetClone
rstChecking.Edit
rstChecking![EndingBalance] = curEndBal
frmChecking![EndingBalance] = curEndBal
DoCmd.GoToRecord acActiveDataObject, Record:=acLast
strTransactionTypeLast = Me.cboTransactionType
If Not strTransactionTypeLast = "" Then
rstChecking.AddNew
rstChecking![BeginningBalance] = curEndBal
End If
rstChecking.Update
Set rstChecking = Nothing
Set frmChecking = Nothing
curEndBal = 0
curBegBal = 0