Way to FindFirst / FindNext "search as you type"

[mdlueck]

I am trying to mimic in Access 2007 a capability I had commonly coded in Paradox / Object PAL. For forms with many fields and records, I would code up a "search-as-you-type" capability. The search should be performed on the form's current sort by column.

I never see the FindFirst actually find based on searching for a partial string. My code is as follows:

Private Sub btnFind_Click()

  Dim strFindInCol
  Dim daoRS As DAO.Recordset

  'Find out which column is currently being sorted by
  If InStr(strCurrentSort, " ") = 0 Then
    strFindInCol = strCurrentSort
  Else
    strFindInCol = Mid(strCurrentSort, 1, InStr(strCurrentSort, " ") - 1)
  End If

  'Attach to the Form's record set
  Set daoRS = Me.RecordsetClone
  daoRS.FindFirst ("[" & strFindInCol & "] = " & Chr(34) & Me.fldFind.Value & "*" & Chr(34))
  'If we could find a match, jump to it
  If Not daoRS.NoMatch Then
    Me.Bookmark = daoRS.Bookmark
  End If

End Sub

I have four records in the test database table. "First Part", "Second Part", "Third Part", and "Fourth Part". I have it sorted by the title column, so "Second Part" is third in the list. Starting from the top record, I enter "Se" into the search fldFind field, click the find button, and I do not receive a hit.

In the final state, I indent to fire this code on the AfterUpdate event of the field control, and convert the button to an enable/disable toggle for the search capability.

Suggestion on how to implement "search-as-you-type" in Access VBA? TIA!

 

[gemma-the-husky]

I haven't tried it, but i suspect you would need to type into a text box, and use the "change" event as you typed/accepted each character.

 

[mdlueck]

I haven't tried it, but i suspect you would need to type into a text box, and use the "change" event as you typed/accepted each character.

Yes that is the desired end-state. As I am prototyping code I have the code tied to a button click event.

I do not suspect that is why I never get a hit for my FindFirst call, however. I suspect something else is wrong.

I have only used FindFirst to look up the ID of a specific record to jump to. So this is the first time I am attempting a partial match of a string value.

 

[gemma-the-husky]

i suppose i would think more of having a query, with the criterion field

"like " & searchtext & "*"

then as you enter text into an unbound search box, you could just do me.requery each time.

 

[vbaInet]

LIKE operator mdlueck.

 

[mdlueck]

"like " & searchtext & "*"

That was it, thanks!

 

[mdlueck]

One other aspect of this "search as you type" seems to be giving troubles...

The firing of the Change event... the text field control seems empty though I can see the character in the control at the time VBA is telling me that it is empty. Code as follows:

Private Sub fldFind_Change()

  DoEvents
  Call Find

End Sub

Do I need to call something other than DoEvents in this case in order to have the character added to the field control before the event continues?

A watch on Me.fldFind shows a Null value even though the control has one or more letters in it.

If I push a button which also fires the Find event, then the Find event sees character(s) in the control.

Same control, same shared event code, just two ways of firing said shared event. Thus I think that the Change event itself is firing before the character actually gets populated to the control. DoEvents does not seem to be enough to allow the character to be added to the control.

Aaahh, and if I start with a couple of characters already in the control, press backspace, then the Change event fires, and the field control appears to have the original collection of letters in it, not "one less" as the result of backspace.

 

[vbaInet]

I'm sure you know this but must have forgotten. Does the Text property of the control ring any bells?

 

[mdlueck]

Does the Text property of the control ring any bells?

rrrrrrrrrrr????????? I have always coded fldControlName.Value, not Text.

And I have a watch simply on Me.fldFind (in this case) and when I evoke the Change method it shows Null, if I push the button it shows the expected value.

Starting with several characters in the control, when I push Backspace it THEN shows the string that was in the control BEFORE I pushed Backspace... so .Value does work, just that on the Change event even DoEvents does not add the Character to the control prior to continuing.

Better understandability?

 

[vbaInet]

What I'm trying to tell you is in the Change event you use the Text property, not the Value property.

... and get rid of DoEvents.

 

[mdlueck]

That is VERY bazaar... you are indeed correct.

Now to have fldFind not to "Select All"... I will look into sending the End key to that control after calling the Find event in the Change event. As-is it sometimes selects all and I end up nuking the previously entered letters.

Thanks vbaInet!

 

[vbaInet]

No problemo!

With regards your highlighting problem, are you setting focus back and forth to the control within your Call Find code?

Oh, by the way, Find sounds like an Access/VBA Reserved Keyword.

 

[mdlueck]

With regards your highlighting problem, are you setting focus back and forth to the control within your Call Find code?

It appears when the FirstFirst event is successful, that causes the fldFind to SelectAll. I am not doing any focus calls in the code, so this is default (annoying) behavior.

Yes, same as if I tabbed off / back to the control.

Oh, by the way, Find sounds like an Access/VBA Reserved Keyword.

ack, thinking to get around to changing that.


Anyway, I found how to send the End keystroke... yet one more problem. That successfully unselects All the fldFind control, just it does not have focus anylonger either, so it is STILL not ready to accept the next key. I have tried sending it the SetFocus event before / after / both around the SendKeys, that still does not completely solve the trouble. So leaving just the SendKeys for now, which undoes the select all.

  'Attach to the Form's record set
  Set daoRS = Me.RecordsetClone
  daoRS.FindFirst ("[" & strFindInCol & "] LIKE " & Chr(34) & Me.fldFind.Text & "*" & Chr(34))
  'If we could find no match
  If daoRS.NoMatch Then
    Me.fldFind.BackColor = vbRed
    Debug.Print "NoMatch"
  'Else jump to it
  Else
    Me.fldFind.BackColor = vbWhite
    Me.Bookmark = daoRS.Bookmark
    'Send the End key to the fldFind control to have it undo the Select-All a successful find does
    'Me.fldFind.SetFocus
    SendKeys "{END}"
    'Me.fldFind.SetFocus
    Debug.Print "Match"
  End If

 

[vbaInet]

Ah, yes. So it does, because it "jumps" to that record. I'm hoping that the control is not bound and that it is placed in the Header section of the form? If both criteria are satisfied you shouldn't be having this problem.

SendKeys

In your words, "eeeeeeewwwww"

You can use SelStart of the control with the Len() function of the Text. Remember not to Len() on Null.

 

[mdlueck]

Ah, yes. So it does, because it "jumps" to that record. I'm hoping that the control is not bound and that it is placed in the Header section of the form? If both criteria are satisfied you shouldn't be having this problem.

The form I am adding this capability to is pictured here:
http://www.access-programmers.co.uk/forums/showpost.php?p=1110945&postcount=33

I am adding it to the footer between the Refresh / Requery buttons and the Close button.

The header controls are populated via DB class objects and are not bound to tables.

The content records are bound to a QueryDef object.

SendKeys

In your words, "eeeeeeewwwww"

Oh you know it, vbaInet!!! LOL...

(It will be good to be 100% free from that type of nonsense programming again!)

You can use SelStart of the control with the Len() function of the Text. Remember not to Len() on Null.

So do the SetFocus call (since the control does not have focus), then if not empty use SelStart to jump to the end of the Text?

 

[mdlueck]

So do the SetFocus call (since the control does not have focus), then if not empty use SelStart to jump to the end of the Text?

Coded as follows and NOW working BEAUTIFULLY!!! Thanks vbaInet!

  'Attach to the Form's record set
  Set daoRS = Me.RecordsetClone
  daoRS.FindFirst ("[" & strFindInCol & "] LIKE " & Chr(34) & "*" & Me.fldFind.Text & "*" & Chr(34)
  'If we could find no match
  If daoRS.NoMatch Then
    Me.fldFind.BackColor = vbRed
    Debug.Print "NoMatch"
  'Else jump to it
  Else
    Me.Bookmark = daoRS.Bookmark
    With Me.fldFind
      .BackColor = vbWhite
      .SetFocus
      .SelStart = Len(Me.fldFind.Text)
    End With
    Debug.Print "Match"
  End If

Note, higher in the code there is a check if the field is blank, so in this area I am guaranteed not to be dealing with a blank field, thus the lack of a blank check.

Updated: To search anywhere within the string data, not just starting from the beginning.

 

[mdlueck]

One more sharp spot to report back on:

When I push the button to fire the event, the event starting from a button CAN NOT access the .Text property of a field control.

I ended up moving the blank check to the Change / Click events grabbing the string out of the correct property of the field control for which event it was, then passing the string along to the shared FindRecord event.

At one spot the shared FindReocrd event needed to blank out the control. I coded that to the Value property and it works no matter which event calls FindRecord.

Access has me doing quite a dance today...

 

[vbaInet]

If you're going to use a button to fire the event then you would have to use the Value property. The only time the Text property is accessible is if the control has focus and the Value property of a control is set when the control (i.e. textbox) loses focus.

 

[mdlueck]

If you're going to use a button to fire the event...

The way I arrived at the UI working is that it searches as you type, or if you push the button then it calls FindNext so you may push the button to step through all of the finds. When no more finds are found, the search field background turns red.

Ready for end-user torture testing!

 

[vbaInet]

Ready for end-user torture testing!

Or user appreciation testing