If..Then..Else when changing original quantities

[liltechy]

I am not sure of how to do this. Here we go, I have an inventory database the warehouse guy prints delivery tickets once he is through selecting the items, that works fine, but my problem is this; I was asked to create a form that would allow the warehouse guy to increase or decrease the QtyOut, well I created the form now I need to know

1. how to add the QtyOut back to the QOH if they decrease the original QtyOut
2. how to subtract from QOH if they increase the original QtyOut
3. how to add the QtyOut if they want to delete the item from the Delivery Ticket.

The fields on the edit form are PartID and QtyOut. I would appreciate any help.

liltechy

 

[RickSpr]

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:

    Private Sub SubtractFromQOH(qty As Long)
        Dim strSQL As String

        If qty <> 0 Then
            strSQL = &quot;UPDATE Inventory SET QOH = QOH - &quot; _
                & qty & &quot; WHERE PartID = &quot; & 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:

    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,

    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.

    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:

    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

 

[liltechy]

On what form event do I put the procedure to update the QOH by whatever value that is passed as a parameter?

liltechy

 

[RickSpr]

It is not an event procedure, it is a standalone procedure. You simply paste it into your form module. There's no need to hook it up to an event.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[liltechy]

Right after your first step you have this info:

(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.)

my PartID is text, but I don't understand where to concatenate. Can you please show me where to concatenate?

liltechy

 

[RickSpr]

... strSQL = "UPDATE Inventory SET QOH = QOH - " _
& qty & " WHERE PartID = '" & PartID & "'"

Apparently you're new to VBA programming?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[liltechy]

Your code is great, but when I decrease a qty it does not add the difference back to the QOH. PLEASE HELP.


liltechy

 

[liltechy]

Your code is great, but it only decreases the QOH, it does not increase the QOH when I decrease the QOH. Any help would be appreciated.


liltechy