Basic Subform Question - Pulling Up A Price

[dancole42]

I have an InvoiceLines subform on an invoice table. In the subform, I
have the following fields that are bound to the actual InvoiceLines
table:

Class (the type of product)
Product (the specific product)
Price
Quantity
Subtotal

When someone selects a product class, the options in the product
dropdown change to only those products in that class. Class looks up
in a ProdClass table that lists all the possible product classes, and
this table is related by ClassID to the Prod table, which lists the
individual products and their prices.

How do I get it so that when someone selects a product from the
Product dropdown, the price listed in the Prod table is automatically
filled into the Price field in the InvoiceLines subform?

I can do a simple VB script that says "If Product = Widget then Price
= $5.95," but that would get real cumbersome real fast.

Any ideas?

[ManningFan]

You can either use a bound form or use a DLookup statement to populate
the Price textbox.

On Mar 5, 12:57 pm, [e-mail] wrote:

I have an InvoiceLines subform on an invoice table. In the subform, I
have the following fields that are bound to the actual InvoiceLines
table:

Class (the type of product)
Product (the specific product)
Price
Quantity
Subtotal

When someone selects a product class, the options in the product
dropdown change to only those products in that class. Class looks up
in a ProdClass table that lists all the possible product classes, and
this table is related by ClassID to the Prod table, which lists the
individual products and their prices.

How do I get it so that when someone selects a product from the
Product dropdown, the price listed in the Prod table is automatically
filled into the Price field in the InvoiceLines subform?

I can do a simple VB script that says "If Product = Widget then Price
= $5.95," but that would get real cumbersome real fast.

Any ideas?

[dancole42]

On Mar 5, 1:54 pm, "ManningFan" <[e-mail]:

You can either use a bound form or use a DLookup statement to populate
the Price textbox.

Can you be a bit more specific? I really have no idea what I'm doing
here :)

Thanks!

[Don Leverton]

How about including the Price fields as columns in your combo-box?
Then use the combo's AfterUpdate event procedure to insert the value of the
price column into the appropriate field in the subform.

Something like this (from one of my invoicing apps):

Private Sub cboPartNumber_AfterUpdate()

Me![LINE] = Me![cboPartNumber].Column(1)
Me![QtySold] = Me![cboPartNumber].Column(2)
Me![REG] = Me![cboPartNumber].Column(3)
Me![SALE] = Me![cboPartNumber].Column(4)
Me![DESC] = Me![cboPartNumber].Column(5)
DoCmd.GoToControl "QtySold"

End Sub

Here is the Row Source for the combo-box above.
SELECT tblToolshow.BASIC, tblToolshow.LINE, tblToolshow.QTY,
tblToolshow.REG, tblToolshow.SALE, tblToolshow.DESC FROM tblToolshow ORDER
BY tblToolshow.BASIC;

HTH,
Don

BTW.
"cboPartNumber" is the name of my combo-box and is bound ( Column(0) ) to
the "PartNumber" field on the datasheet-style subform.
[LINE] is "Product Line", [REG] is "Regular Price", [SALE] is the "Sale
Price", and [DESC] is the "Part Description"
<da*******@gmail.comwrote in message
news:11**********************@64g2000cwx.googlegro ups.com...

I have an InvoiceLines subform on an invoice table. In the subform, I
have the following fields that are bound to the actual InvoiceLines
table:

Class (the type of product)
Product (the specific product)
Price
Quantity
Subtotal

When someone selects a product class, the options in the product
dropdown change to only those products in that class. Class looks up
in a ProdClass table that lists all the possible product classes, and
this table is related by ClassID to the Prod table, which lists the
individual products and their prices.

How do I get it so that when someone selects a product from the
Product dropdown, the price listed in the Prod table is automatically
filled into the Price field in the InvoiceLines subform?

I can do a simple VB script that says "If Product = Widget then Price
= $5.95," but that would get real cumbersome real fast.

Any ideas?