Access 2007 Question on Updating a TotalAvailable ona Product

Posted on 2012-08-17
Last Modified: 2012-08-20
Hi Everyone,

I have created a database called TradeShow.accdb.  I have two tables:  The first Table is called
Product which holds code, product , price, and Unit Balance (Named Available) in the table.
I have another Table called Product Orders.  I have linked the two tables by the Code which is a Primary Key on the product table only and created a Select Query.  This Select Query called ProductOrdersRec is used for input of orders.   This is whats happening, I enter the Code A6000 with 5 units as a quantity.  There are 1000 units available for sale in the product table.
The query runs and takes the Available which is 1000 and suibtracts 5 giving a QtyBalance of 995.  This query is running everytime it is pulled up and subtracting 5 units again on the A6000 even though the A6000 is not even being ordered.  My question is this:  Do I need to have an order form using a table and not a select query?    I need for the available balance to update, but only when I order that A6000 code again or someone else creates an order for it.

I have attached my database along with the two tables and the query that is linking the two tables.  

I have a coding issue also called Error which I have attached.  How can thsi code be written to work and exceute and not give an error.

Any hep with this project is very helpfull.

Question by:jjc9809
    LVL 13

    Accepted Solution

    When viewing the data, you  should merely be using a SELECT statement (they're basically read only). It should not be editing values in a table.

    Your product table qty should be the total qty ordered.
    The qty in Orders table should be the number of units assigned to that order.

    To see how many are remaining, you would do a different query which  takes Products.Qty - sum(Orders.qty)

    I can't currently download the db to look at specifics or see what the actual error is to answer that part.

    Author Closing Comment

    I found out the solution to my problem.  Thanks again.

    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

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    746 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

    14 Experts available now in Live!

    Get 1:1 Help Now