Subform Visual Basic Coding

[Grassy7]

I'm trying to find a code to enter so that when I select or enter a Product
Name into my subform, the UnitPrice will automatically appear making it user
friendly. I actually have the products in a combo box. I have the code in
the AfterUpdate Even Procedure: "Me.UnitPrice = Me.ProductName". Now, when
I select the first product from the combo box, the price jumps to $1.00
(wrong price). The prices increase by one when I select the next products
from the combo box. Any ideas???

 

[Fie]

hey... thats what i want my form do to. I have a combo box that
displays a list of Schools and i want the number of pupils and cost
code for each school to be displayed once a school is selected...???
How did you do that???

Fie

 

[Brian Bastl]

if the rowsource for your Product combo looks something like:

SELECT ProductID, ProductName, UnitPrice
FROM <YourProductTable>

then you can push the value into Me.UnitPrice with
Private Sub YourCombo_AfterUpdate()
Me.UnitPrice = Me.YourCombo.Column(2)

<or>

if the rowsource for your Product combo doesn't include UnitPrice

<watch for line wrapping!>

Private Sub YourCombo_AfterUpdate()
Me.UnitPrice = DLookup("UnitPrice","YourProductTable","[ProductID]="
& Me.YourCombo)

and finally,

if the rowsource for you Product combo doesn't include either ProductID
or UnitPrice then

Private Sub YourCombo_AfterUpdate()
Me.UnitPrice =
DLookup("UnitPrice","YourProductTable","[ProductName]='" & Me.YourCombo &
"'")

HTH,
Brian

 

[Douglas J Steele]

I'm not quite sure what Me.UnitPrice and Me.ProductName are supposed to be.
Assuming UnitPrice is a text box, and ProductName is the combo box, what is
the order of the columns in your combo box?

If the price is, say, the 3rd column in the combo box (counting columns even
if they're not visible), use:

Me.UnitPrice = Me.ProductName.Column(2)

(columns start at 0, so the 3rd column would be 2 as shown above)

When you simply refer to Me.ProductName, you're going to get whatever column
has been designated as the combo box's Bound Field. Usually, that's 1, for
the 1st column (confusing, isn't it, that sometimes it's 1 based and other 0
based! <g>). If the bound field is the identifier for your Product table,
and you've used an Autonumber, it would make sense that selecting the first
row in the combo box would return 1, the second row would return 2 and so
on.