Delete record on a subform using vba

[aardvark]

Hi all,

I am trying to delete a record on a subform under the following conditions: Client is entering inventory transactions and it is possible for the transaction to cause the onhand quantity to go below zero. I am flagging this transaction, and correcting the inventory, but I need to be able to delete the current transaction record on a subform. I am using the following vba code:

If Me.TransType = 2 Then
OnHand1 = OnHand1 - Qty
If OnHand1 < 0 Then
Response = MsgBox("On hand below acceptable level", vbOKOnly, "On Hand")
OnHand1 = OnHand1 + Qty
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
GoTo Quantity_AfterUpdateExit
End If
Else
OnHand1 = OnHand1 + Qty
End If

And I get the following error message:

Runtime error 2046, The command or action 'Delete Record' isn't available now.

Any and all suggestions are welcome. TIA.

Bill K.

[HansV]

Where does this code run? From the main form or from the subform?

[aardvark]

It from from an AfterUpdate event on the subform.

[HansV]

A quick test proved that it is possible to delete a record during an After Update event, so there must be something specific to your situation that prevents it. Could you create a stripped down copy of your database (take care to remove or edit sensitive information), compact and zip it, and attach the zip file to a reply?

[aardvark]

Hans:

On the subform, need to enter a UPC Number (14799717667). The AfterUpdateEvent is on the quantity control.

[HansV]

I can't get a finger behind the cause of the problem (sorry!) but what you're doing is very risky: if the OnHand quantity doesn't get below zero, you already update the value of OnHand in qryCurrentInventory before the record has been saved. If the user cancels the record by pressing Esc, the updated value is not valid.

Therefore, I wouldn't use the After Update event of Quantity, but the Before Update and After Update events of the form:

Code: Select all

Private Sub Form_AfterUpdate()
    Dim strFilter As String
    Dim db As Database
    Dim rstOut As Recordset
    
    Set db = CurrentDb
    Set rstOut = db.OpenRecordset("qryCurrentInventory")

    strFilter = "UPCNumber = " & Me!UPCNumber
    rstOut.FindFirst strFilter
    With rstOut
        .Edit
        !OnHand = Me!OnHand
        .Update
        .Bookmark = rstOut.LastModified
    End With
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Qty As Integer
    Dim Cost As Double
    Dim OnHand1 As Integer
    Dim strFilter As String
    Dim Response As Integer

    OnHand1 = Me.OnHand
    Qty = Me.Quantity

    Debug.Print Qty
    Debug.Print OnHand1

    If Me.TransType = 2 Then
        OnHand1 = OnHand1 - Qty
        If OnHand1 < 0 Then
            Me.Quantity.SetFocus
            Response = MsgBox("On hand below acceptable level", vbOKOnly, "On Hand")
            Cancel = True
            GoTo ExitHere
        End If
    Else
        OnHand1 = OnHand1 + Qty
    End If

    strFilter = "UPCNumber = " & Me!UPCNumber
    Cost = DLookup("costset", "tblInventoryNew", strFilter)
    Me!Value = Qty * Cost

    Me!OnHand = OnHand1

ExitHere:
End Sub

The Before Update event cancels the update if the on hand value would become negative, and it sets the various fields.
The After Update event merely updates the OnHand value in qryCurrentInventory. This won't occur if the user cancels the record.

[aardvark]

Thanks Hans, I'll give this a try. Looks good.

[aardvark]

Tested this morning and all works well. Thanks again, Hans.

[HansV]

Thanks for the feedback!