Cascading combo boxes

[Guest]

I have an access 2000 database with about 500.000 names and addresses. I have
a form called "Enter/View constituents. When I open the form, information on
the first constituent in the "ConstituentsTable" is displayed. I have a
combo box at the top of the form called "NewNameSearch". When I select a
name from the alphabetical list it populates the form with the new persons
information.
THE PROBLEM IS - The combo box is based on a query of LastName, FirstName
and MiddleName from the Constituents Table. I have to sort the names in
ascending order. The combo box will scroll down to approximately 60,000
records and will not go any furthur. For example: If I type in Depoe and
check the drop-down box, the list will only go to Depoe as Depoe is record
75,000. The last name I can scroll to is Clements which is record 60,000. If
I do not set the sort to LastName ascending then I can scroll through all
500,000 records and select anyone I want. As the table records ID are not in
perfect alpha order I have to have a sort by alpha to choose from. Can
someone show me an answer to the problem. Perhaps I could have a Combo box
first select a letter of the alpha and then have another combo to select
from. HELP!

 

[Douglas J. Steele]

Whoa! Even 60,000 names is far more than is reasonable to expect your users
to scroll through.

Take a look at what Allen Browne has at
http://www.allenbrowne.com/ser-32.html

 

[BillD]

Thanks for the info- I will look at it.
Actually, I like the combo being able to scroll through a
lot of names.When I enter a name such as Depoe and press
enter or the arrow for the dropdown, I get a list of say
20 names to start with and can scroll up or down to find
the person I want. Works great. As I said, when I don't
set the last,first and middle name to ascending, the
search is fast and complete. The only problem is that it
is not in alpha order. When I set the fields to ascending
then I only can retreive a name in the first 60,000 names.
The perfect set-up would be to have the list show up for
any of the names in alpha order and to be able to at least
look through all the names for that letter of the alpa.
Thanks again- will respond after I look at your suggestion.

 

[Van T. Dinh]

You may like to try a set of 2 cascaded ComboBoxes. The first ComoboBox
simply has the 26 letters of the alphabets (assuming English) and the second
ComboBox only show the names starting with the selected letter in the first
ComboBox.

See The Access Web for cascaded ComboBox:

http://www.mvps.org/access/forms/frm0028.htm

 

[BillD]

This sounds like a solution. I am not quite sure if I can
complete this as I am not a programmer but I will try. I
usually copy and paste the visual basis part from other
templates.
Thanks a lot. Bill D.

 

[Bill D]

I am trying to set up the combos from the article you
referred me to.It states that the RowSource of Combo1 is
set to a Table Category. I think that they mean Table
Field. In the second to the last Paragraph they ask you to
issue a requery on combo2 in combo1's control AfterUpdate
event. Problem-Combo1 already has an event in the
AfterUpdate event. I am not a programmer. I cannot
understand how this can work. Something seems to be
missing.
Summary of what could work:
1) NewTable say called NameSearchAlpha has the 26 letters
of the alphabet in a field called Alpha.
2) Combo1 RowSourceType should be set to Table/Query.
RowSource should be set to Alpha field in Table
NameSearchAlpha. AfterUpdate event in Combo1 is to include
the code for Privare Sub Combo1 AfterUpdate.
So far this is what we have. When I select combo1 on
the ConstituentsForm and select the dropdown list I have
all 26 letters of the alphabet.
3) The next step would be to create the NameSearchQuery.
The query would have fields LastName, FirstName,and
MiddleName from the ConstituentsTable. The Query is to use
combo1's value as a parameter. Where is the parameter
placed? Field, Table or Criteria of the NameSearchQuery.
Where do I go from here?

 

[Van T. Dinh]

The article actually explain 2 different scenarios. The first scenario is
up and including the code sample in blue background. The article did mean
the Table "Categories" from the sample database "NorthWind" that comes with
your Access software.

The second senario is what you wanted.

Se the RowSource Type of the combo2 to "Table / Query" and set the RowSource
to an SQL String, something like:

SELECT [LastName] & ", " & [FirstName]
FROM [YourTable]
WHERE [LastName] Like Forms!YourForm!combo1 & "*"

and the use the code at the end of the article.

Notes:
=====
1. Replace names above with appropriate names for your Table / Field / Form
/ Controls.

2. The reference

Forms!YourForm!combo1

is the Parameter in the Query / SQL String which doesn't have a value until
you select a character in the ComboBox. Hence, you need to requery the
combo2 after you select a value in combo1.

3. The WHERE clause in the SQL String above specify that only Records with
[LastName] starting with whatever the character selected in combo1 will be
selected into the list for combo2.

 

[Guest]

Thanks for the detailed description. I should be able to understand the
procedure after I work with it.

The article actually explain 2 different scenarios. The first scenario is
up and including the code sample in blue background. The article did mean
the Table "Categories" from the sample database "NorthWind" that comes with
your Access software.

The second senario is what you wanted.

Se the RowSource Type of the combo2 to "Table / Query" and set the RowSource
to an SQL String, something like:

SELECT [LastName] & ", " & [FirstName]
FROM [YourTable]
WHERE [LastName] Like Forms!YourForm!combo1 & "*"

and the use the code at the end of the article.

Notes:
=====
1. Replace names above with appropriate names for your Table / Field / Form
/ Controls.

2. The reference

Forms!YourForm!combo1

is the Parameter in the Query / SQL String which doesn't have a value until
you select a character in the ComboBox. Hence, you need to requery the
combo2 after you select a value in combo1.

3. The WHERE clause in the SQL String above specify that only Records with
[LastName] starting with whatever the character selected in combo1 will be
selected into the list for combo2.

--
HTH
Van T. Dinh
MVP (Access)

I am trying to set up the combos from the article you
referred me to.It states that the RowSource of Combo1 is
set to a Table Category. I think that they mean Table
Field. In the second to the last Paragraph they ask you to
issue a requery on combo2 in combo1's control AfterUpdate
event. Problem-Combo1 already has an event in the
AfterUpdate event. I am not a programmer. I cannot
understand how this can work. Something seems to be
missing.
Summary of what could work:
1) NewTable say called NameSearchAlpha has the 26 letters
of the alphabet in a field called Alpha.
2) Combo1 RowSourceType should be set to Table/Query.
RowSource should be set to Alpha field in Table
NameSearchAlpha. AfterUpdate event in Combo1 is to include
the code for Privare Sub Combo1 AfterUpdate.
So far this is what we have. When I select combo1 on
the ConstituentsForm and select the dropdown list I have
all 26 letters of the alphabet.
3) The next step would be to create the NameSearchQuery.
The query would have fields LastName, FirstName,and
MiddleName from the ConstituentsTable. The Query is to use
combo1's value as a parameter. Where is the parameter
placed? Field, Table or Criteria of the NameSearchQuery.
Where do I go from here?

 

[Guest]

Can't seem to get the combo boxes working properly. Will keep trying. Would
help if I knew what I was doing.

Thanks for the detailed description. I should be able to understand the
procedure after I work with it.

The article actually explain 2 different scenarios. The first scenario is
up and including the code sample in blue background. The article did mean
the Table "Categories" from the sample database "NorthWind" that comes with
your Access software.

The second senario is what you wanted.

Se the RowSource Type of the combo2 to "Table / Query" and set the RowSource
to an SQL String, something like:

SELECT [LastName] & ", " & [FirstName]
FROM [YourTable]
WHERE [LastName] Like Forms!YourForm!combo1 & "*"

and the use the code at the end of the article.

Notes:
=====
1. Replace names above with appropriate names for your Table / Field / Form
/ Controls.

2. The reference

Forms!YourForm!combo1

is the Parameter in the Query / SQL String which doesn't have a value until
you select a character in the ComboBox. Hence, you need to requery the
combo2 after you select a value in combo1.

3. The WHERE clause in the SQL String above specify that only Records with
[LastName] starting with whatever the character selected in combo1 will be
selected into the list for combo2.

--
HTH
Van T. Dinh
MVP (Access)

I am trying to set up the combos from the article you
referred me to.It states that the RowSource of Combo1 is
set to a Table Category. I think that they mean Table
Field. In the second to the last Paragraph they ask you to
issue a requery on combo2 in combo1's control AfterUpdate
event. Problem-Combo1 already has an event in the
AfterUpdate event. I am not a programmer. I cannot
understand how this can work. Something seems to be
missing.
Summary of what could work:
1) NewTable say called NameSearchAlpha has the 26 letters
of the alphabet in a field called Alpha.
2) Combo1 RowSourceType should be set to Table/Query.
RowSource should be set to Alpha field in Table
NameSearchAlpha. AfterUpdate event in Combo1 is to include
the code for Privare Sub Combo1 AfterUpdate.
So far this is what we have. When I select combo1 on
the ConstituentsForm and select the dropdown list I have
all 26 letters of the alphabet.
3) The next step would be to create the NameSearchQuery.
The query would have fields LastName, FirstName,and
MiddleName from the ConstituentsTable. The Query is to use
combo1's value as a parameter. Where is the parameter
placed? Field, Table or Criteria of the NameSearchQuery.
Where do I go from here?