Access 2007 Question on Updating a TotalAvailable ona Product

Posted on 2012-08-17
Medium Priority
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

Ryan earned 2000 total points
ID: 38306437
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

ID: 38312512
I found out the solution to my problem.  Thanks again.

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

850 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