Stock and Sales Form/Sub/Append!?? Help

[james_IT]

hi,

i have three tables - Customers, Sales, Stock

i have a form (like an on screen invoice) for sales. This shows the Customers Details and what stock they have bought. The customer details are the main form and i have a sub form with the stock, however i realised that this would not work (as a sub form is for adding stock not appending existing stock.)

Ideally i would like to type the stock number in, then the details of that piece of stock come up, then i want to change bits (a tick/untick box) but this would not work on a sub form. Customers buy UP TO 5 pieces of stock in one sale.


Any ideas?

thanks

 

[gemma-the-husky]

why can't you do what you want on or from the subform.

are you trying to add new stock items to the stock table, unrelated to the customer on the main form?

 

[james_IT]

no,

stock will have already been logged as it come in

i want to bring up an existing piece of stock to link it to a customer as they buy it. i have attached what my current form looks like. but i realised that a sub form of the table of stock would only add new stock, not append/change existing stock.

thanks

 

[ColinEssex]

I did something similar.

The process I used was to first select the customer using the customer screen. There are various buttons to do various things - one of which is to create an order.

The order screen opens and this is linked to the customer by IDNo. Enter the date, order number etc.
Then you select the item from a ComboBox (linked to the stock table) when you select the item, it puts the detail into a ListBox - if a customer wants 10 and you only have 5, a part order is created. An option comes up to ask if another item is needed, if so, the order number etc remains and you select another item from the ComboBox.

In the ListBox is a button alongside each item so if you want to view the full details of the item, you can do so. You can obviously delete the item (from the order) if an error is made.

Once the order is complete, the stock values are changed (to reflect the items now purchased) and the invoice is created. There's an option to include VAT (or not) for each item, and also a delivery charge and the like. If its a part order, you have the option to charge for the full order or only what they actually get.

When the "finalise" button is pressed on the order, the delivery note / invoice is raised and printed. There is also an option to print a delivery list for the drivers as they need to get stuff signed for.

When new stock is delivered, its added to the stock table, but a flag comes up to indicate if any part orders are outstanding. The part order outstanding knows if its already paid or not.

Col

 

[james_IT]

sounds like a good system..

mine is similar, however the stock is carpet and each piece of stock is pretty much unique. this therefore means that there are thousands of pieces of stock, all different, the only way to bring up the correct details would be by entering a stock number or searching by manufacturer, colour, size etc which takes a long time.

when you finish your purchase does it deduct your stock? how does this work. i want to tick the box (sold?) and enter the customer invoice number when a piece of stock gets sold in the stock table.

thankss

 

[ColinEssex]

My database is a Pharmacy one for a hospital - it has 12,000 items in the stock table

You may need a ListBox that is capable of searching many fields at the same time.

There are examples of this posted, maybe in the sample database forum. I have an example in Access97 if you need that.

Stock gets deducted from the stock list at the point the order is "finalised" or confirmed. If you know how many you have (in stock) and sell (from stock) then its a simple subtraction. Make sure though that you include a "trigger" to warn if stock reaches or is less than a re-order level or you may run out of something.

The re-order list can be one of the reports you run off daily or weekly or whatever.

Col

 

[james_IT]

how do you display the information? do you have customer details at the top and then stock details in a subform? would it be possible to attach part of your screen so i can see how you set it out.

Also, where does the subtract stock calculation/function go, In the command button? What is the code?

thanks again

 

[ColinEssex]

You don't need the customer details on the order screen - only the linking IDNo - which is hidden anyway, the order details go to the order table (via a query).

My orders screen uses a ComboBox to select from and then builds the order in a ListBox.

The stock calculation is done when you are sure the order is right. You know what the current value is, you also know what you are sending out. Just take one from the other and post the result.

At the moment I can't post any pics because my PC is playing up and says its not got enough memory

Col

 

[james_IT]

i wanted a db similar to the Microsoft Example Database "Orders management database". It shows the customer details followed by what they have bought

 

[ColinEssex]

Pull the detail from the customer screen onto the orders screen if you need it. The same as you are pulling the IDNo over so the order links to the customer. The customers screen will be open anyway - but minimised of course.

Col