more than 10000 rows in combobox

[Daniel]

how can i display more than 10000 rows in a combo box ?
I tried changing the MaxRecords (tools -> options -> max records) and set it
to 15000 - didn't help.

i also tried opening a recordset in this way:

dim rs as new adodb.recordset
rs.open "select ....", CurrentProject.Connection, adOpenDynamic,
adLockReadOnly
cmb.recordset = rs

gave me an error...

any ideas ?

 

[Klatuu]

This site has your answer:
http://allenbrowne.com/ser-32.html

 

[Daniel]

I read this solution but it's not so good for me because my combo is on a
repeated form (apears for each record).

other idea ?

 

[Klatuu]

Sorry, I don't

 

[Dale Fye]

Do you mean it is on a continuous form?

There is probably a better way than using a combo box. I rarely use
continuous forms for data entry, so you could probably replace the combo box
with a textbox and modify the query that populates the table so that you
return whatever text you need to populate what used to be a combo box.
Then, modify the form so that it does not allow additions, but has a "Add
Record" button that takes you to a data entry form that allows you to use
some other technique besides an extremely large combo box.

HTH
Dale

 

[Daniel]

it's the AutoComplete function that I realy need. Since there are a lot of
products and the users are assisted with this function.

can I make an Auto complete using a textbox ?

 

[John W. Vinson]

I read this solution but it's not so good for me because my combo is on a
repeated form (apears for each record).

So?

When you're selecting from it the code will work correctly. Why do you feel
that a continuous form won't work?

 

[Dale Fye]

Like I said, I would not use the continuous form as a data entry form. I
would use it to view records, but not to add or edit an individual record.
However, you could copy your continuous form and change it to a single
record form and use that for data entry. Generally, when I have a really
long list of stuff that I want to display in a combo box, but know that it
is too long, or may eventually be too long, is create a textbox (I'll call
it txt_combo) that I place right above (at least in design view) the combo
box (I'll call this cbo_Products). Keep in mind that the code provided
below is untested air code.

1. When the form opens, I change the Top property of the textbox so that it
lays right over the combo box.
1. I set the combo boxes visible property to False
2. I set the TabStop property of the Combo to No, so that you cannot tab
into it.
3. I then add some code to the Enter event of txt_Combo that highlights all
the text in the textbox. I do this so that if I start typing, all the text
that is currently in the textbox will be overwritten.
4. Then, in the Change event of txt_Combo I write a SQL string that selects
the records from the products table that contain the string that is in the
textbox. Something like:

Private sub txt_Combo_Change

Dim strSQL as string
strSQL = "SELECT * FROM tbl_Products WHERE ProductID Like '" &
me.txt_Combo.text & "*'"
me.cbo_Products.RowSource = strSQL
me.cbo_Products = me.txt_Combo.text
me.cbo_Products.visible = True
me.cbo_Products.SetFocus
me.cbo_Products.dropdown
me.txt_Combo.visible = false

End sub

This effectively takes the first character of input to the textbox,
populates the combo, makes it visible, then hides the textbox. Since it
isn't likely that you will have 10000 products that start with the same
number or letter, this will shrink the list for the combo and still allow
you to have the Autocomplete functionality of a combo box.

5. If you use this technique, you will need to use the Change event of the
combo box to make sure that the first letter of the combo matches that of
the textbox. If not, set the value of the textbox to that of the first
character of the combo and reexecute the textboxes change event. Something
like:

Private Sub cbo_Products_Change

if len(me.cbo_Products.text & "") = 0 Then
me.txt_Combo.visible = true
me.cbo_Products.visible = false
me.txt_Combo.setfocus
Elseif left(me.cbo_Products.text) <> left(me.txt_Combo.value) then
me.txt_Combo.value = left(me.cbo_Products.text)
Call txt_Combo_Change
endif

End Sub

Another way to accomplish this is to have a couple of command buttons above
the combo box (A-E, F-J, K-O, P-T, U-Z), or something like this, that the
user clicks to change the WHERE clause on the SQL string for the combo to
display only the products that start with these values.

HTH
Dale

 

[John W. Vinson]

Like I said, I would not use the continuous form as a data entry form. I
would use it to view records, but not to add or edit an individual record.

Ummm...???

Even for a many-side subform, e.g. OrderDetails in Northwind?

Sorry, Dale, but I emphatically disagree here. Continuous forms are very
useful for data entry and editing.

 

[Daniel]

The problem is ont at the data entry session, it's when the user wants to
view an existing list of items. if the combo doesnt contain all the items -
some would display empty - even though there's data in it.

Currently, I replaced the combo with a text box, and developed a form that
helps the user select the product easyli.
But it's still not a good solution for the long run, for systems that work
several years with multiple users - 10K records is a reality that must be
taken care of by MS.

 

[John W. Vinson]

Currently, I replaced the combo with a text box, and developed a form that
helps the user select the product easyli.

One possibility is to have a textbox superimposed on the text area of the
combo box, in front of it. That way the text shows for all values, but the
combo is still available - it "pops front" when dropped down.

 

[Dale Fye]

Well, if you are only talking about the couple of fields that would normally
be entered in the many side of a one-to-many, then, yes, I would use it for
data entry.

But I really try to keep my continuous subforms as small as possible, and
frequently run into situations where I need more info to fill out my table
than I want to display in the subform. In these instances, I generally put
Add and Edit buttons in the subforms footer, to call the actual data input
form.

Dale

 

[Dale Fye]

John,

How would you implement this in a continuous form? Wouldn't making changes
to the SQL of the RowSource of the current (new) record affect all of the
combo boxes, in each of the records?

That is why I recommended using the data input form for new records and
record edits.

Dale

 

[John W. Vinson]

But I really try to keep my continuous subforms as small as possible, and
frequently run into situations where I need more info to fill out my table
than I want to display in the subform. In these instances, I generally put
Add and Edit buttons in the subforms footer, to call the actual data input
form.

I've done that too of course; both techniques have their place.

 

[John W. Vinson]

John,

How would you implement this in a continuous form? Wouldn't making changes
to the SQL of the RowSource of the current (new) record affect all of the
combo boxes, in each of the records?

That is why I recommended using the data input form for new records and
record edits.

Dale

If you have a textbox (using a Query or DLookUp to show the text) superimposed
in front of the (variable rowsource) combo box, you get the best of both
worlds - at the cost of a bit more complexity.