Auto Update a value in a field based on another?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a form for Orders with a subform for order detail. How do I
properly set up the subform so that when Item_ID field is selected from a
combo box, the Price field is updated automatically. I want the user to
select the product without having to enter the Price. FYI: I will also be
including subtotals and totals.
Thank you in advance!
 
Ivan said:
I am creating a form for Orders with a subform for order detail. How
do I properly set up the subform so that when Item_ID field is
selected from a combo box, the Price field is updated automatically.
I want the user to select the product without having to enter the
Price. FYI: I will also be including subtotals and totals.
Thank you in advance!
Click to expand...

Since you're using a combo box to choose the item, and that combo box
presumably has its rowsource set to your Items (or Products) table -- or
to a query of it -- why not include the price in that query, set it as
an additional column in the combo box, and use the AfterUpdate event of
the combo box to assign the price from that column of the combo box to
the Price text box?

For example, if the combo box's RowSource is

SELECT Item_ID, Item_Description, Item_Price
FROM Items;

and the combo box "Item_ID" has these properties:

Column Count: 3
Bound Column: 1
Column Widths: 0"; 1.5"; 0"

(the width of the second column is arbitrary, but I'm assuming you hide
the other columns), then you can use code in the combo box's AfterUpdate
event like this:

'----- start of example code -----
Private Sub Item_ID_AfterUpdate()

Me!Price = CCur(Me!Item_ID.Column(2))

End Sub
'----- end of example code -----
 
Back
Top