store information in table

[Guest]

I have a Form & a Combo that populates a field automatically when a product
is selected. (If a Product is selected a Stock code for the Product is
populated in another column). The problem I have is the code shows on the
Form but does not store in the table.

Code for Combo Box:
Private Sub Product_AfterUpdate()
Me.txtStockCode = Me.cboProducts.Column(1)
End Sub

Control source for Stock code:
=[cboProducts].[Column](1)

 

[BruceM]

The Control Source for StockCode needs to be a field in the form's
underlying table or query. You could use the After Update event of
cboProducts to pass the Column(1) value to txtStockCode, which is in turn
bound to the StockCode field in the table, but you probably shouldn't do it
that way. Instead, set the bound column of cboProducts to 2, and the
visible column to whatever column contains the Product name. Columns are
counted from 1 on the property sheet, and from 0 in VBA and in expressions,
so it's the same column. You can look at the Product name whenever you want
in a form or report. If you want to see both the name and the StockCode,
just continue with setting the Control Source of the unbound txtStockCode to
=[cboProducts].[Column](1).

 

[Guest]

The cboProducts is the actual column for the Products there is no seperate
column/text box for products. I just need the Stock code information to store
in the table.
--
Thank you for your help

The Control Source for StockCode needs to be a field in the form's
underlying table or query. You could use the After Update event of
cboProducts to pass the Column(1) value to txtStockCode, which is in turn
bound to the StockCode field in the table, but you probably shouldn't do it
that way. Instead, set the bound column of cboProducts to 2, and the
visible column to whatever column contains the Product name. Columns are
counted from 1 on the property sheet, and from 0 in VBA and in expressions,
so it's the same column. You can look at the Product name whenever you want
in a form or report. If you want to see both the name and the StockCode,
just continue with setting the Control Source of the unbound txtStockCode to
=[cboProducts].[Column](1).

I have a Form & a Combo that populates a field automatically when a product
is selected. (If a Product is selected a Stock code for the Product is
populated in another column). The problem I have is the code shows on the
Form but does not store in the table.

Code for Combo Box:
Private Sub Product_AfterUpdate()
Me.txtStockCode = Me.cboProducts.Column(1)
End Sub

Control source for Stock code:
=[cboProducts].[Column](1)

 

[BruceM]

cboProduct must have StockCode as part of its Row Source or you would not be
able to pass the value to a text box. If StockCode and ProductName are part
of the same record in a Products table, and if StockCode is unique, there is
no need to store both values. The same thing applies if ProductName is
unique, even if StockCode isn't.

The cboProducts is the actual column for the Products there is no seperate
column/text box for products. I just need the Stock code information to
store
in the table.
--
Thank you for your help

The Control Source for StockCode needs to be a field in the form's
underlying table or query. You could use the After Update event of
cboProducts to pass the Column(1) value to txtStockCode, which is in turn
bound to the StockCode field in the table, but you probably shouldn't do
it
that way. Instead, set the bound column of cboProducts to 2, and the
visible column to whatever column contains the Product name. Columns are
counted from 1 on the property sheet, and from 0 in VBA and in
expressions,
so it's the same column. You can look at the Product name whenever you
want
in a form or report. If you want to see both the name and the StockCode,
just continue with setting the Control Source of the unbound txtStockCode
to
=[cboProducts].[Column](1).

I have a Form & a Combo that populates a field automatically when a
product
is selected. (If a Product is selected a Stock code for the Product is
populated in another column). The problem I have is the code shows on
the
Form but does not store in the table.

Code for Combo Box:
Private Sub Product_AfterUpdate()
Me.txtStockCode = Me.cboProducts.Column(1)
End Sub

Control source for Stock code:
=[cboProducts].[Column](1)

 

[John Vinson]

I just need the Stock code information to store
in the table.

Why?

If it can be looked up from the other table (the combo box's Row
Source) then it SHOULD NOT be stored in the second table. It's
redundant!

Access uses the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". You should store
the stock code once, and once only, and then use Queries to link to
the products table whenever it's needed.

If the stock code is unique within the products table, might it not be
a suitable Primary Key? Then you would have only the one field to
worry about!

John W. Vinson[MVP]