Table update

  • Thread starter Thread starter Jacques Steinman
  • Start date Start date
J

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.
 
Jacques Steinman said:
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.
Click to expand...


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).
 
That did the trick. Thanks!


Dirk Goldgar said:
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)
Click to expand...
 
Back
Top