Combo box (1 Viewer)

[amavadia]

I have a combo box on a form that allowes the user to select a record to be displayed in the form. The problem is i have about 3000 records in the table. Is there any way you can create a drop down menu with all the letters of the alphabet, and then use that to filter the records available in the drop down menu?

 

[amavadia]

I have a combo box on a form that allowes the user to select a record to be displayed in the form. The problem is i have about 3000 records in the table. Is there any way you can create a drop down menu with all the letters of the alphabet, and then use that to filter the records available in the drop down menu?

 

[KenHigg]

I have a combo box on a form that allowes the user to select a record to be displayed in the form. The problem is i have about 3000 records in the table. Is there any way you can create a drop down menu with all the letters of the alphabet, and then use that to filter the records available in the drop down menu?

You may be able to do this with what's referred to as cascading combo boxes. Do a search on the forum and you should should find plenty of posts on the topic...

 

[Bobadopolis]

Set the rowsource of the combo box to be a "value list" with one column. The rowsource will be:

cboLetters:

a;b;c;d;e;f;g;h(...);z

The Second combo would have a rowsource something like:

cboSearch:

SELECT YourTable.YourField FROM YourTable WHERE YourField like [Forms]![MyForm]![cboLetters] & "*") FROM YourTable ORDER BY YourTable.YourField;

In the query that the form runs from you want a criteria in the field that you're filtering by:

Like [Forms]![MyForm]![cboSearch] & "*"

In the AfterUpdate event of cboLetters you need to requery the second combo:

Me.cboSearch.Requery

In the AfterUpdate event of the cboSearch you need to requery the Form:

Me.Requery

That should do the trick

Bobadopolis

 

[RuralGuy]

And here's MVP Allen Brown's suggested solution: Combos with Tens of Thousands of Records

 

[Sergeant]

There are many ways to improve this situation.
One thing I have done is NOT populate the combo box until the third letter is typed by the user.
Do this by making a 'Change' event behind your combo box. In the event, do something like:
If Len(Me.MyComboBox.Text) > 2 Then
Me.MyComboBox.RowSource = "SELECT........WHERE MyText Like '" & Me.MyComboBox.Text & "*'"
Me.MyComboBox.DropDown
End If