Filtering A ComboBox

[randommetalguy]

Hey guys I did a search on the boards but the topics I found I think are slightly different that what I am trying to do.

In order to use my application, you must first select your name from the database. So when my form starts up I have this line of code execute:

Me.cboUsername.RowSource = "SELECT [tblUsernames].FullName FROM tblUsernames ORDER BY [tblUsernames].FullName;"

The full usernames are displaying in the dropdown.

Now as you type, I want the names in the drop down box to filter so I've added this code:

Private Sub cboUsername_Change()
Me.cboUsername.RowSource = "SELECT [tblUsernames].FullName FROM tblUsernames GROUP BY [tblUsernames].FullName HAVING [tblUsernames].FullName LIKE '" & Me.cboUsername.value & "' ORDER BY [tblUsernames].FullName;"
Me.cboUsername.Dropdown
End Sub

The problem I am running into is when I clear the form, the last person found's name is the only one showing in the drop down.

If you press A I want only usernames beginning with the letter A to show up. If you press AR I want it to display only the AR users. If you press backspace I want it to display only the A users again.

Can anyone help me with this?

Thanks,
Glen

 

[MarkK]

- Not sure if this is the issue, but during the Change event handler the character you just typed is not yet present in the .Value property of the control. That doesn't happen until after you leave the control.
- Try using the .Text property of the control, which is only available when the control has the focus, and does expose each character as you type 'em.
- Also, don't you need wild cards with the LIKE operator?

...LIKE '" & Me.cboUsername.Text & "*' ORDER BY...

Cheers,

 

[randommetalguy]

Wow, that simple and I've been racking my brain with the

docmd.applyfilter command for the past 45 minutes.

One last question. Let's say my dropdown list produces 5 names. After I click on the name I want it puts in in the box but the combobox stays in the dropdown position. Any way to "undropdown" after an onclick event?

Glen