Add number to field

[gcomp]

I need to add a number to my inventory table based on a query. I want to add quantity_received from one table to instock in another table. There will already be a number in the instock field. What sort of query should I use. I already have a query to return the value I want, I just don't know what to add to the query to have it update the instock quantity.

 

[SOS]

Just my opinion but that isn't the best design. Updating like that is below using a register like option where you have the stock amounts based on the totals of in and out and then you can enter an "adjustment" if necessary to reconcile (much like a checking account).

But if you must do it this way you would use an UPDATE query and it would be something like this:

UPDATE [tblStock] SET [tblStock].Quantity = [Quantity]+Nz([Forms]![Form4]![Text4],0)
WHERE ((([tblStock].ProductID)=[Forms]![Form4]![Combo2]));

and in VBA it would be:

Dim strSQL As String
 
strSQL = "UPDATE [tblStock] SET [tblStock].Quantity = [Quantity]+" & Nz([Forms]![Form4]![Text4],0) & " " & _
"WHERE ((([tblStock].ProductID)=" & [Forms]![Form4]![Combo2] & "));"
 
CurrentDb.Execute strSQL, dbFailOnError

 

[The_Doc_Man]

Concur with SOS. The update query would do the job, but you had better hope you don't run it twice by accident.

Most inventory systems use transactions that either take away stock or add stock. So you have a transaction summation query that groups according to stock ID and adds according to the quantity of each transaction. There are many ways to handle this, but the most useful I've seen is to include a "direction" value of "+1" or "-1" for each transaction (to show that the transaction, whatever its actual type, is either a pull or a store). In a query, you take the quantity of each transaction times its direction and sum over the expression rather than the raw transaction quantity. That is your current stock level for the given stock item.

 

[gcomp]

I appreciate your comments and I plan on going back to redesign some items after I get through this first stage.

About the code? I understand everything until it gets to the Nz, Forms, Form4, etc. It looks to me like it is looking for a specific field in a form. What I want it to do is look at a specific field in a table that is identified in the query.

 

[SOS]

Well, it would be something like:

UPDATE [tblStock] SET [tblStock].Quantity = [Quantity]+Nz([OtherTableName].[OtherFieldName],0)
WHERE ((([tblStock].ProductID)=[OtherTableName].[ProductID]));