Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Unfortunate side effect. Consider this revised code. Now if I can only figure out why MsgBox prompts twice on a new property.
    Code:
    Private Sub Form_Current()
    If Me.NewRecord Then
        If MsgBox("Do you want to create new Receipt record with Previous Balance?", vbYesNo + vbDefaultButton2) = vbYes Then
            With Me.RecordsetClone
                If .EOF Then
                    Me.Previous = Me.Parent.TotalPrice
                Else
                    .MoveLast
                    Me.Previous = !BalPay
                End If
            End With
        End If
    End If
    End Sub
    
    Private Sub PAmount_AfterUpdate()
    Dim rs As DAO.Recordset, dblBal As Double
    If Not Me.NewRecord Then
        If Me.Dirty Then Me.Dirty = False
        dblBal = Me.BalPay
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM Reciept WHERE PropertyID=" & Me!PropertyID & " AND RecieptID > " & Me.RecieptID & " ORDER BY RecieptID")
        rs.Edit
        Do While Not rs.EOF
            rs!Previous = dblBal
            rs.Update
            dblBal = rs!Previous - rs!PAmount
            If Not rs.EOF Then rs.MoveNext
        Loop
    End If
    Me.Refresh
    End Sub
    
    Or eliminate the Current event code:
    Code:
    Private Sub PAmount_AfterUpdate()
    Dim rs As DAO.Recordset, dblBal As Double
    If Not Me.NewRecord Then
        If Me.Dirty Then Me.Dirty = False
        dblBal = Me.BalPay
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM Reciept WHERE PropertyID=" & Me!PropertyID & " AND RecieptID > " & Me.RecieptID & " ORDER BY RecieptID")
        rs.Edit
        Do While Not rs.EOF
            rs!Previous = dblBal
            rs.Update
            dblBal = rs!Previous - rs!PAmount
            If Not rs.EOF Then rs.MoveNext
        Loop
    Else
        With Me.RecordsetClone
            If .EOF Then
                Me.Previous = Me.Parent.TotalPrice
            Else
                .MoveLast
                Me.Previous = !BalPay
            End If
        End With
    End If
    Me.Refresh
    End Sub
    There is no need to show PropertyID and RecieptID in textboxes on forms - these PK and FK fields have no meaning to users. If you do show them then set them as Locked Yes and TabStop No. Might also do same for Previous and BalPay if code works satisfactorily.

    I notice RecieptNum is a manually entered sequence. This can also be generated by code. Generating custom unique identifier is a common topic. However, instead of saving this value to table, it can be generated when need on a report where textbox has RunningSum property.

    Another misspelling: Catagory should be Category.

    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.

  2. #17
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why not update both Previous and BalPay fields inside the recordset loop, aren't they both going to be wrong after changing the PAmount?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #18
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    OP has BalPay as a Calculated type field in table. This was working just fine so I left it alone.
    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.

  4. #19
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Makes sense, thanks!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #20
    mshad is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Location
    Pakistan
    Posts
    9
    Quote Originally Posted by June7 View Post
    Unfortunate side effect. Consider this revised code. Now if I can only figure out why MsgBox prompts twice on a new property.
    Code:
    Private Sub Form_Current()
    If Me.NewRecord Then
        If MsgBox("Do you want to create new Receipt record with Previous Balance?", vbYesNo + vbDefaultButton2) = vbYes Then
            With Me.RecordsetClone
                If .EOF Then
                    Me.Previous = Me.Parent.TotalPrice
                Else
                    .MoveLast
                    Me.Previous = !BalPay
                End If
            End With
        End If
    End If
    End Sub
    
    Private Sub PAmount_AfterUpdate()
    Dim rs As DAO.Recordset, dblBal As Double
    If Not Me.NewRecord Then
        If Me.Dirty Then Me.Dirty = False
        dblBal = Me.BalPay
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM Reciept WHERE PropertyID=" & Me!PropertyID & " AND RecieptID > " & Me.RecieptID & " ORDER BY RecieptID")
        rs.Edit
        Do While Not rs.EOF
            rs!Previous = dblBal
            rs.Update
            dblBal = rs!Previous - rs!PAmount
            If Not rs.EOF Then rs.MoveNext
        Loop
    End If
    Me.Refresh
    End Sub
    
    Or eliminate the Current event code:
    Code:
    Private Sub PAmount_AfterUpdate()
    Dim rs As DAO.Recordset, dblBal As Double
    If Not Me.NewRecord Then
        If Me.Dirty Then Me.Dirty = False
        dblBal = Me.BalPay
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM Reciept WHERE PropertyID=" & Me!PropertyID & " AND RecieptID > " & Me.RecieptID & " ORDER BY RecieptID")
        rs.Edit
        Do While Not rs.EOF
            rs!Previous = dblBal
            rs.Update
            dblBal = rs!Previous - rs!PAmount
            If Not rs.EOF Then rs.MoveNext
        Loop
    Else
        With Me.RecordsetClone
            If .EOF Then
                Me.Previous = Me.Parent.TotalPrice
            Else
                .MoveLast
                Me.Previous = !BalPay
            End If
        End With
    End If
    Me.Refresh
    End Sub
    There is no need to show PropertyID and RecieptID in textboxes on forms - these PK and FK fields have no meaning to users. If you do show them then set them as Locked Yes and TabStop No. Might also do same for Previous and BalPay if code works satisfactorily.

    I notice RecieptNum is a manually entered sequence. This can also be generated by code. Generating custom unique identifier is a common topic. However, instead of saving this value to table, it can be generated when need on a report where textbox has RunningSum property.

    Another misspelling: Catagory should be Category.
    Great, Second code is working fine now. As for as PK and FK fields are concerned I will remove them from the forms now. Regarding ReceiptNum, we need it to be entered manually because we are still using manual receipts and we enter them in system later so we store those manual receipt numbers in this field. And thank you again for correcting my spellings and helping me out.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-07-2012, 05:31 PM
  2. Replies: 4
    Last Post: 03-30-2012, 01:47 AM
  3. Replies: 1
    Last Post: 11-29-2011, 01:37 AM
  4. Link between subform and mainform
    By crowegreg in forum Forms
    Replies: 2
    Last Post: 08-24-2011, 07:05 AM
  5. MainForm/SubForm question
    By 10 Gauge in forum Forms
    Replies: 5
    Last Post: 03-04-2011, 07:46 AM

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