By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Filter number type drop down list

P: 67
Hi,

I have a combo box which is store bound column as number datatype. I want it filter list when user input in it. For example, my list is 111,312,313,456,567,875,997,998,1100,1170,11165,11 170. When user input like 111, I want to this drop down list do filter by showing only 111,11165,11170.

Can anybody guide me how to do this?
Mar 7 '14 #1

✓ answered by mshmyob

I threw this together.

The code only checks for the 1st character entered so you would have to fine tune it for more detailed matches.

Error handling was put in because of how Access handles entries where no value exists in the list.

You need a hidden field so you can move to it to requery your combobox.

If you want to get the entire list back again just delete all characters and the complete list will show again.

Enter the code in the OnChange event of the combo box.

Obviously change all control names to your setup.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Combo0_Change()
  3.     Dim strSQL As String
  4.     Dim varKey As Variant
  5.  
  6.     On Error GoTo ErrorHandler
  7.  
  8.     varKey = Left(Me.Combo0.Text, 1)
  9.     strSQL = "select SamplePK,SampleInteger FROM tblSample WHERE Cstr(tblSample.SampleInteger) LIKE '" & varKey & "*' ORDER BY tblSample.SampleInteger"
  10.     Me.txtHidden.SetFocus
  11.     Me.Combo0.RowSource = strSQL
  12.     Me.Combo0.Requery
  13.     Me.Combo0.SetFocus
  14.     Exit Sub
  15.  
  16. ErrorHandler:
  17.     ' Display error information.
  18.     Select Case Err.Number
  19.  
  20.     Case Is = 2110
  21.             Resume Next
  22.     Case Is = 2118
  23.             Resume Next
  24.     Case Else
  25.             MsgBox "Error number " & Err.Number & ": " & Err.Description
  26.             ' Resume with statement following occurrence of error.
  27.             Resume Next
  28.     End Select
  29. End Sub
  30.  
Maybe this can get you started and you can expand to improve the functionality.

cheers

Share this Question
Share on Google+
6 Replies


100+
P: 104
I think you need to convert these number into strings. See the CStr() function in the link below.

http://office.microsoft.com/en-us/ac...001229018.aspx

Someone please correct me if I'm wrong, but I don't think you can search for partial matches in a number field.
Mar 7 '14 #2

NeoPa
Expert Mod 15k+
P: 31,492
Good thinking. Filter on CStr() of value using Like.
Mar 8 '14 #3

mshmyob
Expert 100+
P: 903
I threw this together.

The code only checks for the 1st character entered so you would have to fine tune it for more detailed matches.

Error handling was put in because of how Access handles entries where no value exists in the list.

You need a hidden field so you can move to it to requery your combobox.

If you want to get the entire list back again just delete all characters and the complete list will show again.

Enter the code in the OnChange event of the combo box.

Obviously change all control names to your setup.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Combo0_Change()
  3.     Dim strSQL As String
  4.     Dim varKey As Variant
  5.  
  6.     On Error GoTo ErrorHandler
  7.  
  8.     varKey = Left(Me.Combo0.Text, 1)
  9.     strSQL = "select SamplePK,SampleInteger FROM tblSample WHERE Cstr(tblSample.SampleInteger) LIKE '" & varKey & "*' ORDER BY tblSample.SampleInteger"
  10.     Me.txtHidden.SetFocus
  11.     Me.Combo0.RowSource = strSQL
  12.     Me.Combo0.Requery
  13.     Me.Combo0.SetFocus
  14.     Exit Sub
  15.  
  16. ErrorHandler:
  17.     ' Display error information.
  18.     Select Case Err.Number
  19.  
  20.     Case Is = 2110
  21.             Resume Next
  22.     Case Is = 2118
  23.             Resume Next
  24.     Case Else
  25.             MsgBox "Error number " & Err.Number & ": " & Err.Description
  26.             ' Resume with statement following occurrence of error.
  27.             Resume Next
  28.     End Select
  29. End Sub
  30.  
Maybe this can get you started and you can expand to improve the functionality.

cheers
Mar 9 '14 #4

P: 67
Hi mshmyob,

Finally I got it work. Thanks you so much for help.

Best regards,
Sophanna
Mar 10 '14 #5

NeoPa
Expert Mod 15k+
P: 31,492
I can only assume the earlier posts weren't clearly understood. What we were (both) talking about is something of the form :
Expand|Select|Wrap|Line Numbers
  1. SELECT [NumericField]
  2. FROM   [YourTable]
  3. WHERE  (CStr([NumericField]) Like Forms!YourForm!YourControl & '*')
When the value in [YourControl] is updated by the operator then the ComboBox with that RowSource is requeried and is automatically updated to reflect the operator selection. I really don't see any need for code in such a situation, except maybe to call the requery after the control is updated by the operator.
Mar 10 '14 #6

P: 67
Hi,

*Edit*
Please see new thread Filter number on combo box dropdown list for a follow-on question.
May 24 '14 #7

Post your reply

Sign in to post your reply or Sign up for a free account.