S
stacie
Very basic question: How do I add list boxes to a form? I want to manually
input a list of names.
input a list of names.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Golfinray
In your form toolbar, there is an icon for list boxes. Click and then drop on
your form in the location desired.
your form in the location desired.
S
stacie
Another question, how can I allow the user to only choose what I have entered
manually, rather than adding his own choice. For example, I only want user
to pick from red, blue, yellow - but user wants to enter orange instead?
manually, rather than adding his own choice. For example, I only want user
to pick from red, blue, yellow - but user wants to enter orange instead?
S
Scott Shearer
Sounds like you really want to use a combo box rather than a list box. If
you use a combo box, set the Row Source type to be "Value List". Enter what
you want to appear as choices in the Row Source property with semicolons
between your choices. Finally, set the "limit to list" property to true.
As always, you will need to bind the control to a column using the Control
Source property.
you use a combo box, set the Row Source type to be "Value List". Enter what
you want to appear as choices in the Row Source property with semicolons
between your choices. Finally, set the "limit to list" property to true.
As always, you will need to bind the control to a column using the Control
Source property.
J
Jeff Boyce
Stacie
Be aware that a manually-entered list (whether into a listbox or a combobox)
is more difficult to keep up-to-date.
A more common (and more easily-maintained) approach is to use a table to
hold the list, and a query to select items from the table that your listbox
or combobox will hold.
Regards
Jeff Boyce
Microsoft Office/Access MVP
Be aware that a manually-entered list (whether into a listbox or a combobox)
is more difficult to keep up-to-date.
A more common (and more easily-maintained) approach is to use a table to
hold the list, and a query to select items from the table that your listbox
or combobox will hold.
Regards
Jeff Boyce
Microsoft Office/Access MVP
S
Scott Shearer
Jeff - I absolutely agree. I should have suggested that maintaining the list
in a table is the "best practice". Both ways will work, but it is
preferable, for a variety of reasons, to maintain the list in a table. My
thought was this is a question from a new user and I wanted to present the
most simple way to get it done.
in a table is the "best practice". Both ways will work, but it is
preferable, for a variety of reasons, to maintain the list in a table. My
thought was this is a question from a new user and I wanted to present the
most simple way to get it done.
S
stacie
thank you both
K
Ken Sheridan
Stacie:
If you use a combo box, which sounds a more appropriate solution here, set
the control's LimitToList property to True (Yes) as Scott describes. With a
list box the user can't enter rows manually, so the question doesn't arise.
However, do not use a value list as the RowSourceType property unless the
list is one of terms fixed in the external world, e.g. days of the week,
months of the year etc. If you use a value list to compile a list of values
of your own making you are storing data other than as values at row positions
in columns in tables. A fundamental principle of the relational model is
that data is stored in this way and no other way (its called the 'information
principle').
So, taking your hypothetical example you'd have a table Colours with column
Colour. If this only contains the colours you want listed the control's
RowSource property would be:
SELECT Colour FROM Colours ORDER BY Colour;
If it’s a general purpose 'colour-chart' and contains more colours than
those to be listed you'd use:
SELECT Colour FROM Colours WHERE Colour IN("red","blue","yellow") ORDER BY
Colour;
It might not be quite so straightforward of course. Say for instance in an
international database you want to list all cities in the USA :
SELECT CityID, City, Region
FROM Cities INNER JOIN Regions
ON Region.RegionID = Cities.RegionID
WHERE Country = "USA"
ORDER BY Region, City;
The query used as the RowSource property here uses two tables Cities and
Regions, the former with CityID, City and RegionID columns, the latter with
RegionID, Region and Country columns (you'd also have a Countries table to
control data integrity, but its not necessary to use it in this context as a
Country column exists in the Regions table).
Note how it also returns a CityID column. This is the primary key of
Cities; City can't be used as the key because City names are duplicated (I
know there are at least 4 Staffords in the USA as well as the original one
where I live – we are twinned with them). This means the combo box has to be
set up as follows to hide the CityID column and show the names of the cities
and regions (states in the USA of course)
BoundColum: 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 dimension
is zero to hide the first column. Its usually necessary to experiment with
the other two column widths dimensions to get the best fit. The ListWidth
should be the sum of the ColumnWidths dimensions.
Of course with the above example once a city is selected the relevant region
would not show in the combo box (a list box would show both columns all the
time of course), so the usual solution is to add an unbound text box control
to the form to show the region for the selected city, by making its
ControlSource property:
=[cboCity].[Column](2)
where cboCity is the name of the combo box. The Column property is
zero-based, so Column(2) is the third column, Region.
Ken Sheridan
Stafford, England
If you use a combo box, which sounds a more appropriate solution here, set
the control's LimitToList property to True (Yes) as Scott describes. With a
list box the user can't enter rows manually, so the question doesn't arise.
However, do not use a value list as the RowSourceType property unless the
list is one of terms fixed in the external world, e.g. days of the week,
months of the year etc. If you use a value list to compile a list of values
of your own making you are storing data other than as values at row positions
in columns in tables. A fundamental principle of the relational model is
that data is stored in this way and no other way (its called the 'information
principle').
So, taking your hypothetical example you'd have a table Colours with column
Colour. If this only contains the colours you want listed the control's
RowSource property would be:
SELECT Colour FROM Colours ORDER BY Colour;
If it’s a general purpose 'colour-chart' and contains more colours than
those to be listed you'd use:
SELECT Colour FROM Colours WHERE Colour IN("red","blue","yellow") ORDER BY
Colour;
It might not be quite so straightforward of course. Say for instance in an
international database you want to list all cities in the USA :
SELECT CityID, City, Region
FROM Cities INNER JOIN Regions
ON Region.RegionID = Cities.RegionID
WHERE Country = "USA"
ORDER BY Region, City;
The query used as the RowSource property here uses two tables Cities and
Regions, the former with CityID, City and RegionID columns, the latter with
RegionID, Region and Country columns (you'd also have a Countries table to
control data integrity, but its not necessary to use it in this context as a
Country column exists in the Regions table).
Note how it also returns a CityID column. This is the primary key of
Cities; City can't be used as the key because City names are duplicated (I
know there are at least 4 Staffords in the USA as well as the original one
where I live – we are twinned with them). This means the combo box has to be
set up as follows to hide the CityID column and show the names of the cities
and regions (states in the USA of course)
BoundColum: 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 dimension
is zero to hide the first column. Its usually necessary to experiment with
the other two column widths dimensions to get the best fit. The ListWidth
should be the sum of the ColumnWidths dimensions.
Of course with the above example once a city is selected the relevant region
would not show in the combo box (a list box would show both columns all the
time of course), so the usual solution is to add an unbound text box control
to the form to show the region for the selected city, by making its
ControlSource property:
=[cboCity].[Column](2)
where cboCity is the name of the combo box. The Column property is
zero-based, so Column(2) is the third column, Region.
Ken Sheridan
Stafford, England
K
Ken Sheridan
Stacie:
Oops, I should have said "at column positions in rows in tables". Here's
chapter and verse:
'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000
Ken Sheridan
Stafford, England
Oops, I should have said "at column positions in rows in tables". Here's
chapter and verse:
'The entire information content of the database is represented in one and
only one way, namely as explicit values in column positions in rows in
tables'.
C J Date - Introduction to Database Systems; 7th Edition; 2000
Ken Sheridan
Stafford, England