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.