Multi-column combo box

[Jim]

I have a table called "Inventory Items" used as a lookup
table. I created a multi-column combo box which uses two
fields from the lookup table; ItemCode and
ItemDescription. This table is linked to another
table, "Inventory Transactions" in a one-to-many
relationship. I created a Purchase Order with the idea
that all data entered in to the Purchase Order form will
be stored in the Inventory Transaction table. The combo
box stores its bound colum "ItemCode" in the Inventory
Transaction tablejust fine. But I can't seem to get the
value of the second column "ItemDescription" to store in
it's corresponding field in the Inventory Transaction
table. I have tried "=Combobox.Column(1)" in a Textbox
Control Source property which does display the Item
Description on the form but it doesn't store the Item
Description in the Inventory Transaction table. I can
understand why since the control source dictates where
the data will be stored. So then I tried using the same
expression in the Default Value property and set the
Control Source to the Inventory Transaction table to no
avail. I need the ItemCode and ItemDescription stored in
the Inventory Transaction table.

 

[Bogdan Zamfir]

Hi,

The value from a control is stored to an underlying table or query (a field
of it) only if the ControlSource is set to a field from the RecordSource. If
you bound a control to another control (as you do you the textbox, which is
bounded to column 1 of ComboBox), textbox is used only to display the value
from the control it is bounded to, and also it is R/O (you cannot edit it).

In order to be able to update a field in the underlying table, you need to
write some code in AfterUpdate event procedure for combo-box.

As below:

private sub ComboBox_AfterUpdate()
me!ItemDescription = me.ComboBox.Column(1)
end sub

However, I have a question. As long as you store the ItemID in
InventoryTransactions table, why do you need to store Description too? Is
description for a given item changing from time to time, and you want to
know its value at the moment you recorded the order? Otherwise you don't
have to store ItemDescription, ItemID is enough, since you can create a
query anytime to retrieve the ItemDescription from Inventory Items table.

Storing Item Description to InventoryTransactions table cause database to be
denormalized, and this is a bad design.

If you need more info or help on this issue, you can contact me.

HTH,
Bogdan Zamfir
________________________

Independent consultant

 

[Van T. Dinh]

I presumed that the ItemCode uniquely determines the Item (type) that
involved. In this case, storing the ItemCode in the Table [Inventory
Transactions] is enough since from this ItemCode, you can "look up" the
Table [Inventory Items] to find the corresponding ItemDescription.

My guess is that you are (inadvertently) trying to store duplicated data
which violated the Relational Database Design Principles. That's the whole
idea of Relational Database Design: to avoid storing duplicated data!
Perhaps, that's why Access makes it hard to do what you wanted to do (but it
IS still possible to do so).

Perhaps, you should check out Relational Data Design Theory / Principles and
Database Normalisation and ensure that your Database Structure meets the
requirements of these.

 

[Jim]

I don't think you understand what I'm trying to do. I
need to store the description so I can print reports such
as a purchase Order using descriptions instead of a code.
My vendors don't know what my codes mean.

-----Original Message-----
I presumed that the ItemCode uniquely determines the Item (type) that
involved. In this case, storing the ItemCode in the Table [Inventory
Transactions] is enough since from this ItemCode, you can "look up" the
Table [Inventory Items] to find the corresponding ItemDescription.

My guess is that you are (inadvertently) trying to store duplicated data
which violated the Relational Database Design Principles. That's the whole
idea of Relational Database Design: to avoid storing duplicated data!
Perhaps, that's why Access makes it hard to do what you wanted to do (but it
IS still possible to do so).

Perhaps, you should check out Relational Data Design Theory / Principles and
Database Normalisation and ensure that your Database Structure meets the
requirements of these.

--
HTH
Van T. Dinh
MVP (Access)

I have a table called "Inventory Items" used as a lookup
table. I created a multi-column combo box which uses two
fields from the lookup table; ItemCode and
ItemDescription. This table is linked to another
table, "Inventory Transactions" in a one-to-many
relationship. I created a Purchase Order with the idea
that all data entered in to the Purchase Order form will
be stored in the Inventory Transaction table. The combo
box stores its bound colum "ItemCode" in the Inventory
Transaction tablejust fine. But I can't seem to get the
value of the second column "ItemDescription" to store in
it's corresponding field in the Inventory Transaction
table. I have tried "=Combobox.Column(1)" in a Textbox
Control Source property which does display the Item
Description on the form but it doesn't store the Item
Description in the Inventory Transaction table. I can
understand why since the control source dictates where
the data will be stored. So then I tried using the same
expression in the Default Value property and set the
Control Source to the Inventory Transaction table to no
avail. I need the ItemCode and ItemDescription stored in
the Inventory Transaction table.

.

 

[Rick Brandt]

I don't think you understand what I'm trying to do. I
need to store the description so I can print reports such
as a purchase Order using descriptions instead of a code.
My vendors don't know what my codes mean.

He understood, but his point was that you base your reports on queries that
include the lookup table so they can be displayed in your report without
storing them redundantly. That is the proper way to do it.

 

[Van T. Dinh]

It is exactly as I wrote!

The need to print the ItemDescription in the Purchase Order should NOT
affect the Structure of your Tables. According to the RDDT, each Table
should only store details about ONE entity. ItemDescription belongs to the
"Item" entity, NOT the "Transaction" entity.

You can base your Report on a Query that combines the 2 Tables and include
the ItemDescription from the Table [Inventory Items] and thus, it is
available for the Report without being stored in the Table [Inventory
Transactions].

Once you understand the RDDT, look at the sample database NorthWind that
comes with your Access software.