How do you update units available on Table "Stock on Hand" from value entered on form

[Nick Ferreira]

How do you update value "Units available" on Table STOCK ON HAND from a value entered on a form for attribute "Units In" on table UNITS IN. I need to update the "Units available" as soon as control is passed back after entering the "Units In" form. With "Units In" I need to Increase the value of "Units available" on table STOCK ON HAND. I also need to decrease the value in "Units Available" on table STOCK ON HAND when a value is entered on a form in attribute "Units Out" on table UNITS OUT.
Regards,
Nick

[ADezii]

In its simplest form, the Logic would be:

Dim strSQL As String
 
If IsNull(Me![Units In]) Or Not IsNumeric(Me![Units In]) Or Me![Units In] <= 0 Then Exit Sub
 
strSQL = "UPDATE [STOCK ON HAND] SET [Units Available] = ([Units Available] + " & _
                 Me![Units In] & ") WHERE [Unit_ID] = " & Me![Unit_ID]
 
CurrentDb.Execute strSQL, dbFailOnError

[Nick Ferreira]

Hi ADezii,
Thank you for the prompt reply. However I am not a programmer type, and created a Query to link the two tables (STOCK ON HAND and UNITS IN) and used that for the form where the "Units in" are captured.
The same for the Units out form. My question now is where do I copy and paste your solution to have the available units updated?
Regards,
Nick

[ADezii]

@Nick - The logical Event to capture the change in your Units Status would be the AfterUpdate() Event of the Form.

[Nick Ferreira]

Hi again, sorry I am lost again!!!
I went into design mode, got the properties on the "Units In" attribute on the form and on After Update, created an event procedure that looks like this:

rivate Sub Number_of_Units _in_AfterUpdate ()
1. Dim strSQL As String
2.
3. If IsNull(Me![Units In]) Or Not IsNumeric(Me![Units In]) Or Me![Units In] <= 0 Then Exit Sub
4.
5. strSQL = "UPDATE [STOCK ON HAND] SET [Units Available] = ([Units Available] + " & _
6. Me![Units In] & ") WHERE [Unit_ID] = " & Me![Unit_ID]
7.
8. CurrentDb.Execu te strSQL, dbFailOnError

End Sub

This however does not do the trick.
Please help again
Nick

[ADezii]

1. Is the Form Field actually called [Units In]?
2. Is your Primary Key Field actually named [Unit_ID], and is it Numeric? Does it exist on the Form?
3. Are you receiving an Error Message, or is the Value simply not Updating?
4. If you are receiving an Error Message, what exactly is it?
5. Nick, you must be more specific in your explanation.

P.S. - When I get a chance, I'll create a Demo that will illustrate the basic Logic.

[Nick Ferreira]

The Primary key on all three tables are "Stock Code"(Text Field) , the STOCK IN and STOCK OUT tables additionaly have a sequence number added to Stock Code as primary key.
The actual field on the form for the STOCK IN is "Number of Units In" and for the STOCK OUT is "Number of Stock Out". The field I am trying to update on STOCK ON HAND table is "Available Number of Unts"

With the code I sent you I dont receive an error, just nothing happens

[ADezii]

I created a Demo (Attachment) for you that should definately point you in the right direction.