Cascading Combo Boxes

[Guest]

I have a database with a table called "VoterInformationTable" This table
includes the fields "Last Name"," First Name" and "Middle Name". I have a
form called "VoterInformationTable". On the form I have a ComboBox that I use
to look up a name and then when I select the name from the list, the form is
populated with the info from the "VoterInformationTable".

Problem: I have approx. 500,000 names and addresses. The drop-down list in
the combo box will only allow me to look at approx. 55,000 names from the
"VoterInformationTable". Example- Say I type in Smith in the combo box. I
select the drop-down list. The name Smith will not show in the drop-down
list.The drop-down list will show a-c names only. The list has approx. 55,000
names from A to C. The combo is based on a Query with Last Name, First Name,
Middle Name sorted ascending. If I do not have the query set to sort
ascending, then the name Smith will show up. The name after Smith,Albert will
be the next name in the table that the query is based on, since the Table is
not in complete alpha order.

How can I correct this problem.

 

[JohnFol]

How about having a text entry for the criteria for a combo box?

ie txtFilterField is used to capture "Smith"
The record source for the combo is then
Select Last Name, First Name, Middle Name from VoterInformationTable where
LastName like Forms!VoterInformationTable!txtFilterField & "%" order by
LastName ascending

 

[Guest]

Don't quite understand. If I set the combo row source to The "NameSearch"
query and under Last Name criteria in the query I Type Like "D*", then I get
all the names sorted alphabetically that start with D. It is fast and
includes all the D's in the 500,000 name database. How can I set this up to
work from the combo on the form?

 

[JohnFol]

The query I suggested doesn't have any criteria that you enter. It
references the form for the value typed into the text box I called
txtFilterField. ie

LastName like Forms!VoterInformationTable!txtFilterField & "*"

( I think it should be * and not %)

if you type the letter "D" into the text box on the form and run the query
separately you should see the surnames beginnign with "D"


Now, provided you've got that working, you simply requery the combobox when
the value changes (on_AfterUpdate?)





To see it working, create a new form called SearchForm in NWind.
Add a text box called txtFilterField.
Add a combo box and set its record source to
SELECT LastName & ", " & FirstName FROM employees WHERE lastname Like
forms!SearchForm!txtFilterField & "*"
Add a piece of code to the form
Private Sub txtFilterField_AfterUpdate()
Me.Combo1.Requery
End Sub


Run the form.
In the text box type "D" and press return. Look at the contents of the
combo.
In the text box type "F" and press return. Look at the contents of the
combo.



Hope this clarifies the idea.