Listbox - how many items?

[gxdata]

I have a mental block about this, and can't find a ready solution. I'm sure
it's very simple.

I have an unbound listbox into which the user adds items selected from two
other controls (combos - cbPoint1, cbPoint2). The listbox has 4 columns,
essentially Point1_ID, Point1_Name, Point2_ID, and Point2_Name.

What I need to do is make sure that the user doesn't choose the same
combination (Point1_ID, Point2_ID) more than once and try to add it to the
listbox - so I need to loop through the existing items in the listbox and
read the contents of the 4 columns. The logic to exclude a candidate for the
listbox is simple enough.

I've tried a bit of code to set the .ListIndex proprty when nothing has been
chosen in the listbox, and then return the .Column(i) values. I get error
"You've used the ListIndex property incorrectly."

' here, n1, n2 are the Point1_ID, Point2_ID values.
Function InList(n1 As Integer, n2 As Integer) As Boolean
On Error GoTo ErrFN
Dim lng As Long , frm As Form
Set frm = Forms!frmPoints2
For lng = 0 To frm!listChosen.ListCount - 1
Debug.Print "ListIndex = "; frm!listChosen.ListIndex
Forms("frmPoints2").Controls("ListChosen").ListIndex = lng ' ERROR
here
' frm!listChosen.ListIndex = i
If frm!listChosen.ItemData(lng).Column(0) = n1 _
And frm!listChosen.ItemData(lng).Column(2) = n2 Then
InList = True
Debug.Print "Gotcha"
Exit Function
End If
Next

Exit_Here:
Exit Function
ErrFN:
Debug.Print Err.Description
Resume Exit_Here
End Function

TIA - Ian Thomas

 

[Douglas J. Steele]

The ListIndex property is read-only.

Assuming your listbox isn't multi-select, use:

Forms("frmPoints2").Controls("ListChosen").Selected(n) = True

where n is the specific entry. Remember that the rows start numbering at 0.

 

[gxdata]

OK, I think i see what the problem is - if Multiselect is "on" - ie, Simple
or Extended, then the .Column() is always inaccessible (=Null); and so -
apart from my excursion into trying to set a readonly property - I could get
no further.

I think your hint has cleared the way to solving the problems. Thanks.

Ian Thomas

 

[Douglas J. Steele]

I haven't tested, but I don't believe there's any restriction on using
..Column with MultiSelect on. The "trick" is that you need to specify a row
number as the 2nd parameter. In other words,
frm!listChosen.ItemData(lng).Column(0, 0) will give you the first column of
the first row, frm!listChosen.ItemData(lng).Column(0, 1) will give you the
first column of the second row, and so on.

 

[gxdata]

Thanks, Douglas - I wondered whether there should be a (row,col) syntax but
decided not - so did not try. I will experiment.

ILT

 

[Stephen Lebans]

The ListIndex property is read-only.

Hi Doug,
If the control has the focus then you can write to the ListIndex prop.
This prop can be used to scroll a specific row into view, even with a
multi select setting.


HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Douglas J. Steele]

Hi Doug,
If the control has the focus then you can write to the ListIndex prop.
This prop can be used to scroll a specific row into view, even with a
multi select setting.

Gee, you mean there's an error in the Help file? <g>