choose item in a ComboBox as we Type the contained text...

[Anbusds]

Dear All,
I have 1 combobox containes 2 columns from 1 table now i want to select record from the list
Example: i want to select PM-1234-1111 so i dont want to type starting letter PM to select but i want to type 234 or 123 or somewhere in the middle or end to filter that contained text in all the items is it possible?
Thanks in advance...

Anbu

[jforbes]

Here is a link to a very similar question:

http://bytes.com/topic/access/answers/960776-combobox-stop-autofilling-text-portion-selected-item

I took the code from that Question and added an asterisk to the Where Clause and it seems to be doing what you want, or at least close:

Option Compare Database
 Option Explicit
 
 Private bLookupKeyPress As Boolean
 
 Private Sub cboLookup_Change()
     Dim sSQL As String
     Dim sNewLookup As String
 
     If Not bLookupKeyPress Then
         sNewLookup = Nz(Me.cboLookup.Text, "")
         sSQL = "SELECT Field2 FROM [tblBunchOfStuff] "
         If Len(sNewLookup) <> 0 Then
            [icode]sSQL = sSQL & " WHERE Field2 LIKE '*" & sNewLookup & "*'"[/icode]
         End If
         Me.cboLookup.RowSource = sSQL
         Me.cboLookup.Dropdown
     End If
     bLookupKeyPress = False
 End Sub
 
 Private Sub cboLookup_KeyDown(KeyCode As Integer, Shift As Integer)
     Select Case KeyCode
         Case vbKeyDown, vbKeyUp
             bLookupKeyPress = True
         Case Else
             bLookupKeyPress = False
     End Select
 End Sub
 
 Private Sub cboLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
     bLookupKeyPress = True
 End Sub

Be sure to set the AutoExpand Property to No.

[Anbusds]

Hi JForbes,
Thanks for your reply, i have tried a few times after i follow your code i am not able to select that column from my table...
so total 3 event procedure anything wrong with my code...

Code as follows,

Private Sub Combo83_Change()
Dim sNewLookup As String
If Not bLookupKeyPress Then
sNewLookup = Nz(Me.Combo83.Text, "")
sSQL = "SELECT Product from dbo.Customer_Item"
If Len(sNewLookup) <> 0 Then
sSQL = sSQL & " WHERE Product LIKE '*" & sNewLookup & "*'"
Debug.Print sSQL
End If
Me.Combo83.RowSource = sSQL
Me.Combo83.Dropdown
End If
bLookupKeyPress = False


End Sub

Private Sub Combo83_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case vbKeyDown, vbKeyUp
bLookupKeyPress = True
Case Else
bLookupKeyPress = False
End Select

End Sub

Private Sub Combo83_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
bLookupKeyPress = True
End Sub

Thanks in advance...

[jforbes]

I'm a little fuzzy about what error you are getting, if any. So I'm guessing the DropDownList for the ComboBox is not displaying for you.

There are two things that I noticed that you might want to check.
The first is to include these two lines at the top of your Form:

Option Explicit
Private bLookupKeyPress As Boolean

I'm pretty sure these are missing as you should be getting an error in Combo83_Change( ) since it is missing the following:

Dim sSQL As String

The other thing is that you mentioned you have two columns in your ComboBox but the example has only one. You may need to add the additional column into the Select String:

sSQL = "SELECT Product from dbo.Customer_Item"

and/or make sure the ComboBox Properties ColumnCount and BoundColumn line up with the RowSource that is being created.

[Anbusds]

Hi JForbes,
Thanks for your reply...
Still the same issue occurs so i used table/query in the properties to lookup the table, i am not going to use VBA to lookup the table, is it possible to use filter here any VBA Code?
Thanks in advance...

[jforbes]

What issue are you having? What is it doing? Is there an error message?

Filtering a ComboBox RowSource is not a function available in Access. That is why the code sets the RowSource, which is effectively the same.

[Anbusds]

Hi JForbes,
Its Working well now...
Thank you!!!