Combobox rowsource change "on the fly"

[Thevince]

Hi everybody

I have a drop down list and I want that the rowsource changes on the fly.
The code written here works perfectly for the dropdownlist but the control itself works not good

When I type "all", the combobox list shows all the items with "all" in it, that's perfect.

When i type "bo", the combobox.Value automaticly shows an item like "boiler" and the combobox list shows all the "boiler" If I need "bottle", I have to delete the letters from boiler" to "bo" and add "ttle"

Sometimes it works, sometimes not.

That's not what I want.

My access is version 2013 in french

Here is my code

Private Sub Modifiable12_Change()

   rsql = "SELECT produits.id_produit, produits.description " & _
        "FROM produits " & _
        "WHERE description Like " & Chr(34) & "*" & Me.Modifiable12.Text & "*" & Chr(34) & _
        " ORDER BY description;"
  Me.Modifiable12.RowSource = rsql
  Me.Modifiable12.RowSourceType = "Table/Query"
  Me.Modifiable12.Dropdown
 
End Sub

It seems that the combobox.Value automaticly selects a Sourcerow.item. Is there a way to avoid this or do you have another way to do this trick ?

I hope you understand the problem and that my english is not stoo bad

 

[vbaInet]

Something like this?
http://baldyweb.com/CascadingCombo.htm

 

[pbaldy]

I haven't needed this, but see if Allen's method helps:

http://allenbrowne.com/ser-32.html

 

[Thevince]

Thanks for fast reply

No, I have only one combobox that must change on the fly ... Not cascading one's

Another idea ?

 

[vbaInet]

The Row Source doesn't need changing. All it needs is a change in criteria and a Requery which is mentioned in pbaldy's site.

 

[Thevince]

Hi

I've tried many thing but nothing changes

What do you mean with "change in criteria" ?

A requery forces my combobox.value to a blank string and I can only put 1 letter instead of more.

You can see a video of my problem

I may not post video links because I have less than 2 posts and I don't want to lose time. But here is a link to my problem.
Replace ";" with "."

www;clicinfo;be/access/access;mp4

Thanks for any good idea

 

[JHB]

Use a listbox and above a unbond text-control, an example database is attached.

 

[Thevince]

Hi JHB and thanks for the post.

That works perfect but unfortunately I really need a combobox because I'll need it in a subform to chose a product in order lines. That's why a listbox is not what I need.

Thanks

Is it so complicated ? I mean ... that's a basic in searching items. Search something like. Heho Microsoft! do you hear me ?

Kind regards

 

[vbaInet]

  Me.Modifiable12.RowSource = rsql
  Me.Modifiable12.Dropdown
 
End Sub

Hmmm... those two lines of code should be sufficient. I think what you're looking for is in the link pbaldy sent.

 

[Thevince]

Right for the 2 code lines.

So much code for so a basic functionnality. Put 3 vba codes in 3 different places... only for one combobox with one field. Seems a bit complicated to implement in my project. I thought using this for more than one combobox.

Isn't there an easier way?

Thanks

 

[vbaInet]

How many records have you got?

 

[Thevince]

Hi vbainet

Thanks for fast reply

I've more than 2000 records but that's not the question. I want to implement that sort of search with more comboboxes. For each combobox so much coding ... There must be another way i think.

 

[vbaInet]

That's part of the question. Here's an excerpt from the link pbaldy sent:
"Combos become unworkable with many thousands of records, even many hundreds in Access 2. By loading records into the combo only after the user has typed the first three or four characters, you can use combos far beyond their normal limits, even with the AutoExpand property on."

That website is a trusted resource.

 

[Thevince]

I believe all what you say and the website is realy interesting.

I wonder what you say because the rowsource is in fact based on a filter updated every time the control changes. I've just add a top 5 to the query to just select the five first records and the behaviour is just the same.

Bouhouhou ... !

 

[vbaInet]

Can you upload a test db? Just the form in question, some random records (nothing confidential) and the code. I want to see if it's a problem with your end.

 

[vbaInet]

By the way, is your data source a linked table?

 

[Thevince]

Ok here is the whole DB zipped, it's a test db with nothing confidential.

The form is Frm_recherche_produit and the control label is Liste déroulante.

www;clicinfo;be/access/db;zip

Thanks for all the efforts you do ...

 

[vbaInet]

I thought you said it's a combo box vince? I see a list box. Am I missing something?

 

[Thevince]

Then you miss it, the last control on this form has the name modifiable12 an its label is "Liste déroulante", the listbox (secondcontrol) on the same form has as name "liste_produits" and as label Code_produit.

So the combobox is "Modifiable12".

 

[pbaldy]

I'm curious why you're doing this at all. Is there a performance problem? Does the auto complete property not do what you need?