Do Until... Loop (1 Viewer)

[Rob.Mills]

This is the first time I'm setting up my own loop code. What I'm trying to do with this form is create a check register that adds up the deposits and payments. I was able to make this code work in the debug window using the recordset of the underlying table. My challenge is occuring when I'm trying to make it work in the form:

Public Function LoopRecordset()
Dim db As Database, rst As Recordset, intb As Long
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("frm", dbOpenForm) ' This is where the errors occuring.
Do Until rst.EOF
If IsNull(rst!txtPayment) Then
intb = intb + rst!txtDeposit
Forms!frm!txtBalance = intb
rst.MoveNext
Else
intb = intb - rst!txtPayment
Forms!frm!txtBalance = intb
rst.MoveNext
End If
Loop
End Function

I know I've set the rst variable wrong but don't know how to correct it.

Suggestions

 

[Travis]

Public Function LoopRecordset() 
  Dim db As Database, rst As Recordset, intb As Long 
    Set db = DBEngine(0)(0) 
    Set rst = frm.RecordsetClone  'Use a Clone of the Forms Recordset
    rst.MoveFirst  'Make sure you are on the first record
    Do Until rst.EOF 
      If IsNull(rst!txtPayment) Then 
        intb = intb + rst!txtDeposit 
        Forms!frm!txtBalance = intb 
        rst.MoveNext 
      Else 
        intb = intb - rst!txtPayment 
        Forms!frm!txtBalance = intb 
        rst.MoveNext 
      End If 
    Loop 
End Function

 

[Cosmos75]

Why won't a textbox on the form with a formula (as the control source) work?

Is there some kind of term what I just described?

 

[Jeff Bailey]

Or, cutting out some repitition (which might make it a bit easier to follow) ...

Public Function LoopRecordset()
Dim db As Database, rst As Recordset, intb As Long
Set db = DBEngine(0)(0)
Set rst = frm.RecordsetClone 'Use a Clone of the Forms Recordset
rst.MoveFirst 'Make sure you are on the first record
Do Until rst.EOF
If IsNull(rst!txtPayment) Then
intb = intb + rst!txtDeposit
Else
intb = intb - rst!txtPayment
End If
Forms!frm!txtBalance = intb
rst.MoveNext
Loop
End Function

Since the

Forms!frm!txtBalance = intb
rst.MoveNext

part is the same in both bits of the 'If ... Then'
you can put it after the 'End If'.

Doesn't make much difference here, but can make life easier when you have more complex stuff.

Jeff

 

[Rob.Mills]

Changed code to:

set rst=frm.recordsetclone

Now when I run it I get Run-time error '424': Object Required
then highlights the code listed above.

 

[Rob.Mills]

Alright... changed the code to:

set rst=Forms!frm.recordsetclone

That eliminated the error

But now instead of displaying the correct balance on each record it has the sum of all records displayed in each record.

 

[Cosmos75]

What fields are on your form?

Is it a tabular or columnar form?

Are you tring to show totals or Price*Quantity for each record or for all records shown on form (is the form filtered in some way)?

 

[Rob.Mills]

It's a continuous form with three fields in each record (Payment, Deposit, and an unbound textbox for the balance)

What I want to happen is say there's a starting balance of $100.

If in the next record I enter a payment of $50 then the balance in that record should show $50. Kind of like what you would see in Quicken or Microsoft Money.

 

[Rich]

=([OpeningBalance])+Sum([Deposits])-Sum([Payments])