Select while typing in list box MS Access 2003 4

[puforee]

I have a list box. When I type a letter it goes to that letter in the list. If I type another letter it goes to that letter as the first character in the list box. Example.

1 Type the letter J and it shows Jim, John, James, Jonas etc.
2 Then type the additional letter O and it shows Oran, Orion, Orange etc.

I want to type J and show what is above in # 1 then I want to type O and show John and Jonas. Continue typing with an h and it should show John.

How do I set this up?

Thanks,

 

[missinglinq]

Don't remember being able to enter data in standard Listboxes! Are you, perhaps, talking about a dropdown, Combobox?

Linq ;0)>

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[Andrzejek]

I believe you can keep hitting J to loop thru Jim, John, James, Jonas, and Jim again in your list box.

Not really an answer to your question, sorry.

Have fun.

---- Andy

 

[missinglinq]

That's correct, you can navigate through a Listbox in that manner, but it doesn't have a true 'autoexpand' feature, like Comboboxes do, where you can keep modifying the entered string, from J to JA to JAM/b], etc., which seems to be what the OP is expecting.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[lameid]

As pointed out comboboxes work this way, not listboxes. There is nothing that says you can't bind two controls to the same data field. So the easiest solution maybe to simply add a combobox above or below your listbox to use data input... I would probably go above and size the columns to be about the same in both to try to give a sense of navigation.
Beyond that you can make it better with VBA/Code: I would also use the dropdown method of the combobox when I wanted it to drop down. You might want to set the focus to the combo and use drop down when a Key is pressed on the list box.

You might also consider this alternative code: making your combobox visible setting the focus to it and making the listbox not visible on the listbox got focus event.... Just be sure to reverse the visibility on the combo boxes lost focus.

And if the combobox and listbox together just doesn't look write, you could go through the trouble of using one of the on key events to navigate the listbox... Not sure how to make that work but it is where I'd play... About 10 years or so ago in Access 97 I had a combobox with 1 million parts in it and it took so incredibly long to populate the combo, I had to make it change the where clause of the row source and requery on one of the key events so people could see the parts (where column >= combo)... I don't remember the specifics or have the source but it is someplace to start tinkering.

 

[puforee]

Thank you all for your input. So here is what I need in total.

I need to be able to do multiple selects using the control key and/or the shift key. My understanding is this can only be done in a list box....CORRECT?

Also, I would like to perform the show as you type to narrow down the list. As in missinglinq's example above. Based on the response to my posting it looks like this can only be done in a COMBO Box....CORRECT?

I would not want to combined the two functionalities at the same time. It would be either or....so I am looking for a specific name...type letters to narrow the selection.

Or....in another instance of the same box...select multiple names.

Can these functions be combine in a sindle control...List Box or Combo Box?

Sorry for the delay in responding to my posting...I want to thank all of you.....so far.

 

[TheAceMan1]

Howdy puforee . . .

Here's an example of adding [blue]AutoExpand[/blue] to a listbox. For demo there's only two controls unbound textbox & listbox.

[ol][li][blue]MyFilter[/blue]: an unbound textbox which sits on top the listbox.[/li]
[li][blue]MyList[/blue]: the listbox, with fields [blue]ID[/blue] (autonumber) & [blue]spcFolder[/blue] (folder names).[/li][/ol]

The code runs in the [blue]OnChange[/blue] event of the unbound textbox and modifies the SQL of the [blue]rowsource[/blue]. For your needs, you just need to change the sql used.

[blue]   Dim SQL As String, Dat As String
   
   Dat = Me.MyFilter.Text
   SQL = "SELECT ID, spcFolder " & _
         "FROM tblSpecialFolders "

   If Nz(Dat, "") <> "" Then
      SQL = SQL & "WHERE [spcFolder] Like '" & Dat & "*' "
   End If
   
   SQL = SQL & "ORDER BY spcFolder;"
   
Me.MyList.RowSource = SQL[/blue]

[blue]Your Thoughts? . . .[/blue]

 

[lameid]

Aceman,

puforee per the last post want's a multi-select box, so the listbox has to be navigated somehow instead of filtered I believe or risk removing previously selected values (filtering was one of my initial thoughts from the orginal post)... Hopefully someone saves me the work of digging around for a solution later as I only have a vague sense it is possible but don't remember the specifics to make it so.

 

[TheAceMan1]

Howdy lameid . . .

In puforee's post origination he describes [blue]AutoExpand[/blue]

[blue]1 Type the letter J and it shows Jim, John, James, Jonas etc.
2 Then type the additional letter O and it shows Oran, Orion, Orange etc.

[purple]I want to type J and show what is above in # 1 then I want to type O and show John and Jonas. Continue typing with an h and it should show John.[/purple][/blue]

Then again today in 22 post Aug 13 13:08

[blue]I need to be able to do multiple selects ...
[purple]I would like to perform the show as you type to narrow down the list.[/purple]
... so I am looking for a specific name...[purple]type letters to narrow the selection.[/purple][/blue]

Still looks like [blue]autoexpand[/blue] to me. And, if he turns on [blue]multiselect[/blue] he can narrow down to an area and do just that!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lameid]

Aceman,

I'm thinking that he selects "apple" and then starts to type "pin" for "pineapple"... Your example removes apple from the row source... My thinking is if it is not in the rowsource, it can't remain selected.

 

[TheAceMan1]

lameid . . .

Well ... [blue]puforee[/blue] started off asking for it, and quite frankly it's not clear to me which way [blue]puforee[/blue] intends to go. So why don't we leave it up to [blue]puforee[/blue]! ... espcially since options abound!



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

I agree with Lameid, and I do not think this can be done either way easily. If this was not multi select then yes you could do it both ways. If you have a multiselect and then filter the list, you will get some very bizarre results. The highlights will remain in the area of the list but not on the same records because the items selected collection will be those indices. They will remain on the index value. Remember that the 'itemsselected" property is a collection of variants. So if you filter out the top 10 records and select the first visible record you will save the index (0) not 10.

So in order to do this you would have to save your own "items selected" which would be the list of primary keys or unique values. Then as you filter and unfilter the list you would have to loop that and re select.

Navigating is as problematic. In a single select this is easy. You can just do a find first on the recordset and if not .nomatch you can set the selected index to the absolute position of recordset + 1. It will scroll to that position. If you do not want to automatically select the value then just unselect it in code. Unfortunately with a multiselect it will not scroll. I have seen Leban's code using the API to scroll a listbox, but I have not been successful with it. I know of no way to scroll a multiselect without API.

 

[lameid]

To sum up, it looks like the best options are a lot of work and a near miss to target.

MajP, Thanks for filling in those details, I'm glad I didn't try to figure it out.

One more thoght that may help get close enough...
I have used two listboxes on a screen before with recordsources that looked at a temp selection table (one is the recordsource and one is not in the temp) and on double click either append or delete the value from the temp table and requery both listboxes... This is like the wizards were you move fields from a box on the left to one on the right. That would give a way to deal with a single select listbox for navigation and show multiple values in another listbox.

Or you could use a temp table to maintain the selected items and always include them in addition to the filter like TheAceMan was using... the criteria would be an or IN (Select <Key> from <temp table>) You would have the problems of the user having to clear the textbox to see all available options and any prior selections may keep the list from scrolling more. And you would have to code reselcting the values when the filter changed.

To be clear I use "temp table" to mean an access physical table that is used for temporary data that should be located in the front end so that it is user specific (assuming each user has his own front end which is best practice).

 

[MajP]

As I said you can do a find as you type multi select combo, but it is pretty complicated in maintaining your own list of selected values, unhighlighting, selecting and then rehighlighting. It can be done, it just is not that easy. If you really want to go that route I can demo it. I think I would go with a combination control. A find as you type along with a to from listboxes. So you would have a listbox on the left side that is find as you type (a textbox over or under the listbox to narrow/expand the search). Then another listbox on the right side like a common wizard as Lameid describes in which you select from the left and move to the right. If the list you are selecting is very long and the list of items selected is short this is a nice control because you can see all your selections without a lot of scrolling.

 

[TheAceMan1]

lameid ... MajP ...

[blue]That's correct, you can navigate through a Listbox in that manner, but it doesn't have a true 'autoexpand' feature, like Comboboxes do, where you can keep modifying the entered string, from J to JA to JAM, etc., [purple]which seems to be what the OP is expecting.[/purple][/blue]

So your saying [blue]missinglinq[/blue] is wrong as well? ...

[purple]Your opinions are noted[/purple] ......... lets see what [blue]puforee[/blue] (who this is all about ... and not you) has to say.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

No I totally agree with Missingling because what he describes is exactly the limits of a list box and it works as he describes.
I am agreeing with lameid that your proposal to filter the listbox in a multiselect is very problematic. It will work in a single select.

You cannot filter down the list of a multi select listbox and expect the selections to remain correct, without a lot of additional code. You would have to store the selected items then as you filter the list unselect all and reselect those that are visible. I am not disagreeing that is just the facts. I guess if you only select items within your filter it would work. But if you want to do multiple filters or re-expand you will run into trouble.

My other point was there is a problem with lameid's suggestion to set up an external navigation. So you could type in "Jame" in a textbox and make the listboxl go to the first record with "Jame..", as the OP desires. That works well in a single select and will scroll to that location. If I do that in a multiselect, the listbox will not scroll. So that is not a very good work around.

 

[TheAceMan1]

MajP . . .

I have too much respect for you guys to bicker. Nor has my respect for either you changed.

I understand what you guys are saying . . . fully, and I agree.

There's two roads here. One with and one without multiselect.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lameid]

MajP, I personally don't have the time to write a demo to do as you suggested and I believe the OP would much apprecite it. I'd be curious to see how you solve it but if you want to wait for the OP to ask that is good too. I think after your notes I've come to about the same solution plan in my head as you but nuances often change my plans. Thanks again for your insight on this one. It has been enlightening.

 

[puforee]

WOW..great feedback. Thanks to all. I am going to take some time to Mull this over. One last thought.....

Situation 1: Use continues typeing to select a single person.
Situation 2: Use a multiple select to find several persons.

I probably not try to do both at the same time. But I like lameid's idea of building a list.
So, type in joh and get john and store it. Clear and start typing again sa and get Sam...store.
Etc.

Then open a form based on the stored results.

 

[lameid]

Unless I am missing something in what you want to do, Situation 1 is what TheACEMan solution will solve with change the where clause...

Situation 2... what is different here from what you have already asked with the multi-select listbox?