Select as you type ListBox (1 Viewer)

nhorton79

Registered User.
Local time
Tomorrow, 02:41
Joined
Aug 17, 2015
Messages
147
Hi All,

I have a listbox (lstJobs) on a form (frmJobStatusList) which contains a large list of jobs.

Users can double click on the listbox to open a job form which contains details about the job.

Currently when the listbox is selected people can type a character which takes them to the first job that starts with that character (normal listbox behaviour).

What my users would like is that if they type further characters, it selects jobs that contain those characters.

I know I could limit/filter the list to show only jobs that contain those characters, but they would still like to see all jobs.

I.e. if they currently type “Burger” it would select the first job starting with Burger instead of selecting the first job that starts with B, then the first job starting with U and so on until it finally selects the first job starting with R.

Has anyone implemented this with a listbox?


Sent from my iPhone using Tapatalk
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
Do you mean combobox, since no way to type in listbox unless you add a textbox? But I do not understand the functionality. You type b it moves to first b word. But when you type bu it moves to first u word not first bu
word?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
Yes you can move without filtering, but that would be easier to do with a listbox vs combo. So you need to be specific on how you want it to work.
 

nhorton79

Registered User.
Local time
Tomorrow, 02:41
Joined
Aug 17, 2015
Messages
147
Hi All,

Thanks for your quick responses.

I've attached a screenshot showing what the form looks like.

There is a large listbox showing all projects/jobs within a certain status.

Users usually click anywhere on the listbox and start typing. At the moment they can only get to the first letter of the client name (which is a start I suppose and to be honest we've been running it this way for the past 3 or so years without too much hassle).

We can often have big sections of clients with the same first letter and some of the users have asked whether they may be able to continue to type to get the client further down the list.

See the attached screenshot, if they typed C, H, R, I, S it would take them to 'Christchurch Engine Centre'.

It's a small thing but something that has been a niggle for while.

DBGuy, I see that link to MajP's post for a class module for FindAsYouTypeCombo. MajP, as you supplied this, do you believe that your code could be altered to allow this functionality?
 

Attachments

  • screenshot.jpg
    screenshot.jpg
    56.7 KB · Views: 110

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:41
Joined
Feb 19, 2002
Messages
42,970
The selection expansion for combos and listboxes ONLY works as you type from left to right. The select as you type code (if it is the example I'm thinking of) uses *value* so it also filters to select embedded strings, not just leading characters.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
I can do this with using an additional textbox for searching see demo. Works well; however, I cannot find a way to override the default behavior so I cannot find a way to type directly in the list. In the combobox you can turn off the autoexpand basically overriding the default. Maybe someone else can figure out a way.

I slightly modified the original demo. If they type in the listbox it adds that text to the textbox. This would avoid some muscle memory of typing in the listbox with the bonus of being able to clear the textbox.
 

Attachments

  • MAJP SelectAsYouType ListBox V2.accdb
    988 KB · Views: 110
Last edited:

Micron

AWF VIP
Local time
Today, 09:41
Joined
Oct 20, 2018
Messages
3,476
My guess would be one of 2 ways:
- populate a hidden text box via Key Up event on the listbox and filter the list by modifying the listbox row source in that event, using the textbox as the criteria. However, I suppose one would need a control for clearing the filter.
- use the visible textbox as per MajP sample but use its contents to filter the listbox row source. The advantage of a visible textbox might be that if user deletes all the characters, no other control would be required to remove any filtering. However, the former might be closer to replicating the way it normally works when you type and a listbox has the focus.

I know of no way to turn off the normal filtering behaviour of a listbox either.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
This incorporates the best of both worlds. You can type in the textbox or type directly in the listbox.
 

Attachments

  • MAJP SelectAsYouType ListBox V3.accdb
    964 KB · Views: 124

nhorton79

Registered User.
Local time
Tomorrow, 02:41
Joined
Aug 17, 2015
Messages
147
Hi All,

Thanks for your input.

I’ll try and have a bit of a play, on a copy of the form, using combos, text boxes and MajP’s suggestion and see how I go.

Will report back.


Sent from my iPhone using Tapatalk
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
I am having difficulty with posting, but I have an update and will post later.
1)It handles a backspace while typing in the list, spaces, single quotes.
2)If you type too far "Burgern" and only have "burger" it will say not found and automatically move back to "Burger"
3) Handles up and down arrows. Presently they are disabled.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,169
another variation of type as you search.
 

Attachments

  • searchListbox.zip
    1.5 MB · Views: 132

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
Here is a final version using a class module. This will turn any listbox + textbox into a Select while you type listbox with one line of code. Works well for typing directly in the listbox which is much more challenging then using the textbox. You can do all your searching from the list or from the textbox. The only code needed is to initialize a SearchWhileYouTypeListBox

Code:
Private SAYT_ListBox As SelectAsYouTypeListBox
Private Sub Form_Load()
  Set SAYT_ListBox = New SelectAsYouTypeListBox
  SAYT_ListBox.Initialize Me.lstSearch, Me.txtSearch, "ProductName"
End Sub

Also this demonstrates how to change the search column.
 

Attachments

  • MAJP SelectAsYouType ListBox V5.accdb
    940 KB · Views: 130

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:41
Joined
May 7, 2009
Messages
19,169
yet another variation without the image button.
 

Attachments

  • listbox_search2.zip
    1.5 MB · Views: 147

isladogs

MVP / VIP
Local time
Today, 13:41
Joined
Jan 14, 2017
Messages
18,186
Just tried both arnel's & MajP's demos, both of which work nicely. Thanks

MajP
For info, clicking the labels to sort the list throws an error - it can't find the sortlist function
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
MajP
For info, clicking the labels to sort the list throws an error - it can't find the sortlist function

I did not demo that in this form. The other FAYT listbox that demos the feature is in that db. The FAYT class has a sort feature.
 

nhorton79

Registered User.
Local time
Tomorrow, 02:41
Joined
Aug 17, 2015
Messages
147
Hi MajP,

I'm trying your examples and they seem to be what I am looking for (when I look at your supplied example).

However, when I try to implement in my database they don't work as expected.

When I attempt to use your class version I get Runtime Error 3061 Too few parameters. Expected 1.

The initialise line I've used is:

Code:
SAYT_ListBox.Initialize Me.JobList, Me.txtSearch, "[Client Name]"

And my recordsource is:
Code:
SELECT IIf([tblAccType].[AccTypeID]=1,[tblClient].[ClientName] & " - CASH",[tblClient].[ClientName]) AS [Client Name], tblStatus.StatusType AS Status, tblJob.JobID AS [Job ID], tblJob.JobTitle AS [Job Title], tblJob.JobStatus_FK, Nz([JobDateExpected],DateValue(Now())) AS [Due Date]
FROM tblAccType INNER JOIN (tblContact RIGHT JOIN ((tblStatusCategory INNER JOIN tblStatus ON tblStatusCategory.StatusCategoryID = tblStatus.StatusCategory_FK) INNER JOIN (tblClient INNER JOIN tblJob ON tblClient.ClientID = tblJob.JobClient_FK) ON tblStatus.StatusID = tblJob.JobStatus_FK) ON tblContact.ContactID = tblJob.JobContact_FK) ON tblAccType.AccTypeID = tblClient.ClientAccType_FK
WHERE (((tblStatusCategory.StatusCategoryID)=[JobFrame]))
ORDER BY IIf([tblAccType].[AccTypeID]=1,[tblClient].[ClientName] & " - CASH",[tblClient].[ClientName]), tblJob.JobID;

When I try to use your non class version, it always selects the item before the expected item. Think this is due to having Column Heads set to Yes, and when I set it to No, it works as expected. But can't find where I would alter your code to make it step along an extra line to account for the Column Heads.:banghead:

Any thoughts?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:41
Joined
May 21, 2018
Messages
8,463
Where the code says
Code:
  lstSearch.Selected(rsSearch.absolutePostion) = True

You can do something like this

Code:
       selectedItem = rsSearch.AbsolutePosition
       If Me.lstSearch.ColumnHeads = True Then selectedItem = selectedItem + 1
       lstSearch.Selected(selectedItem) = True

or simply hard code
Code:
  lstSearch.Selected(rsSearch.absolutePostion + 1) = True

I would try to use the class because it has more features. I did not update the no class anymore. I would save the query as a Named query. Then use that query as the rowsource. That may help avoid the error.

I purposely made this more complicated so that I could search on any column not only the bound column. I would say for me 90% of the time you are searching on a visible column and the bound column is a hidden PK. You can adapt the general concepts from Arnelgp simple solution for the bound visible column and the keyup from the class. Then you can do away with the recordsets and absolute position and go directly to the value property. That is a limited solution but much easier. I normally build class modules that are universal, so I can build it once and use it everywhere.

If you post a strip down version, I will take a look. I am interested why the class fails, but believe I know where.
 

Users who are viewing this thread

Top Bottom