Combobox Lookup

[Kenneth Hutson]

Hi,

I am pretty much a novice at this, but this must be a simple thing to do.

I have an Access database with tables offices and states. Offices has a
stateid (long integer) column. States table has a stateid (long integer) and
a column statename (text) where the state name is spelled out. The tables
are related on stateid.

I want to display the offices table information a form (VB.Net 2005
Express). The DataGridView control looks like a good control for my purpose.
I would like to have the states column to be a combobox control. The user
would see the spelled out state name in the combobox. A selection from the
combobox would store the stateid from the states table into the offices
table.

Is this possible to do? If so, I have other tables which could benefit from
a similar treatment. If someone could provide a step by step list of
instructions to illustrate this technique, it would provide me a greater
insight and would help me greatly.

Thanks,
Kenneth Hutson
San Antonio, TX

 

[Bart Mermuys]

Hi,

Hi,

I am pretty much a novice at this, but this must be a simple thing to do.

I have an Access database with tables offices and states. Offices has a
stateid (long integer) column. States table has a stateid (long integer)
and a column statename (text) where the state name is spelled out. The
tables are related on stateid.

I want to display the offices table information a form (VB.Net 2005
Express). The DataGridView control looks like a good control for my
purpose. I would like to have the states column to be a combobox control.
The user would see the spelled out state name in the combobox. A selection
from the combobox would store the stateid from the states table into the
offices table.

Is this possible to do? If so, I have other tables which could benefit
from a similar treatment. If someone could provide a step by step list of
instructions to illustrate this technique, it would provide me a greater
insight and would help me greatly.

Do you already have created a Data Source for the two tables ? I'll start
with that:

1. Create Data Source (Typed DataSet):
- Open Data Sources window ( Menu -> Data )
- Create a new Data Source, choose your DB, choose the tables (offices and
states), finish wizard. (If the wizard asks you to make a copy of the DB
and import it into your project, choose "No")

2. Create data entry form
- From the Data Source window drag the offices table onto the Form, this
should setup a basic data entry form with a DataGridView.

3. Configure state_id column
- Right-click on the DataGridView and choose Edit Columns
- Select the state_id column (on the left)
- Configure column properties:
- Change ColumnType to DataGridViewComboBoxColumn
- For the DataSource select: Other Data Sources - Project Data Sources -
YourDataSet - states, this should create a StatesBindingSource and
StatesTableAdapter on the Form.
- For the DisplayMember choose "statename"
- For the ValueMember choose "stateid"

That should (basicly) do it.

HTH,
Greetings

 

[Kenneth Hutson]

Bart,
Exact and concise. Just what I was looking for. I can't tell you how much
simpler these instructions are compared to patchy Microsoft docs!
Thanks Again,
Kenneth Hutson

 

[Earl]

As Kenneth has already taken care of your main issue, I'll only make the
observation that this is one of the few situations where a "natural" key
would be a better choice than a surrogate identity for your States table and
the relationship back to the Offices table (assuming you have control over
the design). State abbreviations aren't likely to change, and they are
compact enough that a char(2) datatype is as efficient as an int(4). A
single column lookup table might seem odd, but is certainly more concise and
"natural" than using an additional identity column just to make the
relationship. In all of those cases where every value in the list will be
unique, you might want to consider using a natural key (note that even where
your State entity had additional attributes, use of the natural key is still
correct).