Results 1 to 8 of 8
  1. #1
    Gina Maylone is offline Always learning
    Windows 10 Access 2016
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544

    Loop is over writing previous value


    Hello all, this question is about a database which I have posted many questions on this forum. It is running nearly perfectly, however, this code which posts payments is over writing the previous check number. For example if a payment was made yesterday and the check number was 1644, and another payment is made today with check # 1647, 1644 will become 1647 on all records. I cannot tell where, or how to stop it from doing that. Any help is always greatly appreciated!

    Code:
    Private Sub monthlyCheckNum_AfterUpdate()
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strsql As String
        Dim CN As String
        Dim BD As Variant
        Dim BL As Variant
        Dim CR As Currency
        Dim P As Currency
        Dim mbrid As Long
        Dim dp As Date
        Dim u As String
        Dim C As String
        
        '    Dim RC As Long
        'get values from sub form
        dp = Me.MonthlyDatePaid
        u = Forms![memberpayments]![dbuser]
        CN = Forms![memberpayments]![MonthlyPayments].Form![monthlyCheckNum]
        'debug.print u
        
        mbrid = Forms![memberpayments]![MemberID_PK]
        P = Me.Monthlypayment
        BL = P  '<<-- payment amount
        'open recordset
        strsql = "SELECT MonthlyQueryforPayments.MemberID_FK, MonthlyQueryforPayments.DBAmount, MonthlyQueryforPayments.CRAmount,"
        strsql = strsql & " MonthlyQueryforPayments.dbdate, MonthlyQueryforPayments.AsmtType, MonthlyQueryforPayments.Details,"
        strsql = strsql & " MonthlyQueryforPayments.LotNumber, MonthlyQueryforPayments.EnteredBy, MonthlyQueryforPayments.crdate,"
        strsql = strsql & " MonthlyQueryforPayments.CheckNumber, MonthlyQueryforPayments.comments, [dbamount]-[cramount] AS baldue"
        strsql = strsql & " FROM MonthlyQueryforPayments"
        strsql = strsql & " WHERE MonthlyQueryforPayments.MemberID_FK = " & [Forms]![memberpayments]![MemberID_PK]
        'strsql = strsql & " And ([DBAmount] - [CRAmount]) > 0"
        
        strsql = strsql & " ORDER BY MonthlyQueryforPayments.dbdate;"
        Set rst = CurrentDb.OpenRecordset(strsql)
        'check for records in recordset
        If Not rst.BOF And Not rst.EOF Then
            rst.MoveLast
            rst.MoveFirst
            Do While Not rst.EOF
    
                'get values from each record in recordset
                BD = rst!baldue
                CR = rst!CRAmount
    
                'edit or add a payment to the record set
      
                If BL < BD Then   'less than
                    rst.Edit
                    rst!CRDATE = dp
                    rst!CRAmount = BL + CR
                    rst!CheckNumber = CN
                    rst!EnteredBy = Forms![memberpayments]![dbuser]
                    rst!Comments = dp & " - " & CN
                    rst.Update
                    BL = 0
                ElseIf BL > BD Then  'greater than
                    rst.Edit
                    rst!CRDATE = dp
                    rst!CRAmount = BD + CR
                    rst!CheckNumber = CN
                    rst!EnteredBy = Forms![memberpayments]![dbuser]
                    rst!Comments = dp & " - " & CN
                    rst.Update
                    BL = BL - BD
                ElseIf BL = BD Then  'equal to
                    rst.Edit
                    rst!CRDATE = dp
                    rst!CRAmount = BD + CR
                    rst!CheckNumber = CN
                    rst!EnteredBy = Forms![memberpayments]![dbuser]
                    rst!Comments = dp & " - " & CN
                    rst.Update
                    BL = 0
                End If
                rst.MoveNext
            Loop
    
            '---- extra left over after making payments ----
            'save remaining amt to additional principal in last records of record set
            If BL > 0 Then
                rst.MoveLast
                rst.Edit
                rst!CRAmount = rst!CRAmount + BL
                rst!CheckNumber = CN
                rst!Comments = dp & " - " & CN
                rst!EnteredBy = Forms![memberpayments]![dbuser]
                rst.Update
                BL = 0
            End If
    
            'add payment to payments table
            strsql = "INSERT INTO AsmtPayments ( MemberID_FK, [asmttype], PaymentAmount, PaymentDate, CheckNumber, enteredby )"
            strsql = strsql & " VALUES ( " & mbrid & ", '" & AsmtType & "'," & P & ", #" & dp & "#, '" & CN & "', '" & u & "');"
    
            
    DoCmd.OpenQuery "UpdateCRDATEStoNull"
        DoCmd.OpenQuery "balfwd"
        DoCmd.OpenQuery "cleanupoverpayments"
        DoCmd.OpenQuery "DeleteZeroLines"
        Forms![memberpayments]![AsmtPayments].Form.Requery
    Forms![memberpayments]![AsmtPayments].Form.Refresh
        Me.Refresh
    CurrentDb.Execute strsql, dbFailOnError
        End If
    
        rst.Close
        Set rst = Nothing
        
        
        Forms![memberpayments]![MonthlyPayments].Form.Requery
        Forms![memberpayments]![AsmtPayments].Form.Requery
        
        
        'MsgBox "End of process"
    
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you step debugged?

    I would have to examine db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Add debug lines or use message boxes to chec each strsql statement and The CN value in each section of your if statement.
    Step though your code checking for where it overwrites the existing value
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Gina Maylone is offline Always learning
    Windows 10 Access 2016
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    Hey Ridders, I have stepped through it and debugged over and over. It happens on each level.

    Upon further investigation, all of the values are over-written. So confused.

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you can zip and upload your database with instructions, I'm sure someone will look at it for you.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The block of code below (from your procedure):
    Code:
                'edit or add a payment to the record set   
                If BL < BD Then   'less than
                       rst.Edit
                       rst!CRDATE = dp
                       rst!CRAmount = BL + CR
                       rst!CheckNumber = CN
                       rst!EnteredBy = Forms![memberpayments]![dbuser]
                       rst!Comments = dp & " - " & CN
                       rst.Update
                       BL = 0
                ElseIf BL > BD Then  'greater than
                       rst.Edit
                       rst!CRDATE = dp
                       rst!CRAmount = BD + CR
                       rst!CheckNumber = CN
                       rst!EnteredBy = Forms![memberpayments]![dbuser]
                       rst!Comments = dp & " - " & CN
                       rst.Update
                       BL = BL - BD
                ElseIf BL = BD Then  'equal to
                       rst.Edit
                       rst!CRDATE = dp
                       rst!CRAmount = BD + CR
                       rst!CheckNumber = CN
                       rst!EnteredBy = Forms![memberpayments]![dbuser]
                       rst!Comments = dp & " - " & CN
                       rst.Update
                       BL = 0
                End If

    is making the EXACT SAME changes to each record in your recordset, regardless of the values of BL and BD, so each record in the recordset is having the check number reset.

    (You are not adding a new record, even though the comment says "edit or add")

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    is making the EXACT SAME changes to each record in your recordset
    Are you sure? I'm seeing only 1 change to rst, though it is a change.
    rst!CRAmount = BL + CR
    rst!CRAmount = BD + CR
    this code which posts payments is over writing the previous check number.
    If you get the cn from the form, and that comes from a Select query, then are you not simply writing the existing cn to your recordset? What ensures that the cn has been changed on the form? If the form is bound to your query and user changes the cn, the value likely gets written into the table for the old record - probably not what you want. As noted, the most important thing may be what John detected - you are modifying a record, not adding one.

    EDIT
    Wondering if you can't eliminate all the duplication (below for update, not appending to rst)...
    Code:
    Do While Not rst.EOF
    'get values from each record in recordset
      BD = rst!baldue
      CR = rst!CRAmount
      rst.Edit
      rst!CRDATE = dp
      rst!CheckNumber = CN
      rst!EnteredBy = Forms![memberpayments]![dbUser]
      rst!Comments = dp & " - " & CN
      If BL < BD Then   'less than
        rst!CRAmount = BL + CR
        BL = 0
      ElseIf BL > BD Then  'greater than
        rst!CRAmount = BD + CR
        BL = BL - BD
      ElseIf BL = BD Then  'equal to
        rst!CRAmount = BD + CR
        BL = 0
      End If
      rst.Update
      rst.MoveNext
    Loop
    Last edited by Micron; 09-26-2018 at 11:37 AM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gina Maylone is offline Always learning
    Windows 10 Access 2016
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    Thank you all for your input! And especially to Steve S for helping me with this from the beginning. The customer has once again changed their mind , so I will be going a different direction. OMG the hours I can never get back Sorry to have wasted all of your time as well. ~PEACE~

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  2. Replies: 1
    Last Post: 11-14-2016, 10:36 AM
  3. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  4. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  5. Replies: 17
    Last Post: 04-07-2014, 07:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums