Work Arround 65536 limit for a dropdown

[MKH2001]

Have a form with a combo box from which users can select the UNITID.

Since this is a very format sensitive field and the format isn't easily recognizeable it cant be expected that the User can manually input available on the combo it correctly so the autocomplete is necessary.

A combo will only allow them to select 65536 of these records which I understand is a limitation of Access.

Is their anyway to work around this limitation?

 

[lupins46]

In my view you need to find a different approach to record selection. There is no way round the limit, but you shouldn't even be contemplating doing such a thing.

Give the user a textbox where they can enter the first few characters and click a button to retrieve a small number of matches to select from (using a combo box)

 

[jebenson]

You should seriously rethink an application design that requires users to wade through 66000+ combobox entries to get the one they want. Think about some sort of filter, group/category, etc. that can reduce the number of UNITIDs in the combobox at any given time. Your users will thank you - or at least they won't curse your name!

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[MKH2001]

Its not that I want them to wade through so many.
Its just with autocomplete I could guarantee that they use the correct formatting and thereby select the correct UnitID.
I will have a look at some method of filtering through a query.... but it is something I need to make seamless from the usual method they would use to pick from a combo.

Else I have to announce that they are doing something different in sky high Neon letters and alarm bells.

 

[ZmrAbdulla]

There is a little croocked way, may not be fast.
You need to have 27 buttons(26 alpha and 1 number) on the form. User will click a letter/number then pass SQL to the combo filtered by that letter/number. So you can have total 1769472(65536*27)entries in the combo. Then user can just type in to the combo as normal.

________________________________________________________________________
Zameer Abdulla
Visit Me
By the time a man realizes that may be his father was right,
he usually has a son who says, "Father, you are wrong!".

 

[gol4]

there is also the ability to fill the combobox as they type so you are returning a much smaller recordset with each letter.
start blank
I type letter A

Private Sub Combo0_Change()
Dim strsql As String
strsql = ""select foo from foobar where foo like '" & me.combox0.text & "*'"
Me.Combo0.RowSource = strsql
End Sub

would need to add code for no matches etc but