Prev: Forms Field List
Next: Combining two fields in a table
From: Hendrix on 18 Feb 2010 15:24
I created a form that works from a table. From the form you can enter
information and it will put it into the table. I would like to create
a field in the form where the user can selects what to be inputed from
a drop downlist. how would I do that?
From: KenSheridan via AccessMonster.com on 18 Feb 2010 16:49
You need to first create another table which contains the values you want to
list, each as a separate row in the table. If the values to be looked up are
unique, then the 'referenced' table and the 'referencing' table can have the
same column, e.g. a States table could have a State column, and the
referencing table, e.g. a table of Cities, can also have a State column.
These are known as 'natural' keys. Often, however, the values won't be
unique, e.g. a Cities table could have the same city name multiple times
because city names can legitimately be duplicated. So in this case the
Cities table would have a CityID column, and a City column and a State column.
In this case the CityID is a 'surrogate' key, most probably an autonumber, to
give each row a unique identifier. A referencing table would then also have
a numeric CityID column, but not an autonumber this time.

Taking cities as an example, a combo box on a form based on a referencing
table, e.g. of addresses, would be set up like this:

Name: cboCity

ControlSource: CityID

RowSource: SELECT CityID, City, State FROM Employees ORDER BY City,
State;

BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0cm;3cm;3cm
ListWidth: 6cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first
ColumnWidths dimension is zero to hide the first column. Experiment with the
other two to get the best fit. The ListWidth is the sum of the ColumnWidths.

In this example, having selected a city from the list you'll se its name in
the combo box. To see the state for the selected city you can add an unbound
text box to the form with a ControlSource of:

=cboCity.Column(2)

The Column property is zero-based, so Column(2) is the third column, i.e. the
state. Note that this means you don't need, and shouldn't have, a State
column in the table of addresses. That would be redundancy and leave the
table at risk of inconsistent data. Storing just the CityID automatically
tells you the state via the relationships.

When you create a referencing (aka 'lookup') table like this you should
create a relationship between it and the referenced table (on CityID in this
case) and enforce referential integrity. This ensures that (a) only valid
values can be entered in the referencing table, and (b) a row cannot be
deleted from the referenced table while a matching row still exists in the
referencing table. The integrity of the data is thus protected.

Finally, a word of warning. The 'lookup wizard' you see listed in the data
types of a field in table design view will build this sort of thing for you.
Don't use it! For reasons why see:

http://www.mvps.org/access/lookupfields.htm

Ken Sheridan
Stafford, England

Hendrix wrote:
>I created a form that works from a table. From the form you can enter
>information and it will put it into the table. I would like to create
>a field in the form where the user can selects what to be inputed from
>a drop downlist. how would I do that?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1

From: KARL DEWEY on 18 Feb 2010 16:51
Search on List Box and Combo Box as they both will do that but have some
different features.
--
Build a little, test a little.


"Hendrix" wrote:

> I created a form that works from a table. From the form you can enter
> information and it will put it into the table. I would like to create
> a field in the form where the user can selects what to be inputed from
> a drop downlist. how would I do that?
> .
>
From: Hendrix on 18 Feb 2010 16:57
On Feb 18, 4:49 pm, "KenSheridan via AccessMonster.com" <u51882(a)uwe>
wrote:
> You need to first create another table which contains the values you want to
> list, each as a separate row in the table.  If the values to be looked up are
> unique, then the 'referenced' table and the 'referencing' table can have the
> same column, e.g. a States table could have a State column, and the
> referencing table, e.g. a table of Cities, can also have a State column.
> These are known as 'natural' keys.  Often, however, the values won't be
> unique, e.g. a Cities table could have the same city name multiple times
> because city names can legitimately be duplicated.  So in this case the
> Cities table would have a CityID column, and a City column and a State column.
> In this case the CityID is a 'surrogate' key, most probably an autonumber, to
> give each row a unique identifier.  A referencing table would then also have
> a numeric CityID column, but not an autonumber this time.
>
> Taking cities as an example, a combo box on a form based on a referencing
> table, e.g. of addresses, would be set up like this:
>
> Name:    cboCity
>
> ControlSource:    CityID
>
> RowSource:     SELECT CityID, City, State FROM Employees ORDER BY City,
> State;
>
> BoundColumn:   1
> ColumnCount:  3
> ColumnWidths:  0cm;3cm;3cm
> ListWidth:   6cm
>
> If your units of measurement are imperial rather than metric Access will
> automatically convert them.  The important thing is that the first
> ColumnWidths dimension is zero to hide the first column.  Experiment with the
> other two to get the best fit.  The ListWidth is the sum of the ColumnWidths.
>
> In this example, having selected a city from the list you'll se its name in
> the combo box.  To see the state for the selected city you can add an unbound
> text box to the form with a ControlSource of:
>
> =cboCity.Column(2)
>
> The Column property is zero-based, so Column(2) is the third column, i.e. the
> state.  Note that this means you don't need, and shouldn't have, a State
> column in the table of addresses.  That would be redundancy and leave the
> table at risk of inconsistent data.  Storing just the CityID automatically
> tells you the state via the relationships.
>
> When you create a referencing (aka 'lookup') table like this you should
> create a relationship between it and the referenced table (on CityID in this
> case) and enforce referential integrity.  This ensures that (a) only valid
> values can be entered in the referencing table, and (b) a row cannot be
> deleted from the referenced table while a matching row still exists in the
> referencing table.   The integrity of the data is thus protected.
>
> Finally, a word of warning.  The 'lookup wizard' you see listed in the data
> types of a field in table design view will build this sort of thing for you.
> Don't use it!  For reasons why see:
>
> http://www.mvps.org/access/lookupfields.htm
>
> Ken Sheridan
> Stafford, England
>
> Hendrix wrote:
> >I created a form that works from a table. From the form you can enter
> >information and it will put it into the table. I would like to create
> >a field in the form where the user can selects what to be inputed from
> >a drop downlist. how would I do that?
>
> --
> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/20...

Can I set it so that it is the default of the list is empty?
From: KenSheridan via AccessMonster.com on 18 Feb 2010 17:16
I don't follow. Do you mean an empty list, which seems a bit pointless. Or
that the control is empty before you select an item from the list, which is
what happens already. A combo box will be empty until a value is selected, a
list box will show all items, but not have any selected until you select one.
For a bound control a combo box would generally be used. List boxes are
better suited for unbound controls for interrogating the database rather than
as bound controls.

Ken Sheridan
Stafford, England

Hendrix wrote:
>On Feb 18, 4:49 pm, "KenSheridan via AccessMonster.com" <u51882(a)uwe>
>wrote:
>> You need to first create another table which contains the values you want to
>> list, each as a separate row in the table. If the values to be looked up are
>[quoted text clipped - 64 lines]
>> --
>> Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/20...
>
>Can I set it so that it is the default of the list is empty?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201002/1

 | 
Pages: 1
Prev: Forms Field List
Next: Combining two fields in a table