Update quantity on hand

[Henley12]

I have a form where I enter materials used for a job. How can I automatically update the on hand quantity for the inventory item used?

 

[Fifty2One]

If the form has an update or confirm button then place a sql statement written in vb on the onclick event of the button

 

[Henley12]

My form does have such a button.....I am unsure as to how to write a statement to update a table. I know how to add a new record, but updating an existing record is not familiar to me just yet.

 

[Fifty2One]

My form does have such a button.....I am unsure as to how to write a statement to update a table. I know how to add a new record, but updating an existing record is not familiar to me just yet.

My quick workaround to write SQL statements to work under buttons OnClick event is to write a query qith the information that i wish to view and a sample criteria then view it in SQL view. if you build an update query in this fashion then you will have all the correct elements.

Here is a sample from a button i use to update a table called IAMHERE

 Dim NOWSTALK As String
 Dim Sql As String
 Dim NOWITHERE As Date
 Dim THEREBYWHO As String

If Me.OrigStalker <> "" Then
NOWSTALK = Me.OrigStalker
NOWITHERE = Now()
THEREBYWHO = [Forms]![frmSignOn]![Combo9]

            Sql = "UPDATE IAMHERE SET "
            Sql = Sql & "[LOCATION] = 'Stalker Closed', "
            Sql = Sql & "[STALKCOMMENT] = 'Added to Coating and Splicing Inventory', "
            Sql = Sql & "[WHOLOCATION] = '" & THEREBYWHO & "', "
            Sql = Sql & "[WHENLOCATION] = #" & NOWITHERE & "#, "
            Sql = Sql & "[ComplWho] = '" & THEREBYWHO & "', "
            Sql = Sql & "[ComplWhen] = #" & NOWITHERE & "# "
            Sql = Sql & " WHERE [STALKER] = '" & NOWSTALK & "' ;"
           
'Turn warning off, run SQL, turn them back on again
DoCmd.SetWarnings False
DoCmd.RunSQL Sql, 0
DoCmd.SetWarnings True
End If

 

[pbaldy]

Personally I would not try to store quantity on hand. Here on some thoughts on the matter:

http://allenbrowne.com/AppInventory.html

 

[MdHaziq]

I have read the sample program.

I don't understand the codes being used but I understand the method.

I need to update on-hand quantity too.
I do not know how to use the sample code in my program.
Maybe you can help me.