Updating subsequent records in a recordset

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
No one ever helped me with this. I tried several different types of loops and
then forced it to loop without using any of the traditional loop syntaxes. I
titled my beginning (where I declared my variables) "Update:" and then put

If Not rstChecking.EOF Then
rstChecking.MoveNext
frmChecking![BeginningBalance] = curEndBal
GoTo Update
End If

just before

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

It now works great. I hope my solution will help someone else who may face
similar difficulties in future.

--Catherine

Catherine said:
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
Click to expand...
 
Back
Top