Access 2007 Question

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.
Who is Participating?
Bob BenderConnect With a Mentor Commented:
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?

Bob BenderCommented:
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?
jjc9809Author Commented:

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.

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

jjc9809Author Commented:
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.
Bob BenderCommented:

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.

jjc9809Author Commented:
Reopen the question.  frbCoinDude, may have a sample I can look at.

jjc9809Author Commented:

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 ?

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.