List box filter on form

[Oreynolds]

Hi, I have a form which shows a list of records. To help the user find the record they want I have a text box txtSiteName which the user types into. On the OnChange event of this there is the following code:

Private Sub txtSiteName_Change()

    Me.txtSiteNameX = Me.txtSiteName.Text
    Me.lstJobs.Requery

End Sub

The hidden text box txtSiteNameX is referenced to as the criteria in the underlying query to the list box as follows:

Like "*" & [Forms]![frmFindQuoteRecord]![txtSiteNameX] & "*"

The form and filtering of the records in the list box work fine. However when there are 10+ users logged into the DB it slows down a fair bit meaning there is about a 1 second pause on every character entered, therefore if they need to enter 5/6 characters to filter down enough it can become a bit clunky.

What I was hoping to do was remove the change the requery command the txtSiteName_Change() event and then have it only requery the list box when the user presses 'Enter' having typed the full search string they wanted. I changed the enter key behaviour on the text box to 'New line in field' and added the following code to the the OnKeyPRess event:

Private Sub txtSiteName_KeyPress(KeyAscii As Integer)

    If KeyAscii = 13 Then 'Enter key
        Me.lstJobs.Requery
    End If

End Sub

Although in principle this works the problem I now have is that by pressing the enter key it does add a new line in what is only a single line text field and therefore the test string the user typed is now out of sight and it looks like the text box is blank. Furthermore if they want to then edit/amend there type string its not easy/obvious as there is nothing showing. I tried to find a way of removing the carriage return from the text box but none of the web solutions seemed to work. I then found by adding the following line of code I could at least remove the second line from the field but it then leaves the typed string highlighted in the field. This isn't ideal as if the user types anything it of course over types the entire string.

Private Sub txtSiteName_KeyPress(KeyAscii As Integer)

    If KeyAscii = 13 Then 'Enter key
        Me.lstJobs.Requery
        Me.txtSiteName = Me.txtSiteNameX
    End If

End Sub

So what I need to do is unselect the text in the box and leave the cursor after the last typed character. I have tried all sorts of web suggestions as below but none seem to work, many of which again leave the cursor on a new line in the field which hides the typed text:

       Me.txtSiteName.SelStart = Me.txtSiteName.SelLength
       Me.txtSiteName = Left(Me.txtSiteName, Len(Me.txtSiteName) - 1)
       Me.txtSiteName = Replace(Nz(Me.txtSiteNameX, ""), Chr(13), "")
       Me.txtSiteName.SelLength = 0
       Me.txtSiteName.SelStart = 50

Any ideas?

 

[Oreynolds]

It appears from all my web searching that this is a relatively common problem (trying to use the enter key to trigger an event without effecting cursor position) and I have now solved it as follows:

Private Sub txtSiteName_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
KeyCode = 0
Me.lstJobs.Requery
End If
End Sub

This captures the enter key being pressed, prevents it from creating a new line in the field or anything else but enables you to trigger another line of code, in this case a list box requery

 

[cheekybuddha]

Another way to do this is to 'debounce' the text being entered in the search box.

You will need to make some adjustments.

First, remove the criteria in your listbox's RowSource query, you will construct it in code later.

Then, add a module level string variable to hold the criteria

Add a timer event to your form which will perform the filtering.

Change your textbox Change event code to set the criteria variable and call the timer.

No need for a hidden textbox, nor events capturing keypresses.

Private m_SiteNameCriteria As String

Private Sub Form_Timer()

  Dim strSQL As String

  strSQL = "SELECT * FROM YourQuery WHERE SiteName LIKE '*" & m_SiteNameCriteria & "*'"
  Me.YourListbox.RowSource = strSQL
  Me.TimerInterval = 0

End Sub

Private Sub txtSiteName_Change()

  m_SiteNameCriteria = Me.txtSiteName.Text
  Me.TimerInterval = 300     ' 300 milliseconds, adjust as required

End Sub

As the user types in txtSiteName the criteria variable is populated and the timer is reset not to fire for another 300 milliseconds.

Once the user stops typing for 300 milliseconds the timer event will fire, set the listbox with revised filtered RowSource, and turn the timer off from firing again. It will only be turned back on on subsequent change in txtSiteName. The search will only happen every few characters typed, rather than for every single character.

If you can get away with only searching 'Starts with' as opposed to 'Contains' then you can speed the query up a lot - remove the first asterisk from the LIKE clause (and make sure SiteName field is indexed in your table. Wildcard search from the beginning of a table field can utilise an index on the field (much faster), whereas searching 'within' a string can not use the index.

 

[Pat Hartman]

You have been given a suggestion on how to speed this up but if you really don't need a wild card search, you should eliminate the code entirely and just use the sombo sorted correctly so that it will filter automatically.

 

[Oreynolds]

Another way to do this is to 'debounce' the text being entered in the search box.

You will need to make some adjustments.

First, remove the criteria in your listbox's RowSource query, you will construct it in code later.

Then, add a module level string variable to hold the criteria

Add a timer event to your form which will perform the filtering.

Change your textbox Change event code to set the criteria variable and call the timer.

No need for a hidden textbox, nor events capturing keypresses.

Private m_SiteNameCriteria As String

Private Sub Form_Timer()

  Dim strSQL As String

  strSQL = "SELECT * FROM YourQuery WHERE SiteName LIKE '*" & m_SiteNameCriteria & "*'"
  Me.YourListbox.RowSource = strSQL
  Me.TimerInterval = 0

End Sub

Private Sub txtSiteName_Change()

  m_SiteNameCriteria = Me.txtSiteName.Text
  Me.TimerInterval = 300     ' 300 milliseconds, adjust as required

End Sub

As the user types in txtSiteName the criteria variable is populated and the timer is reset not to fire for another 300 milliseconds.

Once the user stops typing for 300 milliseconds the timer event will fire, set the listbox with revised filtered RowSource, and turn the timer off from firing again. It will only be turned back on on subsequent change in txtSiteName. The search will only happen every few characters typed, rather than for every single character.

If you can get away with only searching 'Starts with' as opposed to 'Contains' then you can speed the query up a lot - remove the first asterisk from the LIKE clause (and make sure SiteName field is indexed in your table. Wildcard search from the beginning of a table field can utilise an index on the field (much faster), whereas searching 'within' a string can not use the index.

Thanks for this looks like an interesting solution which I will try

 

[Oreynolds]

You have been given a suggestion on how to speed this up but if you really don't need a wild card search, you should eliminate the code entirely and just use the sombo sorted correctly so that it will filter automatically.

Hi, unfortunately I think the wildcard is fairly essential in our application given the potential variations in search methods for certain records. Thanks

 

[Josef P.]

1) Why can't you use the AfterUpdate event of the textbox?

2)
Values for SiteName will probably not be unlimited. Have you considered using a combobox with the possible values instead of the textbox?
Then an index search would be possible, because it is possible to filter for a certain value.

 

[Oreynolds]

1) Why can't you use the AfterUpdate event of the textbox?

2)
Values for SiteName will probably not be unlimited. Have you considered using a combobox with the possible values instead of the textbox?
Then an index search would be possible, because it is possible to filter for a certain value.

I have experimented with all sorts but text boxes filtering the results of the list box is definitely the best solution for our application. The screenshot below is an example of one of the search forms we use. It is very powerful and allows the user to very quickly narrow down the records. In many cases users won't know 'exactly' what they are looking for so need to use key words to narrow the search results to enable them to find what they are looking for.

As I tried to explain in my first post currently the list box requery command was formerly on the KeyPress event but when multiple users are logged in this is very laggy given it is requerying every time they add a character which is typically 4-6 characters. By changing this as per my solution found above this now only requeries once the user presses enter. This is dramatically quicker and the users have reported it being much quicker and more efficient this morning having implemented it overnight

 

[Josef P.]

I just don't understand why KeyPress is used.
Especially with multiple filter controls I would use AfterUpdate.
Since users usually know what values they want to search for, they can enter the full value in my applications and confirm the entry with {Return} or {Tab}.

In the example of #8 I would use a combo box for the supplier. There will probably not be 100 000 entries. Then you could filter the list by its ID.
Basic rule: avoid table scans as much as possible.

 

[Oreynolds]

I just don't understand why KeyPress is used.
Especially with multiple filter controls I would use AfterUpdate.
Since users usually know what values they want to search for, they can enter the full value in my applications and confirm the entry with {Return} or {Tab}.

In the example of #8 I would use a combo box for the supplier. There will probably not be 100 000 entries. Then you could filter the list by its ID.
Basic rule: avoid table scans as much as possible.

Key press was used as it effectively gives you a dynamic 'search while you type' facility. It is brilliant when only a few users are logged in but as soon as the numbers increase it slows right down.

I don't want to use the AfterUpdate as this requires the user to tab away from the field. I want them to be able to type a string, review the results but then without any other action be able to add to that string or backspace and alter it to suit their search requirements, therefore I cannot leave the field or enter on it. Whilst a Combo is an option this also requires users to switch to use the mouse to select it etc where as tabbing and then typing into the text boxes is far quicker

 

[cheekybuddha]

Whilst a Combo is an option this also requires users to switch to use the mouse to select it etc where as tabbing and then typing into the text boxes is far quicker

This is not strictly the case.

You can tab into a combo. Start typing the search term and the list will automatically drop down and begin filtering according to what has been typed.

However, it may require education of your users that this behaviour is available.

 

[Josef P.]

It is brilliant when only a few users are logged in but as soon as the numbers increase it slows right down.

This should be independent of the number of users, since each user in an Access BE has its own DBEngine.
I haven't used Access-BE in a long time, but LDB locking will probably still play a role.

If a few million data per character input have to be searched, KeyPress can become annoying.
Relevant for the runtime is that in the SQL execution plan as good as possible matching indexes can be used.

 

[cheekybuddha]

since each user in an Access BE has its own DBEngine.

Yes, this is very important!

Does every user have their own copy of the front end linked to the backend (tables,queries etc) on a network share?

If not, and everyone is opening the same copy of your database at the same time then you will have lots of trouble. Your database must be split into frontend (interface, forms, reports etc) and linked to backend (data, tables,queries etc). Each user must have their own copy of the frontend

 

[Pat Hartman]

product category should be a small enough list to use a combo. Again, make sure the RowSource is sorted on the text value so that it searches correctly. If supplier is a small enough set, it also can be a combo. You can also not actually filter until the user leaves the field which limits the number of times you have to retrieve the data.

The problem with the wild card searches is that they force the database engine to examine every row in the database and prevent the use of indexes and any optimization methods. If you really have to use them, then you have to pay the price but if you can avoid them, your search will speed up relatively based on the number of rows in the table you are searching. The more rows, the more speed you gain.

 

[cheekybuddha]

The problem with the wild card searches is that they force the database engine to examine every row in the database and prevent the use of indexes and any optimization methods.

Only if wildcard comes at the beginning of the criteria.

Criteria like: fld LIKE 'abc*' can still use an index if available.

 

[Pat Hartman]

But he is using the wildcard at the beginning

 

[cheekybuddha]

Many people read these threads, Pat, not just the participants!

Just trying to help those who might get the wrong idea from reading your blanket statement

 

[Pat Hartman]

Very helpful