You'll have to do this in code, of course.
First, add to the form's module a procedure that can update QOH by whatever value you pass as a parameter:
Code:
Private Sub SubtractFromQOH(qty As Long)
Dim strSQL As String
If qty <> 0 Then
strSQL = "UPDATE Inventory SET QOH = QOH - " _
& qty & " WHERE PartID = " & PartID
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End If
End Sub
(Note: This assumes PartID is numeric. If it isn't, you need to modify the expression to concatenate apostrophes before and after the PartID value.)
Next, each time you navigate to a record (the form's Current event), save the QtyOut value in a module-level variable. For example:
Code:
Dim lngOriginalQtyOut As Long
Private Sub Form_Current()
lngOriginalQtyOut = Nz(QtyOut, 0)
End Sub
(The Nz() function here saves you a headache in case QtyOut is Null, such as when the user moves to an empty record at the end of the recordset.)
Then, when an update occurs (the form's AfterUpdate event), use the saved quantity to determine by how much QtyOut was changed (it could be positive or negative), and subtract that from QOH. For example,
Code:
Private Sub Form_AfterUpdate()
SubtractFromQOH Nz(QtyOut, 0) - lngOriginalQtyOut
End Sub
The deletion is a little more complicated, because you have to do it at different times depending on whether the Confirm Record Changes option is active. If it isn't, you have to use the form's Delete event, but if it is, you have to use the form's AfterDelConfirm event. To be safe, code it both ways and check the option at run time.
Code:
Private Sub Form_Delete(Cancel As Integer)
If GetOption("Confirm Record Changes") = False Then
SubtractFromQOH -Nz(QtyOut, 0)
End If
End Sub
Private Sub Form_AfterDelConfirm(Status As Integer)
If GetOption("Confirm Record Changes") = True _
And Status = acDeleteOK Then
SubtractFromQOH -Nz(QtyOut, 0)
End If
End Sub
Warning:
Do not use lngOriginalQtyOut instead of QtyOut in these two procedures. Access allows the user to change the QtyOut field before deleting the record. When this happens, the record will actually be updated first, then deleted. The update would invoke your AfterUpdate event to change QOH, and then the delete would invoke your Delete or AfterDelConfirm event to change it again. The sum of these two changes will be the total value of lngOriginalQtyOut, which is the amount by which you really want to change it. If you use lngOriginalQtyOut in the delete events, you could subtract too little or too much because of the extra AfterUpdate event occurring.
There's one very troublesome possibility to worry about: If, after updating or deleting the record, the user clicks the "Undo" button or menu item, the changes made by Access will be backed out--but any QOH change you made in code
won't be backed out. Furthermore, no event occurs which you could trap so that you could do the QOH backout yourself. This leaves you with an incorrect QOH. In my opinion, this is a serious flaw in Access' design. Microsoft either should have given us some way to detect the Undo button, or should have let us include our own updates in the form's transaction so they would also be backed out automatically.
There are only two ways I know of to avoid this problem, and they both take away the user's ability to undo the saved record. The first method is to remove the Undo command from the menu bar and toolbar (or, alternatively, use a form-specific menu bar and toolbar which omit Undo). The second method is to do something in code that causes the Undo Saved Record operation to become disabled. I prefer the latter method, and you can do it by changing the Form_Current event to this:
Code:
Private Sub Form_Current()
lngOriginalQtyOut = Nz(QtyOut, 0)
QtyOut = QtyOut
Me.Undo
End Sub
By doing a "fake" change to the QtyOut field, and then undoing the change, you leave the form in the same state it was in before you did the "fake" update,
except that this disables the Undo Saved Record operation.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein
