Table update

[Jacques Steinman]

I have a combo box set up on a subform to list items from another table.
After selecting an item from the list I need it to update another field on
the same row with information relating to the selected item, I.E. when
selecting an product, i want to pull the cost from the products table.

Everything else is working perfect, I just can't get this to work. I feel so
stupid.

 

[Dirk Goldgar]

I have a combo box set up on a subform to list items from another table.
After selecting an item from the list I need it to update another field on
the same row with information relating to the selected item, I.E. when
selecting an product, i want to pull the cost from the products table.

The easiest way I know of is to include the price as a hidden column of the
combo box, and then pick it up from there when the user updates the combo by
picking a product. For example, you might have properties for the combo box
like this:

Name: cboProduct
Row Source: SELECT ProductD, ProductName, Price FROM Products
Bound Column: 1
Column Count: 3
Column Widths: 0"; 1"; 0"

Then you'd have code in the AfterUpdate of the combo box like this:

Private Sub cboProduct_AfterUpdate()

With Me.cboProduct
If Not IsNull(.Value) Then
Me.ItemPrice = .Column(2)
End If
End With

End Sub

Note that, in VBA code, the columns are numbered from 0, so the third column
is .Column(2).

 

[Jacques Steinman]

That did the trick. Thanks!

The easiest way I know of is to include the price as a hidden column of the
combo box, and then pick it up from there when the user updates the combo by
picking a product. For example, you might have properties for the combo box
like this:

Name: cboProduct
Row Source: SELECT ProductD, ProductName, Price FROM Products
Bound Column: 1
Column Count: 3
Column Widths: 0"; 1"; 0"

Then you'd have code in the AfterUpdate of the combo box like this:

Private Sub cboProduct_AfterUpdate()

With Me.cboProduct
If Not IsNull(.Value) Then
Me.ItemPrice = .Column(2)
End If
End With

End Sub

Note that, in VBA code, the columns are numbered from 0, so the third column
is .Column(2).


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)