Loop issues

[Gina Maylone]

Good morning! I postedthis earlier (I thought) but do not see it in the thread, sorry if it's aduplicate. I am trying to get the following procedure to post payments againstassessments (i.e. invoices) from oldest to newest, until the payment is used up.The first pass works perfectly (from the "First Loop" on) the"Exact Payment" loop will only work if I step through the code.Otherwise it is skipped and nothing posts. I'm totally confused on this. I'vetried wrapping the whole thing in a loop, it doesn't change anything. TIA

DoCmd.SetWarnings False

   Dim strsql As String
   Dim baldue As Currency
   Dim balleft As Currency
   Dim CRAmount As Currency
   Dim dbamount As Currency
   Dim totalbal As Currency
   Dim payment As Currency

   baldue = Me.baldue
   balleft = Me.balleft
   CRAmount = Me.CRAmount
   DBAmount = Me.DBAmount
   totalbal = Me.totalbal
   payment = Me.payment


   ' Add to payments
    DoCmd.RunSQL "INSERTINTO AsmtPayments (PaymentAmount, MemberID, PaymentDate ) " & vbCrLf& _
  "SELECT[Forms]![AccountsandPayments]![Accountssubform].[Form]![payment]ASExpr1,Accounts.MemberID,[Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid]AS Expr2" & vbCrLf & _
   "FROM Accounts "& vbCrLf & _
   "WHERE(((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID]));"

   DoCmd.SetWarnings True
   Me.Requery


  Forms![AccountsandPayments]![Accountssubform].SetFocus
 Forms![AccountsandPayments]![Accountssubform].Form![DateAssessed].SetFocus
   DoCmd.GoToRecord , , acFirst

       Debug.Print baldue
       Debug.Print Me.totalbal
       Debug.Print Me.CRAmount
       Debug.Print Me.DBAmount
       Debug.Print balleft
       Debug.Print Me.balleft =Me.totalbal
       Debug.Print Me.baldue> 0

       Do While Me.balleft >0
       MsgBox "balance leftisgreater than zero"


       '**********EXACTPAYMENT****

       While Me.balleft =Me.totalbal AndMe.totalbal > 0

       Debug.Print "meetsfirstcriteria of exact payment"
       If Me.CRAmount <>Me.DBAmountThen
       Me.CRAmount = Me.CRAmount+Me.balleft
       Me.balleft = 0

       End If
       DoCmd.GoToRecord , ,acNext

       Wend
       'uncomment these nextlines whenready
       'Me.DatePaid1 =Me.DatePaid
       'Me.PostedBy=[Forms]![AccountsandPayments]!user



       If Me.balleft = 0 Then
       Exit Sub
       End If
   '**********FIRSTLOOP************
       While Me.balleft >=Me.baldueAnd Me.baldue > 0
       Debug.Print "meetsfirst loopcriteria"
       Debug.Print Me.balleft
       Debug.Print Me.baldue

   Me.CRAmount = Me.DBAmount
   Me.baldue.Requery

       Debug.Print Me.CRAmount
       Debug.Print Me.DBAmount
       Debug.Print Me.baldue
       Debug.Print Me.balleft

   Me.balleft = Me.balleft -Me.CRAmount
   Me.balleft.Requery

   Debug.Print Me.balleft

   'uncomment these next lineswhen ready
   'Me.DatePaid1 = Me.DatePaid
   'Me.PostedBy =[Forms]![AccountsandPayments]!user
   DoCmd.GoToRecord , , acNext
   Wend
   '*******************

   '**********SECOND LOOP***
   While Me.balleft > 0 AndMe.balleft >=Me.baldue And Me.baldue > 0

   Debug.Print "meets thesecond loop criteria"
   Me.CRAmount = Me.CRAmount +Me.baldue
   Me.balleft = Me.balleft -Me.baldue
   'Me.DatePaid1 = Me.DatePaid
   'Me.PostedBy =[Forms]![AccountsandPayments]!user
   Me.balleft.Requery
   DoCmd.GoToRecord , , acNext
   Wend
   '************************

   '***********THIRD LOOP***
   While Me.balleft > 0 AndMe.baldue > 0 AndMe.CRAmount < Me.DBAmount

       Debug.Print "meetsthe secondloop criteria"
       Debug.Print Me.CRAmount<Me.DBAmount

   Me.CRAmount = Me.CRAmount +Me.balleft
   Me.balleft = Me.balleft -Me.CRAmount
   DoCmd.GoToRecord , , acNext
   Wend
   '************************

   '*****Overpayment creates anew credit record notrelated to a debit.
'    If Me.balleft > 0 AndMe.totalbal = 0 Then
'
'        Debug.Print Me.balleft
'        Debug.Print Me.baldue
'
'    DoCmd.GoToRecord , ,acNewRec
'    Me.DatePaid1 = Me.DatePaid
'    Me.PostedBy =[Forms]![AccountsandPayments]!user
'    Me.CRAmount = Me.balleft
'    Me.balleft = Me.balleft -Me.CRAmount
'    balleft = 0
'    If Me.Note = ""Then ''add a note toa new record only
'    Me.Note = "OverPmt" & "" & Me.DatePaid1 & " " &CheckNum
'    DoCmd.GoToRecord , , acNext
'    Wend
'    DoCmd.RunCommandacCmdSaveRecord
'
'  [Forms]![AccountsandPayments]![Accountssubform].Requery
'
'    balleft = 0
'    payment = 0
'    DatePaid = ""
'    CheckNum = ""
'    End If
'

   'Me.Requery
 '[Forms]![AccountsandPayments]![Accountssubform].Requery
'    If balleft > 0 And[Forms]![AccountsandPayments]![Accountssubform].[Form]!baldue= 0 Then
'    DoCmd.GoToRecord , ,acNewRec
'    Me.DatePaid = Me.DatePaid1
'    CRAmount = balleft ''(setthe CRAmount to theamount of money left
'    Me.Note = "OverPmt" & "" & Me.DatePaid
'    Me.CRAmount = balleft
'    Me.PostedBy =[Forms]![AccountsandPayments]!user

'    End If
   'Me.Requery

'    IfForms![AccountsandPayments]!amountdue = 0Then
'        MsgBox "Noassessment to addto"
'        Exit Sub
'    End If
   'CRAmount = DBAmount
   'DatePaid = D


'    AmountPaid_AfterUpdate
 'balleft = 0
'    payment = 0
'    DatePaid = ""
'    CheckNum = ""
 'new code
' Forms![AccountsandPayments]![Accountssubform].SetFocus
'  Forms![AccountsandPayments]![Accountssubform].Form![DateAssessed].SetFocus
'    DoCmd.GoToRecord , , acFirst
'
'   'If baldue > 0 AndCRAmount <> 0 And balleft>= baldue Then
'   While CRAmount < DBAmountAnd balleft >= baldueAnd baldue > 0 And DatePaid1 < Me.DatePaid
'
'
'   Debug.Print "BalDUE" & baldue
'   Debug.Print "Cramount" & CRAmount
'   Debug.Print "BalLeft" & balleft
'   Debug.Print "datepaid1" & DatePaid1
'   Debug.Print "datepaid" & DatePaid
'
'   balleft = balleft - baldue
'   CRAmount = DBAmount
'   Me.DatePaid1 = Me.DatePaid
'   Me.PostedBy =[Forms]![AccountsandPayments]!user
'   balleft = balleft - baldue
'   Me.balleft.Requery
'   DoCmd.GoToRecord , , acNext
'   Wend
  'End If

'    If balleft > 0 Andballeft <= baldue Andbaldue <> 0 Then
'    CRAmount = balleft ''set theCRAmount to theamount of money left
'    Me.DatePaid1 = Me.DatePaid
'    Me.PostedBy =[Forms]![AccountsandPayments]!user
'    Me.balleft.Requery
'    End If
'

'
'    balleft = 0
'    payment = 0
'    DatePaid = ""
'    CheckNum = ""


   'Me.Requery
  '[Forms]![AccountsandPayments]![Accountssubform].Requery
'    If balleft > 0And[Forms]![AccountsandPayments]![Accountssubform].[Form]!baldue = 0 Then
'    DoCmd.GoToRecord , ,acNewRec
'    Me.DatePaid = Me.DatePaid1
'    CRAmount = balleft ''(setthe CRAmount to theamount of money left
'    Me.Note = "Over Pmt"& "" & Me.DatePaid
'    Me.CRAmount = balleft
'    Me.PostedBy =[Forms]![AccountsandPayments]!user
'
   'End If
  ' Me.Requery

'    IfForms![AccountsandPayments]!amountdue = 0Then
'        MsgBox "Noassessment to addto"
'        Exit Sub
'    End If
   'CRAmount = DBAmount
   'DatePaid = D


'    AmountPaid_AfterUpdate
 'balleft = 0
'    payment = 0
'    DatePaid = ""
'    CheckNum = ""
Exit Do
Loop
End Sub

[davegri]

Wow, never thought of updating a recordsetclone by looping through it via its form! There's got to be a better way to do this.
If all the fields referred to as "Me.something" are in the form's recordsource, there's no reason to get the form involved like that.

Can you explain the big picture?
What fires this code off?
Is all that code supposed to update only the record just inserted?
What tables are involved here? Only one, or is it 2 tables such as a main table updated by a transaction table?

[Gina Maylone]

Hi davegri! Yes there is a better way to do this, with recordsets. I'm just not very adept at it. So I do what I can. The code right now is firing from a button, it will be firing from the form control "checknumber" The table that is being updated is "Accounts". There are several types of assessments, monthly, dam, roads, special. I am trying to be prepared for any scenario of payments. i.e. Paid In Fulls, Partial Pays, overages, etc. There is a Payments table that gets updated when a payment is entered too. BIG picture is it's assessments (invoices) and payments that I want to apply to the oldest assessments first.

I hope I've answered your questions - do you have any idea why the code works if I step through it but not when I don't?

Thank you!

[davegri]

Maybe this will help. Your outside loop will never repeat as you have an Exit Do before the loop.
Suggest you delete the Exit Do line if you actually want the loop to repeat.

Do While Me.balleft >0
.
.
.
Exit Do
Loop

[Micron]

- you should not have a variable and a field with the same name (Dim balleft and Me.balleft)
- you declare a variable for some fields (e.g.payment), yet use this long reference -
[Forms]![AccountsandPayments]![Accountssubform].[Form]![payment]
- you turn off warnings, but don't turn them back on. The Execute method of the DoCmd object is usually a better approach for running update type queries.

Difficult to make sense of what's going on as there are no notes. I see no reason why EXACTPAYMENT would run more than once since you make the condition of the first line to be False on the first pass (Me.balleft = 0) so I don't get the idea of a loop here. However, you're saying it won't execute the block at all unless you're stepping through? This would suggest some condition that is set before hand (such as a field update somewhere) doesn't have time to process and update your form before this loop is started.

Beyond that, it's difficult to trouble shoot without more clarity, but I suggest you fix some things first. BTW, not including the first line of your code means we can't tell what calls it (as noted). I also wonder if you have Option Explicit turned on, which could also be a contributing factor if you don't. Eliminating all the commented out part, plus indentation would make your code easier to read, which might reduce the errors or oversights we make. I also note that you have no spaces between some key words, so that suggests some manipulation after your copy/paste, which might also be contributing to over sight on our part. I have to assume that's not the way your code really looks as I'm sure it would error out when you compile it. Compiling is another thing I hope you're doing regularly.

[Gina Maylone]

Hi Micron - for some reason I am unable to copy and paste directly from the code window, so I used Word as a go between. Option Explicit is turned on and warnings were turned back on right after the SQL. I use the tool "Smart Indenter" I thought it knew what it was doing as far as indenting. I have renamed the variables, and the code compiles fine. The Exact Payment (in my scenario) has to find the 3rd record which is a balance due from a previous payment, thus I tried the loop, which loops 3 times when I step through it. Not any longer however, I'm getting an "Invalid use of null" on CR=me.cramount (cramount shows null upon hover as does dbamount) both fields have their default set to 0 in the table.

Thank you for taking the time to educate me :-)

Private Sub Command52_Click()
    DoCmd.SetWarnings False

        Dim strsql As String
        Dim bd As Currency
        Dim bl As Currency
        Dim cr As Currency
        Dim db As Currency
        Dim tb As Currency
        Dim P As Currency

        bd = Me.baldue
        bl = Me.balleft
        cr = Me.CRAmount
        db = Me.DBAmount
        tb = Me.totalbal
        P = Me.payment

        ' Add to payments
         DoCmd.RunSQL"INSERT INTO AsmtPayments ( PaymentAmount, MemberID, PaymentDate ) "& vbCrLf & _
        "SELECT [payment] ASExpr1, Accounts.MemberID,[Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid] AS Expr2" & vbCrLf & _
        "FROM Accounts " & vbCrLf &_
        "WHERE(((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID]));"

        DoCmd.SetWarnings True
        Requery

       Forms![AccountsandPayments]![Accountssubform].SetFocus
        Forms![AccountsandPayments]![Accountssubform].Form![DateAssessed].SetFocus
        DoCmd.GoToRecord , ,acFirst


            Do While bl > 0

            '**********EXACTPAYMENT****
            While bl >= tb Andtb > 0
            If cr <> db Then
            cr = cr + bl
            bl = 0

            End If
            DoCmd.GoToRecord , ,acNext
            Wend

            'uncomment these nextlines when ready
            'DatePaid1 = DatePaid
            'PostedBy =[Forms]![AccountsandPayments]!user



            If bl = 0 Then
            Exit Sub
            End If
        '**********FIRSTLOOP************
            While bl >= bd Andbd > 0
            Debug.Print"meets first loop criteria"
            Debug.Print bl
            Debug.Print bd
            Debug.Print cr
            Debug.Print db
            Debug.Print bd

        'uncomment these nextlines when ready
        'DatePaid1 = DatePaid
        'PostedBy =[Forms]![AccountsandPayments]!user
        DoCmd.GoToRecord , ,acNext
        Wend
        '*******************

        '**********SECOND LOOP***
        While bl > 0 And bl>= bd And bd > 0

        Debug.Print "meetsthe second loop criteria"
        cr = cr + bd
        bl = bl - bd
        'DatePaid1 = DatePaid
        'PostedBy =[Forms]![AccountsandPayments]!user

        DoCmd.GoToRecord , ,acNext
        Wend
        '************************

        '***********THIRD LOOP***
        While bl > 0 And bd> 0 And cr < db

            Debug.Print"meets the third loop criteria"
            Debug.Print cr <db

        cr = cr + bl
        bl = bl - cr
        DoCmd.GoToRecord , ,acNext
        Wend
        '************************

        '*****Overpayment createsa new credit record not related to a debit.
    '    If BL > 0 And totalbal = 0 Then
    '
    '        Debug.Print BL
    '       Debug.Print BD
    '
    '    DoCmd.GoToRecord , , acNewRec
    '    DatePaid1 = DatePaid
    '    PostedBy =[Forms]![AccountsandPayments]!user
    '    CR = BL
    '    BL = BL - CR
    '    BL = 0
    '    If Note = "" Then ''add a note toa new record only
    '    Note = "Over Pmt" & "" & DatePaid1 & " " & CheckNum
    '    DoCmd.GoToRecord , , acNext
    '    Wend

    Exit Do
    Loop
    End Sub

[pbaldy]

A variable declared as Currency can't accept a Null, only Variant can. You can either declare them as Variant and deal with potential Nulls or use the Nz() function:

CR=Nz(me.cramount, 0)

[Micron]

So you probably have the (at least current) fix courtesy of Paul.
FYI on "default set to 0 in the table". This only applies (exists/is committed) when a record is saved. While you will often see a defined default value while filling in other fields for a record, it isn't a "saved" value until the record is committed. In other words, you can see it, but it doesn't belong to the record yet, so it doesn't actually exist. Therefore you cannot refer to it and return said unsaved value, otherwise a null will be returned. If that's problematic, it might be necessary for you to step back and reconsider your approach.

I still don't get the idea of the loop.

The Exact Payment (in my scenario) has to find the 3rd record

Can you not just provide the criteria that would result in finding the 3rd record? If I get what you're doing (and that's unlikely) finding the nth record is wonky. What if the record order isn't what you expect?

[ssanfu]

It doesn't look like the SQL is correct. The MemberID should be concatenated in the WHERE clause:
What is printed in the immediate window if you run this?

    strsql = "INSERT INTO AsmtPayments ( PaymentAmount, MemberID, PaymentDate )" & _
        " SELECT [payment] AS Expr1, Accounts.MemberID,[Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid] AS Expr2" & _
        " FROM Accounts" & _
        " WHERE Accounts.MemberID = [Forms]![AccountsandPayments]![MemberID];"
        Debug.Print "G - " & strsql

        Debug.Print
        Debug.Print

    ' Add to payments
    strsql = "INSERT INTO AsmtPayments ( PaymentAmount, MemberID, PaymentDate )"
    strsql = strsql & " SELECT [payment] AS Expr1, Accounts.MemberID, [Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid] AS Expr2"
    strsql = strsql & " FROM Accounts"
    strsql = strsql & " WHERE Accounts.MemberID = " & [Forms]![AccountsandPayments]![MemberID] & ";"
        Debug.Print "S - " & strsql
Exit Sub

In either or both of the SQL strings,
is there a value for "Expr2"??
does the SQL look correct? (especially the WHERE clause)

[davegri]

Trying to get a feel for this.
Here are the assumptions:
The recordsource for the form is: SELECT * FROM Accounts ORDER BY MemberID, InvoiceDate DESC
It appears that the subform recordsource is AccountsAndPayments. If that's the case, you don't need the INSERT SQL at all. It will insert automatically when you complete the subform entry.

Then you need to apportion the payment to the Member's invoices, starting with the earliest.
If the payment exceeds the apportioned amounts due in the remaining invoices, create a credit record in AccountsAndPayments?

It would be helpful to see an image of your form in design mode, and an image of your relationships, showing all the fields (drag down the bottom until the scroll bar disappears).
Better yet, a copy of the DB.

[Gina Maylone]

Steve-I have renamed Expr1 etc. Here are my results of the debug.print, believe it or not, it appends the payments table fine.

G - INSERT INTO AsmtPayments ( MemberID, PaymentAmount, PaymentDate ) 
SELECT Accounts.MemberID,[Forms]![AccountsandPayments]![Accountssubform].[Form]![payment] AS PayAmt,[Forms]![AccountsandPayments]![Accountssubform].[Form]![DatePaid] AS PayDate 
FROM Accounts 
WHERE (((Accounts.MemberID)=[Forms]![AccountsandPayments]![MemberID]));

S - INSERT INTO AsmtPayments ( PaymentAmount, MemberID, PaymentDate )SELECT [payment] AS Expr1, Accounts.MemberID,[Forms]![AccountsandPayments]![Accountssubform].[Form]![datepaid] AS Expr2 FROMAccounts WHERE Accounts.MemberID = 1;

Thanks for the looksee!

[Gina Maylone]

Hi Paul - I did this (declared as variant) and it took care of the problem. Thanks so much!

[Gina Maylone]

Micron,

I am not looking for the 3rd record per se. The criteria is: if the balance left is greater than or equal to the total balance due and the total balance due is greater than 0 (some month has a balance) and if cramount does not equal dbamount (which means there was a partial payment previously)

While bl >= tb And tb > 0
            If cr <> dbThen

Thanks again!

[ssanfu]

Steve-I have renamed Expr1 etc. Here are my results of the debug.print, believe it or not, it appends the payments table fine.

Very strange. I would have expected it to bomb. Maybe it is because you are using "DoCmd.RunSQL" whereas I only use "CurrentDb.Execute strsql, dbFailOnError".

As far as I know:
Using "DoCmd.RunSQL", Access parses the SQL string before it is passed to the DBE and executed. (DBE = DataBase Engine)
(If Access parses the SQL string, maybe it resolves the Forms references.)
Using "CurrentDb.Execute strsql, dbFailOnError", the SQL string is passed to the DBE without being parsed (you have the responsibility of creating the correct SQL statement) executed.



I, also, would like to see the dB with some records and payment examples to be able to test the code.
I think using record sets in code would be faster/safer than stepping through form records.

[Gina Maylone]

NEWHAHDBforum.zip Here you go. I am going to give the recordset a shot. Will probably have more questions. Thank you all for your time!