problem with invoice form

[4Ankit]

I am having trouble with my invoice form. Basically my invoice form
consists of a subform(which includes invoice details such as which
products are brought, quantity and price)Outside this invoice form
consists of customer details, employee creating invoice and subtotal
for the invoice. The problem i am having is with the subform, i have
added the appropriate fields to the subform such as my invoice details
table(consisting of invoice id, line number, product id(drop down menu
to find the product) ,quantity field and price field. I want the price
field to show the price of the product when chosen from the product id
column. The price of the product is in the field('retail price' in the
stock table). How would i go about getting this price into my subform?
So when i click which product id the price will show up next to it.

Below is the 'invoice master table' and 'invoice details table'
attributes

'invoice master table'
PK: invoiceID, invoicedate, empno, custno

'invoice details table'
PK InvoiceID, PK LineNumber, ItemID, Quantity, Price( i want this field
to have the price according to the ItemID in the stock table, the field
to reference is 'retailprice' in stock table)I am having a problem with
the price field.

All help would be much appreciated.

 

[Albert D. Kallal]

A common problem..

You have two solutions.

1), if the price will never change, then you don't have to copy the
price from the price table. In these cases, I base the sub-form on a query
that joins to the product table. That means when you select/set the product
id in the comb box, the price (and even description fields) will
automatically display (assuming your build your sub-form on a join to the
products table --- remember, this has to be a left join to work).

2) the price really does need to be copied to the sub-form table. This
is likely your case.

Solution

Simply build the product id combo box to include the price field. So, you
would likely have for this combo box


id Product description Product price.

So, we would have a 3 column combo box. The first column of "id" is likely
zero length set in the combo...so, we don't see it...

Now, in the eh combo box after update event, we simply copy the price
value....

me.Price = me.cboProduct.Column(2)

That is it!!...one line of code!!!

Note that the column(2) is zero based, so 0 = 1 col, 1 = 2nd col etc......