Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Problem with running balance

    My subform, sfmForm has these following fields . The columns, InvQty and ConsumedQty have their sums in the footer.


    ID EntryDate InvQty ConsumedQty RunBal
    1 20/06/2008 30 0 30
    2 24/06/2008 0 15 15
    3 03/07/2008 0 10 5
    4 10/07/2008 4 1 8
    ... Sum 34 26

    Users are allowed to enter or edit ConsumedQty in any record.

    I have the followings which work fine
    Private Sub txtConsumedQty_BeforeUpdate(Cancel As Integer)

    If txtConsumedQty < 0 Then
    MsgBox "Error in ConsumedQty"
    Me.Undo
    End If

    End Sub

    Private Sub txtConsumedQty_LostFocus()
    Dim lngkey As Long
    Dim rs As DAO.Recordset

    lngkey = ID

    Me.Requery

    If Nz(txtSumInvQty) < Nz(txtSumConsumedQty) Then
    MsgBox "Error in ConsumedQty"
    Set rs = RecordsetClone
    rs.FindFirst "[ID] = " & lngkey

    If rs.NoMatch Then
    MsgBox "Error Find Record " & lngkey
    Else
    Me.Bookmark = rs.Bookmark
    Me.txtConsumedQty.SetFocus
    End If
    End If

    Set rs = Nothing
    End Sub

    However, I do not want to get into a situation likes this


    ID EntryDate InvQty ConsumedQty RunBal
    1 20/06/2008 30 0 30
    2 24/06/2008 0 23 7
    3 03/07/2008 0 10 -3
    4 10/07/2008 4 1 0

    Sum 34 34

    Can somebody help me please?
    Last edited by big_mon; 05-01-09 at 09:27.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    What are you using to get the running total?
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Jan 2008
    Posts
    54
    I thought I could circumvent the problem by comparing the sum. For example,
    ID EntryDate InvQty ConsumedQty RunBal
    1 20/06/2008 30 0 30
    2 24/06/2008 0 28 2
    3 03/07/2008 0 10 -8
    4 10/07/2008 4 1 -5

    Sum 34 39

    However, under certain condition it is not a good test and does not work. So I hope someone like yourself may have a good idea how to test that Runbal is not negative when a user edits the ConsumedQty in any record.
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •