Automatical Table Update and Price Storage.

[Andrew_w]

Lets assume I am a tiny shop selling computers. I sell only three computers which are selected through a drop down menu, they are Sony, Mac and Dell.

To avoid the possibility of mistyping and unnecessary delays, I have made a query which says that if the computer is for example Sony price 500 else 0.

I have stopped there with the query because what I want to do is once the details are entered and then click on store, for the price to be stored in a table also.

Ok, let me explain this better, I have a Customer table with name and address

Computer table with serial number as key and Model (the computer model) in it

And then have an order line table which has order line no, serial no, invoice no, quantity, unit price, and amount

then have an invoice table which has date, vat and total.

Now in the order form once the customer details are entered, the model is chosen, if they chose Sony for example, the unit price is filled by itself, through the IIf statement. It seems impossible for the unit price to be stored in the Order Line table though. It comes up with 0 or sometimes nothing at all.

How would I be able to store the unit price in the order line table?

 

[pbaldy]

I would have a table that listed the products and their prices (and whatever else). On the form where users would input a sale (bound to your order line table), I'd have a combo box whose rowsource was that product table, with the price field included. In the after update event of that combo:

Me.PriceField = Me.ComboName.Column(x)

where x is then number of the column containing price (and column is zero based).

 

[Andrew_w]

Right, let me see if I have understood. You would have a table which would look a bit like this: 1 Sony 500
2 dell 600
3 Mac 700

and nothing else in it?


Ohh I get it, You mean, rather than calculating the unit price through IIf statements, just display it in the combo box next to the computer model.

Well the Unit Price is just one of them. I want the total to be calculated automatically as well and then stored in the corresponding field in the table.

So if a person buys 5 Sony computers then the total would be 5 * 500 thats 3500, that would be easily done with a query, but then how would I be able to store that number in the table?

 

[georgedwilkinson]

Hmmm. This looks like a typical invoicing system. The Northwind database is a good starting sample of how to do it.

Like Paul said, display your OrderLineItems table with DDLs for the "items" you are selling. In the sub-form footer, put an unbound textbox with the calculation for that particular invoice.

 

[pbaldy]

Displaying the price in the combo is up to you. The combo provides you a way for the user to select the product being sold, and the bit of code I provided will fill in the price field when they do. The combo would be bound to the product field, and the user would fill in quantity. That gives you the main data you need. You don't need to save the 3500, as it can easily be calculated from quantity and price.