[Last Call] Learn how to a build a cloud-first strategyRegister Now


Access 2007 Question

Posted on 2012-08-15
Medium Priority
Last Modified: 2012-08-20
Hi everyone,
I need some help updating a field each time an order is placed in my database.  We will have six people taking orders on about 100 products which have codes such as A6000.  I have created my database called Database9.  I have two tables which are:  Product which holds the product codes with descriptions and prices along with starting available for each product.

When a product is entered and sold I need for the available balance remaining on each product to update.  This updated available balance needs to be used for each product when another order is placed on a product.

For example:  I have A6000 with 1000 units available for sale.  We sale 10 units so the new available balance to use for the next order placed on the product will be 990.

I have created an Update Query, but how can I develop an input form to take orders with what I got created.  I need for the two tables:  Product and product Orders to link with Code as being a Primary Key.  I want the user to just enter say A6000 and the product description will come in automatically. I have created this query, called ProductOrdersRec.
Now,I can't use drop down boxes or lists becuase there will be too many codes and descriptions.  In fact over 100 products.  I need an input form which will allow the user to just enter Code and Qty.  The Description of the product should come as should price.  The Total which is QTY*Price must be calculated.  Also, QTYBalance should calculate too which is Starting Available Balance - Qty each time.  If I use a query as a subform for Input,  All of the fields can be made and created, but when the update query runs each time, the available balance gets off and is incorrect.  So when another sale is made on the product, I have an incorrect starting balance available for sale.

My update query will update the product table with the available balance to start with. I want to use the ProductOrdersRec as a subform and enter orders directly in this subform, but when I do enter products and run the Update query, the available balance is right until I open the form again, when I do the QTYBalance ends up with an incorrect balance available for use next time when an order is placed on the code becuase the query is running each time and the update occurs more than once giving incorrect results.

Any suggestions will be very helpful.

I am very close.  Please find my database9 attached.
Question by:jjc9809
  • 4
  • 3

Expert Comment

by:Bob Bender
ID: 38299119
Is this a duplicate posting from another category, but with a different description?

Looks pretty familiar... especially the Initual spelling on a table.

OR, is it an updated question based on your answer before?

Author Comment

ID: 38300051

It is an updated question based on the answer before.  I can't use the updated query's available balance on the product table due to using ProductInvoiceRec Query as a subform and using it as an input form.


Author Comment

ID: 38304982
I've requested that this question be deleted for the following reason:

I can't get any real help here due to the complexity of the problem.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

by:Bob Bender
ID: 38303448

Studying the database to get the flavor of what you are looking at doing.

First thought and approach to consider is using a Main form, and then embed a subform with the inventory totals.  That way, you would have an automatic view of the available inventory. '

I think that I have a decent example of this in a database I have, and am looking for it.


Author Comment

ID: 38304662
Reopen the question.  frbCoinDude, may have a sample I can look at.


Author Comment

ID: 38305004

We have developed a main form input document off a query which is calculating the available balance each time an order is entered for a code, but when multiple codes are put in, we have an update query that is updating the Balance Available which is incorrect due to having the multiple codes which are sold.    The multiple codes are causing multiple updates to occur which we do not want but one time.

In the query (Update Orders Query) the ProductOrdersRec query is linked to the Product file by Code.

I believe the ProductOrdersRec query which has multiple Codes is  causing multiple updates of the QtyAvailable field.  Is there any way to make a update distinct ?


Accepted Solution

Bob Bender earned 2000 total points
ID: 38309782
Sorry for the delay in answering here. Personal issues to handle since Friday night.

First, do you have access to the Northwind database?  If not you can search the web, as that is how I have a copy of when I use it for referencing.  It has an Orders form that you could use for ideas.  Modifying (or other terms if you wish) it to include your info may not be too hard.

I may be off here, but what I hear is that you have problems with multiple entries, in that the inventory totals are not being updated right when multiples entered.

In my mind, you would treat each row (or item) as a separate entity.  As you finish a row, it would update the database and inventory.  Then when entering row 2 thru n, and you input an part #, that row would pull the updated data.  And you update line by line...

The same could be done (though stickier) to create a full table of parts wanted, an iterate (repeat a process for each row) through the table entries and do the updates.  Still, it would behave the same as you do above, but all at once,

Make any sense?


Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

830 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