Unanswered: Updating a table entry when a list box is selected
I have 10 list boxes where products can be selected for a order. The list box includes ProductID, ProductName, UnitPrice, And StockLevel. What I want to do is every time a product is selected from a list box to then take away 1 from the stock level of that specific product in the products table. Please could someone help me think of the correct VB code to put in the list boxes After Update.
Do I need something like
SELECT Products WHERE Poduct.ProductID = listbox.column(4)
Obviously, what you are looking for is Inventory Tracking and Updating as orders are placed. This is easier said than done and therefore the process will not be explained here. Instead, I am working on your DB to implement Inventory Updating and tracking (with low level warnings) and I will attach the modified DB to a new post in this thread when I'm done.
There will be some table modification (field additions) to the Products table and a new table and form created for measure units. (i.e.: Kg, grams, leters, etc.).
Sorry for the late reply and the delay with your DB sample (attached). Take a look at your DB sample....it will look somewhat different (sorry..I can get carried away). I did not, however, change your limited approach to product selection and worked within the bounds of your concept. I personally would have done this a different way. Also the large screen resolution requirements remain the same (1152 x 864).
The Listbox has been changed to a SubForm and I have added a Index bar for selecting customers either by company name or Contact Name. This is not a ActiveX control. It is simply a Label control coded to act as a Index. Just move the mouse within the Label control and select the desire letter that is displayed.
There is also a slider along the right side of the Customer edit section. Again, this is not a AxtiveX control but a Box control coded to act as a slider for scrolling through your different customers within the edit section.
Other controls have also been added to the Orders SubFom. In particular, Box controls. The Yellow boxes which reside beside each Product combobox is are used to delete (remove) the specific Product from the order. Everything is updated during this process. The Red boxes, which you wont see unless Stock levels for a specific Product fall on or below a preset warning level. Warning Levels are now set within the Products Form which can be quickly accessed by way of double-clicking on any Products ComboBox located within the Orders SubForm. These small red boxex will flash when stock levels fall to low.
I have also added an addition column to your Products ComboBoxes located on the Orders SubForm. This column displays the current Stock Level for each Product contained within the ComboBox(es). When the quantity ordered (Actual Weight field) is updated (as it loses focus) the stock is automatically reduced in Products table. I have also added a Shipped field (checkbox) to the Orders SubForm which serves the following purpose:
If a specific order has not yet been Shipped, then the order form can be edited. Where as to say if the product amount is modified stock levels are updated accordingly. If some more product was added to the order then stock levels for that product will be reduced. Consequently, if some product is removed then the stock levels will increase.
If the Specific Order has been Shipped then the Order record is considered Locked. Actually, more than consisdered...it is locked. It can be Unlocked at any time by clicking on the added Unlock link button.
By the way...Ummm...I removed all the buttons. I personally find them bulky and an eyesore on the screen. In the provided sample, they appear as web links with compression when selected (clicked). When your mouse passes over a link, that specific link changes to the color red and back to blue when the mouse cursor moves off it.
When a Invoice is Printed (and it must be printed) the Shipped field automatically gets Checked (true) and the record is Locked. Oh...I also changed the look of your invoice a little. It simply displays a simple way to put multi colors into the invoice lines.
There is some sound also added to this sample. Whenever a button link is selected or the buttons on the home made coded record navigation buttons are selected, you will here the sound of a camera shutter. You can obviously change this sound to whatever you like.
There is a lot of coding in this sample and not all of it is as clean as it should be. That would take considerably more time. Commenting is also limited but does exist in pertinant areas. You will definately need to know a little of VBA code. Some Windows API functions are also used.
This may seem somewhat useless to you as it stands but perhaps someone out there may find some of the techniques and code samples helpfull for their own projects.
Hope this helps in some little way.....
PS: Make sure you have the "Break On Unhandled Errors" checked (on) within the VBA Editor Options.
Thankyou so much for your help. From this I can now understand how to produce inventory systems and use this one in the project i am working on. I can learn alot from the features you added and provide more professional DB feature.
I like the customer select where you can see the company and contact name. Also the way you have used msgbox's.
There is also lots of things I dont understand, so thats my job to work them out for