Hi,

I have created an simple customer order and stock database with an update query that deducts the quantity required entered on my customer orders subform from the quantityinstock field in the product table.

From reading through some threads on the forum I understand this is not the best way to control stock, although I only need a simple system for academic purposes.

When the user enters the quantity for a specific product it is on one row of my subform, they then have to press a 'confirm order' button to run the update query, although if there is more than one product on the order they have to go to the next record on the subform and then press the confirm order button again to run the update query for that row.

I was just wondering if there was any way of showing when the confirm order button had been pressed for a certain row on the subform. i.e. when confirm button is clicked - run query then change current record background colour?

I just thought this may help stop a user clicking the button twice for the same row (product).

If this is not possible, can anyone help me find a way to include all the rows of the subform in the query i.e. when it is run it updates all rows of the subform?

Many thanks for your help, I am really stuck!

I would strongly suggest changing the way you are doing this and normalising your database...

By not doing this the correct way you are making problems for yourself.

Here's a link for you to look at

http://members.iinet.net.au/~allenbrowne/AppInventory.html

I'm sure you've already seen it (I posted it in one of your previous threads) but I think you should possible look at it again and consider modeling your DB on the advice there

Hi Danny,

Thank you for your help, unfortunately, I have not got much time left before I have to hand my work in and dont think I will be able to change my design. I am just trying to think of ways to improve my existing design as best I as I can.

Thanks again for your help

The way to improve your existing desing is to do it correctly. I gather this is a class project. Therefore you are seeking a grade for the project. Don't you want the best grade?

Hi Scott,

I am trying to achieve the best grade, although as I said I do not have much time left for this project (I wish I had checked this forum earlier!)

50% of my grade is for explanation of how I have done things. Unfortunatley I do not fully understand the article that Danny has referenced and therefore I decided to go with the simpler design and explain how I have done it rather than just copying the code from the article.

I will mention that this is not the best way to do things and will reference the above article.

Thanks

A properly designed inventory application centers around a transactions table. This transactions table will look something like this:

tblTransactions
TransactionID (PK Autonumber)
ReferenceID (FK)
ProductID (FK)
TransactionType (Outgoing, Incoming)
Quantity
Price

This table is then used for the Details of a sales order or a purchase order. The ReferenceID foreign key ties the transaction back to the sales or purchase order. Every movement of product is recorded as a transaction. To CALCULATE stock on hand, you simply add the incoming and subtract the outgoing.

Hi Scott,

Thanks, I will definitely look into the transactions table, hopefully I will have enough time to change my design before I hand my work in.

Thanks again for your help, I may have a few more questions soon!

Thanks

Glad to assist. We will be here if you need us