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

Search as you type in split form

P: 5
Hey I am new to this forum. I want to set up combo box on a split form to filter the record in the Datasheet. I want to filter either by selecting the value or type which will filter the record as you type (partial match). Can someone tell me how I can achieve this.? I donít know abcd about vba programming. Thanks in advance .
Feb 9 '19 #1
Share this Question
Share on Google+
7 Replies


PhilOfWalton
Expert 100+
P: 1,430
Welcome to Bytes. Let's see if we can help you.

Firstly, the Combo box is a search as you type control, but remember it is finding letters from the left, not letters in the middle of a word.
Secondly the text in the Combo box must already exist.

My approach is somewhat different, in that it gets a partial match anywhere in a field.

This is an image of a form to search in the titles of pieces of music where I am trying to find the word "Spring". Personally I never use data sheets and use continuous forms instead.

Initially there are over 1300 disks when no filtering is applied. If I type "s" into the search box, the filter reduces this to about 900 disks ("s" is a very frequently used letter, so many of the titles will contain the letter "s".

The first image shows what happens after tying "sp" into the search box.



Now down to 30 disks

The second image is after adding an "r" to the search box ("spr")



As you see, we still haven't reduced the search to only "sping" as there are a couple of German tunes that have "spr" in their titles.

If this is the sort of thing you are after, please come back

Phil
Attached Images
File Type: jpg Search SP.jpg (322.0 KB, 141 views)
File Type: jpg Search SPR.jpg (151.2 KB, 143 views)
Feb 9 '19 #2

P: 5
Thanks for caring to help me. Yes that is what I essentially want to achieve but on a split form using combo box. But itís is fine if the same functionality can be achieved on a continuous form using search text box. Currently I use text keyword search on a continuous form by using Update Event code or a macro in which case I have hit search button or enter key. I want to filter the form as I type any letter in the search box (as you have shown)without having to hit the enter key.
Feb 9 '19 #3

PhilOfWalton
Expert 100+
P: 1,430
So use a standard query as the RecordSource of the form with no criteria in the query.

Basically on my form, I can limit the Media to Disc, Vinyl, Tape or all media types. That is why, on the code below, the variable "Fltr" may have a value in it before we start entering letters into the Text Box (TxtFilter).

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub FilterForm()
  5.  
  6. 'MediaTypeID = 1 AND (Title Like '*roy*' OR Title Like '*kin*')
  7.  
  8.     Dim Fltr As String
  9.  
  10.     If SelectMediaType > 0 Then           ' Media Type not all
  11.         Fltr = "MediaTypeID = " & SelectMediaType
  12.     End If
  13.  
  14.     ' If we return no records, temporarily remove the filter
  15.     If Me.RecordsetClone.RecordCount = 0 Then
  16.         Me.FilterOn = False
  17.     End If
  18.  
  19.     TxtFilter.SetFocus
  20.  
  21.     If Left(TxtFilter.Text, 1) = "~" Then  ' Don't allow 
  22.                                       ' a tilde as first letter
  23.         TxtFilter.Text = ""
  24.     End If
  25.  
  26.     If Fltr > "" Then                               ' Media Type selected
  27.         Fltr = Fltr & " AND "
  28.     End If
  29.     Fltr = Fltr & "(Title Like '*" & Replace(Me.TxtFilter.Text, "'", "''") & "*'"
  30.     Fltr = Replace(Fltr, "~", "*' OR Title Like '*")
  31.     Fltr = Fltr & ")"
  32.     Me.Form.Filter = Fltr
  33.     Me.FilterOn = True
  34.  
  35.  
  36.     Me.TxtFilter.SetFocus
  37.  
  38.     ' Move the cursor to the end of the input text box.
  39.     If Me.RecordsetClone.RecordCount = 0 Then
  40.         Exit Sub
  41.     End If
  42.  
  43.     Me.TxtFilter.SelStart = Len(Me.TxtFilter.Text)
  44.  
  45. End Sub
  46.  
  47. Private Sub SelectMediaType_AfterUpdate()
  48.  
  49.     FilterForm
  50.  
  51. End Sub
  52.  
  53. Private Sub TxtFilter_Change()
  54.  
  55.     FilterForm
  56.  
  57. End Sub
This sub routine is called when the TxtFilter is changed or on the AfterUpdate of the Media Type.

If you type a tilde (~) in the search box, you can search for more than 1 word, so typing "spr~fest" in the serch box will bring up the music with both "Spring" and "Festival" on the continuous form.

Phil
Feb 9 '19 #4

P: 5
How about if I use only text search box without the combo box e.g media type in your case. Sorry I canít understand code much but it be helpful if you tell me which part of the code should I attach to Chang event. I want to use table tblPerson as the record source. It has field Name, CID No, and so on which I want to filter.
Feb 9 '19 #5

PhilOfWalton
Expert 100+
P: 1,430
Yes the Combo box is not necessary.

So I gather you are looking to filter a form for a particular person. That should be no problem.

Can you post an image of your relationship page, making sure that all the field names are visible, and I will have another look

Phil
Feb 9 '19 #6

P: 5
Thnaks for trying to help me.I just figured out the code to filter using the combo box in a split form. Itís working out the way I wanted. I will come back with another problem next time. Thanks Phil once again.
Feb 9 '19 #7

PhilOfWalton
Expert 100+
P: 1,430
Good, glad you've dot it sorted

Phil
Feb 9 '19 #8

Post your reply

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