A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Forms Drop down list



 
 
Thread Tools Display Modes
  #1  
Old February 18th, 2010, 09:24 PM posted to microsoft.public.access.gettingstarted
Hendrix[_3_]
external usenet poster
 
Posts: 10
Default Forms Drop down list

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?
  #2  
Old February 18th, 2010, 10:49 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Forms Drop down list

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/For...arted/201002/1

  #3  
Old February 18th, 2010, 10:51 PM posted to microsoft.public.access.gettingstarted
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Forms Drop down list

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?
.

  #4  
Old February 18th, 2010, 10:57 PM posted to microsoft.public.access.gettingstarted
Hendrix[_3_]
external usenet poster
 
Posts: 10
Default Forms Drop down list

On Feb 18, 4:49*pm, "KenSheridan via AccessMonster.com" u51882@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?
  #5  
Old February 18th, 2010, 11:16 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Forms Drop down list

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@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/For...arted/201002/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.