Access 2007 Question on Update Queries

Posted on 2012-08-10
Last Modified: 2012-08-11
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.

Question by:jjc9809
    LVL 10

    Accepted Solution

    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.
    LVL 10

    Expert Comment

    Glad to be of help. Good luck!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now