Changing a value in one form from another form (in code).

[VBjavaFriend]

Is there a way to change a value in another form/table from a different form? For instance, I have a form called customer invoices. When the user is finished entering a part number I want to open the inventory form and deduct the quantity of the part number from inventory based on the quantity on the invoice. Here is my code, but it doesn't work.
Private Sub cmdOpenInventoryForm_Click()
On Error GoTo Err_cmdOpenInventoryForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Inventory"

stLinkCriteria = "[PartNumber]=" & "'" & Me![cmbPartNum1] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If [Customer Invoices].Form![txtQuantity1] > 0 Then
[Inventory].Form![Units In Stock] = ([Inventory].Form![Units In Stock] - [Customer Invoices].Form![txtQuantity1])
End If
Exit_cmdOpenInventoryForm_Click:
Exit Sub

Err_cmdOpenInventoryForm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenInventoryForm_Click

End Sub
What am I doing wrong? OR is this impossible to do? Somebody told me the only way to do this is by using a subform (inventory) on the invoice form. I really don't want to have to do this because I like the layout of my invoice. Can anyone help me?

 

[kraxmo]

So far, so good. You need to issue a Refresh on the target subform to complete the update.

If Forms![Customer Invoices].Form![txtQuantity1] > 0 Then
Forms![Inventory].Form![Units In Stock] = (Forms![Inventory].Form![Units In Stock] - Forms![Customer Invoices].Form![txtQuantity1])
Forms![Inventory].Form.Refresh
End If

If you plan to use another form for updates, you need a way to close the open form afterwards. (Maybe if you instantiate a form object, filter it, update it, close and destroy the object?)

Alternatives:

1. Use a direct ADO Command object to update the values via SQL (Recommended).

2. As someone else mentioned, use a hidden subform on your customer form that points to the invoice form and do all your operations on Customer.

Assume sfrm_Invoice is your non-visible subform linked to Me![cmbPartNum1].ControlSource:

Me.Form!sfrm_Invoice![Units In Stock] = Me.Form!sfrm_Invoice![Units In Stock] - Me![txtQuantity1])

Note: this may have interesting side effects if there is more than one row in the subform... ;-)
Jim Kraxberger
Spinning gold into straw...

 

[VBjavaFriend]

Jim,
I added the refresh method, but it still doesn't update the field. I have a feeling I am going to have to completely destroy the invoice form and do a subform. If I could just access that one field, update it, and be done with it I am sure I could get the coding to work for all of the other quantity textboxes. If you have any other ideas about this and want to see the whole database so you can see the entire problem e-mail me at amy_vb_java@yahoo.com. Thanks for your help!