Access 2007 Question on Update Queries

Hi Everyone,

Please see my new database where I have made some changes .

Please analyze the Product Table.  The IntitalQuantity field needs to update each time an item is sold.  I will have a Complete Command Button above on the Main Form with the subform on top.  When the user enters a sale and selects the Complete Button, I want an update query to run updating the InitialQuantity of each Code.

For Example:  User enters a Qty 10 Units.  The Initial Quatity in the Product Table is 1,000 Units.  When the sale of 10 units is entered and the Complete button is selected, I want the InitialQuantity field to Update in the product Table to 990.  The number 990 will be the new QTYAvailable for a product when a new order is made for the code.

i hope this makes sense.

Any help is very much appreciated.

Who is Participating?
Your download does not seem to be working.

You can put together a basic query using the query builder, and then modify it for your needs.

Click Create, Query Design.

Add the Product Table.

Add the Initial Quantity field to the query.

You also need to add a field with the product ID (Or whatever your primary key is - how Acccess should identify which product's initial quantity to change.)

In the Product ID field on the button, in the Criteria row, type FOrms!formname!FieldContainingProductID.

On the ribbon, change the view to Datasheet to make sure that it pulls in the record in question. (Note: this query will only work when the form is opened.)

On the ribbon, click the 'Update' button to make this query an update query.

In the Update row of your query, type Forms!formname!FieldWhereTheyEnterHowManyUnits

You can click the View button to see which record will be updated. You can click the Run button to actually update it.

Save the query with a name you like.

Then, on your form, create a button using the wizard. Tell the button to run a query, and tell it the name of the query you just created.
Glad to be of help. Good luck!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.