Dynamic filling of Data in a form

Greg · External Since: Jan 10, 2005 Posts: 154

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 · External Since: Sep 17, 2003 Posts: 9558

On Fri, 27 May 2005 14:05:01 -0700, "Greg"
<Greg DeleteThis @discussions.microsoft.com> wrote:

>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 · External Since: Sep 16, 2009 Posts: 1

"John Vinson" wrote:

> On Fri, 27 May 2005 14:05:01 -0700, "Greg"
> <Greg.RemoveThis@discussions.microsoft.com> wrote:
>
> >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 · External Since: Sep 16, 2009 Posts: 1

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.

"John Vinson" wrote:

> On Fri, 27 May 2005 14:05:01 -0700, "Greg"
> <Greg DeleteThis @discussions.microsoft.com> wrote:
>
> >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]
>