Combo Box Problem

[Nick Bradbury]

Hi

I have a form with a combo box to select stock items, my problem is that the
items have an eleven character code, the first seven characters identify
the product and the last four characters identify the supplier and country
of origin. Consequently there are several options to choose from and
depending on what is available I need to be able to select different
products. What I want to achieve is to have one combo box to select the
first seven characters, which stay constant, and once this selection is made
a second combo box containing the list of alternatives for that product.

Is this possible and if so do I need to create separate tables for the seven
character codes and eleven charcter codes or can they exist as separate
fields in the same table


Thanks


Nick

 

[Graham R Seach]

Nick,

Take a look at the followiong website to show you how to cascade combo
boxes.
http://www.pacificdb.com.au/MVP/Code/ComboRS.htm

You can limit what you see in the "Products" combo by filtering its
rowsource. For example,
SELECT DISTINCT Left(ProductCode, 7) As Product,
Mid(ProductCode, 8, 2) As Supplier,
Mid(ProductCode, 10, 2) As CountryCode
FROM tblMyTable
ORDER BY Left(ProductCode, 7)

Set the ColumnCount = 3, and the ColumnWidths = 3cm;0cm;0cm (or imperial
measurement if you prefer)

To get at the product code, use Me!cboProductCode
To get at the supplier, use Me!cboProductCode.Column(1)
To get at the country code, use Me!cboProductCode.Column(2)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Nick Bradbury]

Hi Graham

Thanks for your help, that should do the job


Nick