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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
mbizupCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
That was exactly it. Thanks very much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.