This is getting complicated. Maybe it should be called a filter field instead of a combobox.
So I’ll attempt an example use case.
You have a field that you need to enter an item code for an item.
But you have 80k items and don’t remember the item exactly.
Start typing the beginning of the item and it autocompletes while showing a dropdown list of the filtered items.
Because the item code may not be very relevant the drop down list also includes a ‘description’ and ‘price’ column.
In addition to that you might not remember the item code at all. Or you want to search for ‘shovels’. No problem the dropdown list already includes all items where the description matches the text typed in.
the idea is that the dropdown list constantly changes to show a list of items that are relevant to the search being entered.
the dropdown list shows a list of returned records. The PK is know, as it was returned from the query. How ever the listindex is a totally different story as it is somewhat disconnected from the PK.
Here is a GIF to show you what I’m trying to replace. And yes we really do have over 80k items.
I’ve overwritten most of the combobox methods so it doesn’t really contain any actual records. All the listitems are stored in my own internal in memory db.
ListCount is also overwritten and was the simplest of all.
Get
if db <> nil then
dim rs as RecordSet = db.SQLSelect("SELECT COUNT(*) Count FROM CB")
Return rs.field("Count").IntegerValue
Else
dim n() as String = InitialValue.Split(EndOfLine)
Return n.Ubound + 1
end if
End Get
I have done that. What I do is store additional information in the non-visible columns beyond ColumnCount. You can store up to 64 columns (it might be more now). In fact, I store enough data in the listbox to satisfy the display requirements of the form without having to hit the database again. That doesn’t apply in your case, of course, but you could easily store the PK in an unused column.
Ok got this working good. I have an array to hold the positions of the record ids. When the list is filtered it also ordered by the column being searched. The problem is that when clicking on the drop down arrow, an unsorted list is expected to be shown. This works until I insert new rows at the beginning. No those new items are shown at the bottom of the list.
The only solution I can think of is looping through the array and doing a select for each item. This seems really cumbersome and plain wrong.
for i as integer = 0 to idx.ubound
rs = db.SQLSelect("SELECT * FROM CB WHERE ID = " + idx(i))
'add the record the the combobox
next
