Maximum data limitation ? or SQL failure.

M

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.
 
J

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
 
M

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


John Spencer said:
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.
Click to expand...
Click to expand...
 
J

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).
Click to expand...

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
 
Top