Requery a comboBox

[Trodelphin]

just wondering if its possible to requery a Combo box with text allready partially entered
(ie a Name Combobox from table with hundreds of names the drop down list changing with every text character entered)
way i figure should be possible with a requery on update for field with a criterea of [Name]=[Name] but with a partial selection like a column selection works

 

[Galaxiom]

You seem to be describing the normal AutoExpand functionality of the combobox.

 

[missinglinq]

And since the AutoExpand Property is set to Yes, by default, you have to do absolutely nothing except create the Combobox!

Linq ;0)>

 

[Trodelphin]

not quite the problem ...yes it does go to closest matching but if you then select the dropdown it still contains ALL the data still
ie a table with 3 records
Daniel
David
Peter

if i type Dan it will jump to Daniel but if i select the dropdown it will still show all 3
i want it to requery on update with Criterea of matching characters entered
ie Da comes back with
Daniel
David

 

[Trodelphin]

i think im halfway there with a "Like" Criterea
Combobox label is NameLookup with Row Source is a query where TableName.CustomerName has the Criterea Like ("[NameLookup]*") but i cant seem to get this to refresh

ideas ?

 

[Trodelphin]

here is a sample one

 

[vbaInet]

You're going to potentially slow your database right down if you create this kind of search feature. It's only worthwhile if you have very few records to filter.

Think about what happens in a multi-user environment.

 

[CBrighton]

I only have 2003 on this PC so can't open accdb files, but you should be able to do what you want in the keypress event of the combobox, the code being

ComboboxName.requery

However, I don't recall if it resets the focus in the control (i.e. moves cursor to the beginning / selects whole contents of control) so you may need to find a way to move the cursor back to the end via VBA if you want the combobox to requery each time a character is input.

:edit:

I agree with above though, I'm not sure it's a good idea.

 

[vbaInet]

However, I don't recall if it resets the focus in the control (i.e. moves cursor to the beginning / selects whole contents of control) so you may need to find a way to move the cursor back to the end via VBA if you want the combobox to requery each time a character is input.

Yea, it resets the combo box and jumps to the first record in the row source.

 

[Trodelphin]

03 Version

while it will grow to be a largish database it would never usually be used by more than one user at a time
but so much more usefull to have a dropdown filter rather than a cold search form with various spellings/mistakes of names/surnames in the database

 

[Galaxiom]

Until you leave the combobox the Value Property does not change. Use the Text Property to get the current content.

However the query is not aware of anything but the Value property so the new RowSource must be constructed with concatenation in VBA.

Private Sub NameLookUp_OnChange()
   With Me.NameLookUp
      .RowSource = "SELECT blah Like '" & .Text & "*'"
      .Requery
   End With
End Sub

However I suspect the requery might loose the entered characters. If so you will need to use a variable to store the current Text and then restore it after the requery.

 

[Galaxiom]

I also agree with the other guys.

If you really want to persist it would be better done with an ADO Recordset and applying a Filter. This is all done locally in RAM without hitting the backend every time you type a character.

You are still going to get problems with spelling mistakes anyway. You could consider a Soundex match I guess.

 

[Trodelphin]

cheers for your input
i've allways adhered to the addage that trying to do something and discovering its not possible is better than being told you cant and giving up

 

[Galaxiom]

Many an advance has been made by those who didn't realise what they were trying to achieve was "impossible".