Dynamic filling of Data in a form

[Guest]

A simple question for people who know Access. I am using Access 2003 and have
a simple table in which I enter Goods and a Price for that item. I have set
up a form that has 2 single fields that wants to use this data. The 1st field
is a combo box in which you select the goods, then I want the corresponding
value from the Price column to automatically populate the other field, but I
can't seem to get it to work.

Does anyone have the answer.

Regards

 

[John Vinson]

A simple question for people who know Access. I am using Access 2003 and have
a simple table in which I enter Goods and a Price for that item. I have set
up a form that has 2 single fields that wants to use this data. The 1st field
is a combo box in which you select the goods, then I want the corresponding
value from the Price column to automatically populate the other field, but I
can't seem to get it to work.

Will the price change over time? do you want to display the price as
of the time that you select the Goods (and have that price remain
static even if the Goods table changes later)? That would be the ONLY
reason to store this Price field in your second table.

If you do want to do so, include the Price in the Combo Box's
RowSource query; put the following VBA code in the AfterUpdate event
of the combo box (by clicking the ... icon and choosing Code Builder):

Private Sub cboGoods_AfterUpdate()
Me!txtPrice = Me!cboGoods.Column(1)
End Sub

I'm assuming the combo box is named cboGoods, the textbox containing
the price named txtPrice, and that the price is in the second column
of the combo's query - the Column() property is zero based.

John W. Vinson[MVP]

 

[John Bartram]

Will the price change over time? do you want to display the price as
of the time that you select the Goods (and have that price remain
static even if the Goods table changes later)? That would be the ONLY
reason to store this Price field in your second table.

If you do want to do so, include the Price in the Combo Box's
RowSource query; put the following VBA code in the AfterUpdate event
of the combo box (by clicking the ... icon and choosing Code Builder):

Private Sub cboGoods_AfterUpdate()
Me!txtPrice = Me!cboGoods.Column(1)
End Sub

I'm assuming the combo box is named cboGoods, the textbox containing
the price named txtPrice, and that the price is in the second column
of the combo's query - the Column() property is zero based.

John W. Vinson[MVP]

 

[John Bartram]

I tried this with a form I am working on that will be used for invoicing and
got "Run-time error 451" (Property let procedrue not defined and property get
procedure did not return an object). what am I doing wrong?

The two fields I am working with are
"Part Number" which is a combo box and "Part Name" that is a text box and I
want to automatically display and store the part name based on the part
number selected.