Access VBA. Can't update a field value on a form

Hi. I am getting the error "The expression you entered refers to an object that is closed or doesn’t exist" at the line marked "ERROR HERE"
in my Access form. I am not sure what is preventing me updating that record. When I tested by just using one line of code "Me![Inventory ID] = 4" it works

Private Sub Posted_To_Inventory_AfterUpdate()
 On Error GoTo EH
    Dim InventoryID As Long
    Dim PartID As Long
    Dim Quantity As Long
    Dim oAdd_InventoryTransactions_GetID As Integer
    Dim oNewInventoryID As Integer
    PartID = Nz(Me![Part ID], 0)
    Quantity = Nz(Me![Quantity], 0)
    InventoryID = Nz(Me![Inventory ID], 0)
    'Posting New Inventory
    If Me![Posted To Inventory] Then
        If IsNull(Me![Date Received]) Then 'So if no date insert today's date in [Date Received]
            Me![Date Received] = Date
        End If
        oAdd_InventoryTransactions_GetID = AddInventoryPurchase(Me![Purchase Order ID], PartID, Quantity)
        If oAdd_InventoryTransactions_GetID > 0 Then 'So if new Inventory ID is greater than zero
                oNewInventoryID = oAdd_InventoryTransactions_GetID
                Me![Inventory ID] = oNewInventoryID 'ERROR HERE The expression you entered refers to an object that is closed or doesn’t exist
                Me![Posted To Inventory] = True
            Me![Posted To Inventory] = False
        End If
        'If Inventory.GetQtyOnBackOrder(ProductID) > 0 Then
            'If MsgBox(FillBackOrdersPrompt) Then
                'Inventory.FillBackOrders ProductID
            'End If
        'End If
    'Removing Posted Inventory
    'Forces a re-check if you are trying to uncheck....
        If InventoryID > 0 Then
            'Me![Posted To Inventory] = True
        End If
    End If
    Exit Sub

    MsgBox (Err.Description)
End Sub

Function AddInventoryPurchase(PurchaseOrderID As Long, PartID As Long, Qty As Long) As String

    'NOTE: Must return the new Inventory ID  for the Purchase Order Detail table
On Error GoTo EH

    Dim rsInventoryTransactions As DAO.Recordset

    Set rsInventoryTransactions = CurrentDb.OpenRecordset("SELECT * FROM [Inventory Transactions]")
    rsInventoryTransactions![Transaction Type] = 1 '1 = Purchased
    'rsInventoryTransactions![Transaction Created Date] = Date
    'rsInventoryTransactions![Transaction Modified Date] = Date
    rsInventoryTransactions![Part ID] = PartID
    rsInventoryTransactions![Quantity] = Qty
    rsInventoryTransactions![Purchase Order ID] = PurchaseOrderID
    rsInventoryTransactions.Bookmark = rsInventoryTransactions.LastModified
    AddInventoryPurchase = rsInventoryTransactions.Fields("Transaction ID").Value 'Get ID of newly added record
    Set rsInventoryTransactions = Nothing
    Exit Function
    AddInventoryPurchase = 0
End Function

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
mbizupConnect With a Mentor Commented:
The only OBJECT in the problematic line of code is "Me", the reference to the current/active form.

The *reason* that you are seeing this error is that you are closing the current form in AddInventoryPurchase, and then trying to set values on the form after it has been closed.  The error is letting you know that the form/Object ("Me!") is no longer open.  

Try removing the DoCmd.Close from the AddInventoryPurchase function and see if that resolves the error.  (There are other places to put the Close statement which might make more sense, depending on your overall goals.)
Fabrice LambertFabrice LambertCommented:

First, your AddInventoryPurchase function return a string, yet you assign the returned value to an integer (this is a no sens).

Second, I think afterUpdate is the wrong event, data have already been pushed to the database, and can't be updated anymore.
Try with the beforeUpdate event.
John TsioumprisSoftware & Systems EngineerCommented:
I think you have mispelled something ...probably Me![Inventory ID]....try it simple...type
Me. <---- at the dot try to see if InventoryID pops as an option to select in order to get...-->Me.InventoryID
Also i am not sure about this
Dim InventoryID As Long

Open in new window

An lastly do you actually get a value here : oNewInventoryID
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
That was exactly it. Thanks very much!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.