Results 1 to 4 of 4

Thread: Improve code

  1. #1
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Talking Unanswered: Improve code

    Good morning,
    I have some code I've been using and it works fine but I need it to be more functional. The code is located in the Before Update event of a Quantity field.
    It opens a recordset and update the Units In Stock field from a different table when someone orders products. If the user tries to order more quantity than is in stock an error message appears and prevents the Units In Stock for changing. But I had a thought! What if the user (after closing the recordset) realizes they ordered to much or not enough of the product? If you go back to the same record, you can change the Quantity but it does not update the Units In Stock. I'm posting the code below. It's written in DAO ( I know some would call this DOA ) but it's an older script that I haven't converted yet.

    If anyone can figure out how to make Units In Stock update a second time on the same record, I'll put in a good word to Santa!!

    Thanks

    Private Sub Quantity_BeforeUpdate(Cancel As Integer)

    'This code opens a recordset in the Supplies Received table and edits/updates
    'the amount of Units In Stock. The update only takes place when the Quantity
    'ordered is less than the Units In Stock. If the Quantity ordered is
    'greater than the Units In Stock, a Warning message appears and the entry is
    'deleted without updating the Supplies Received table.

    On Error GoTo Error_Handler

    If Me.NewRecord Then
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SuppliesReceived", dbOpenTable)

    If Me.Quantity < Me.UnitsInStock Then

    rst.Index = "PrimaryKey"
    rst.Seek "=", Me!SupplyReceivedID
    rst.Edit
    rst("UnitsInStock") = rst("UnitsInStock") - Me!Quantity

    rst.Update
    rst.Close

    Set rst = Nothing
    Set dbs = Nothing

    Else
    MsgBox "The QUANTITY exceeds the amount of UNITS IN STOCK. " & _
    "This entry will be deleted!", vbCritical, "Quantity Error"
    Me.Undo

    End If
    End If

    Exit Sub

    Error_Handler:
    MsgBox Err.Description
    Exit Sub

    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, if they're adjusting down, you could trap that using:

    If YourControl.value < YourControl.OldValue

    If that's true then you could skip the code. This would work for adding quantity back into stock. if they're adjusting a quantity UP, and it exceeds onhand, I still don't see why you would let them append the changes.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Location
    Nashville
    Posts
    52

    Unhappy

    Hi Teddy,
    I think I understand what you're trying to tell me. But I'm not sure where in my current code you would insert this condition. Could you clarify this for me.

    If the user was adjusting the quantity up and the new quantity did exceed Units In Stock then I wouldn't want that update to occur but if the new quantity did not exceed the Unit In Stock then I would want the update to occur.

    Oh man, a light bulb just went off!

    Scenario:
    User orders 5 pencils and current Units In Stock show 10 are available
    leaving 5 pencils in stock.

    User goes back to record to change Quantity to 7 (adding two more pencils)but Units In Stock now shows 5 are available.

    I see what you mean. Bummer!

    Is there a way to rollback to the previous Units In Stock based on a condition?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ahhhh... I see what you mean. Perhaps you could lock already existing transactions and provide an "edit button" of sorts. If the transaction is in "edit" mode (set a boolean variable perhaps?), then your application knows this is pre-existing and to do the math accordingly.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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