Maximum data limitation ? or SQL failure.

[Maxz]

Had no luck finding an answer that made any sense, so here we go.

I have an unbound list box, that as you type the part number it scrolls thru
the list.
Also if you alt+arrow down you can view an extra 8 numbers scrolling below.

Sounds and looks simple.... See code for box
SELECT DISTINCTROW tblInventoryExtend.[Part#],
tblInventoryExtend.Description FROM tblInventoryExtend;

The problem is that up to the RR's this works fine record #65539 top and
bottom.
But 65539 to 75810 donot scroll in the bottom scroll area.

 

[John Spencer]

You are limited to 64K records in a listbox (or combobox).

A way to handle this is not to populate the listbox until the first few
characters have been typed into the combobox and then grab them and use them
as a filter on the query that you are using to populate the listbox.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Maxz]

I like the idea, thanks on the lmitation info.
Do you have any sample of how to apply that type of rolling filtering.
--
always try, try always

You are limited to 64K records in a listbox (or combobox).

A way to handle this is not to populate the listbox until the first few
characters have been typed into the combobox and then grab them and use them
as a filter on the query that you are using to populate the listbox.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Had no luck finding an answer that made any sense, so here we go.

I have an unbound list box, that as you type the part number it scrolls thru
the list.
Also if you alt+arrow down you can view an extra 8 numbers scrolling below.

Sounds and looks simple.... See code for box
SELECT DISTINCTROW tblInventoryExtend.[Part#],
tblInventoryExtend.Description FROM tblInventoryExtend;

The problem is that up to the RR's this works fine record #65539 top and
bottom.
But 65539 to 75810 donot scroll in the bottom scroll area.

 

[John Spencer]

Sorry, but I don't have anything handy.

The general idea would to leave the Row source blank and then use the
combobox's On Change event to detect when the text property of the combobox is

= 3 (for example).

UNTESTED AIR CODE follows.
Private Sub ComboBox1_Change()

IF Len(Me.Combobox1.Text & "") = 3 Then
Me.Combobox1.RowSource = "SELECT Field1 FROM SomeTable" & _
" Where Field1 Like """ & me.Combobox.Text & "*"" " & _
" ORDER BY Field1"
ElseIf Len(Me.ComboBox1.Text & "") < 3 Then
ME.ComboBox1.RowSource = ""
END IF

End Sub
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County