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
        
        Else
            Me![Posted To Inventory] = False
        End If
        
        'EH.TryToSaveRecord
        
        'If Inventory.GetQtyOnBackOrder(ProductID) > 0 Then
            'If MsgBox(FillBackOrdersPrompt) Then
                'Inventory.FillBackOrders ProductID
            'End If
        'End If
        
    'Removing Posted Inventory
    Else
    'Forces a re-check if you are trying to uncheck....
        If InventoryID > 0 Then
            'Me![Posted To Inventory] = True
        End If
    End If
    
Done:
    Exit Sub

EH:
    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.AddNew
    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.Update
    rsInventoryTransactions.Bookmark = rsInventoryTransactions.LastModified
    AddInventoryPurchase = rsInventoryTransactions.Fields("Transaction ID").Value 'Get ID of newly added record
    rsInventoryTransactions.Close
    Set rsInventoryTransactions = Nothing
    DoCmd.Close
    Exit Function
EH:
    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.)
0
 
Fabrice LambertFabrice LambertCommented:
Hi,

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.
0
 
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
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
That was exactly it. Thanks very much!
0
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.