CODEWITHSUNDEEP
ms-accesslistboxsetselectedmultiple-columns
Pete Danes
Pete Danes

Reputation: 289

Set 'selected' value in Access multicolumn listbox

I am using several bound ListBox controls on an Access form. The ListBox contents are related, so that, besides other things, when the user clicks on one ListBox, the selected row can change in others. All works fine, I have done this many times, as long as the ListBox has a single column. I use the super-simple VBA syntax lstBoxWhosis = "SomeText"

and as long as the ListBox does contain that item, the selected row becomes the one containing that text, with no other search, find or reposition commands necessary.

My current headache is a multicolumn ListBox. The left-most column does not have unique entries, and when I use the above syntax, I get a reposition, but only to the FIRST row containing the text. I need to position according to the values in TWO columns. Is there some similar way to do this with multiple columns, or must I resort to searching the ListBox contents manually?

I've already tried concatenating the columns with space, comma and semicolon, also the syntax

lstBoxWhosis = array("SomeText1","SomeText2")

No luck with any of those.

Remou:

I am doing it that way now, and it's actually fairly simple. Here's the code:

lstSpoje = lstKatastr
Do Until lstSpoje.Column(1) = lstOblast
  lstSpoje.Selected(lstSpoje.ListIndex + 1) = True
Loop

The first line positions directly to the start of the block in the first column, the second steps by one, checking the second column until it gets the correct row. The number of duplicates does not warrant a more efficient searching method. It works okay, except the display flutters during the repositions.

Otherwise, I posted everything I thought was pertinent. The ListBoxes are bound, as I stated in the very first sentence, which means they are based on a table or a query. If there is some other information that you think might help someone solve this, let me know what that might be.

Remou:

Yes, that would undoubtedly work. I actually already do something similar, one of the ListBoxes has almost 15,000 items, so to help the user locate stuff, I put in 26 CommandButtons, with letters, that move that ListBox to the start of that letter. I use the following code to positition the ListBox

x = ActiveControl.Caption
i = CurrentDb.OpenRecordset("SELECT Count(*) FROM TableOfKatastrs WHERE Left(Katastr,1)<'" & x & "'").Fields(0)
lstKatastr.Selected(i) = True

I may go to a similar scheme for this - it would get rid of the flickering problem and be faster to boot. But I was hoping for some little-known syntax with the simplicity of my first example, where one line of code does EVERYTHING:

lstBoxWhosis = "SomeText"

Remou:

I'd settle for two, since I'm using two columns. :) But seriously, the simple assignment syntax is so straightforward and elegant, and there's so often some odd phrasing that is little used, little known, that solves problems like this. I've tried all sorts of other things, like

lstSpoje.Column(1) = "SomeText2"

but all I get is syntax errors. Maybe it really is only applicable to the first (or only) column.

Upvotes: 1

Views: 4762

Answers (1)

Pete Danes
Pete Danes

Reputation: 289

I was searching for something along these lines, and ran across this old post. I know this is down the road a ways, so this more in the spirit of filling in information for any future readers who may run across this while searching the archives.

I figured out another way of doing this - DUPLICATE the column (or concatenated columns into one column) by which I want to set the proper line of the list box, as the FIRST column, and make its width zero. It will not show, so the listbox will look normal to the user, but the standard syntax of 

lstBoxWhosis = "value"

will then work normally.

Upvotes: 0

Related Questions