Set Focus & 2108 error

[dave h]

Hi,

This is just an FYI for anyone who may have lost as much time as I did with
this set focus issue. The case is this:

You have 2 list boxes and you want to update the 2nd one when a user selects
a row in the first one. However, lets say for some reason you want to have
a particular row selected/highlighted in the 2nd list box. Further, lets
say that "list2 = list2.itemdata(somevalue)" does not work because you don't
have a column in the list box that is always unique. This bit of code will
select the first bound column that matches - but that may not be the row you
want. Or, your case my be more like mine where I was trying to iterate
through the list box to develop some totals.

The most direct approach is to use listIndex and set it to the row you want.
However, you must set focus first. Here is where the problem comes in. I
get getting this miserable 2108 error.

The problem started because I would select my list box event in the VBA
dropdown event list and use the default "before" update event. For whatever
reason, using the before update for the 1st list box, screws up trying to
set focus on the 2nd list box. Here is the code:

Private Sub Form_Load()
List1.RowSource = "select * from table1"
End Sub

Private Sub List1_AfterUpdate() 'this works fine
List2.RowSource = "select * from table2"
List2.SetFocus
List2.ListIndex = 1
End Sub

Private Sub List1_BeforeUpdate(Cancel As Integer)
' it you put the above code in here you will get a 2108 runtime error
End Sub

Maybe I'm the only person in the world who used the default "before" and got
into this mess - but, if there is a similar dummy out there, this might save
you some time.

 

[Dirk Goldgar]

Hi,

This is just an FYI for anyone who may have lost as much time as I
did with this set focus issue. The case is this:

You have 2 list boxes and you want to update the 2nd one when a user
selects a row in the first one. However, lets say for some reason
you want to have a particular row selected/highlighted in the 2nd
list box. Further, lets say that "list2 = list2.itemdata(somevalue)"
does not work because you don't have a column in the list box that is
always unique. This bit of code will select the first bound column
that matches - but that may not be the row you want. Or, your case
my be more like mine where I was trying to iterate through the list
box to develop some totals.

The most direct approach is to use listIndex and set it to the row
you want. However, you must set focus first. Here is where the
problem comes in. I get getting this miserable 2108 error.

The problem started because I would select my list box event in the
VBA dropdown event list and use the default "before" update event.
For whatever reason, using the before update for the 1st list box,
screws up trying to set focus on the 2nd list box. Here is the code:

Private Sub Form_Load()
List1.RowSource = "select * from table1"
End Sub

Private Sub List1_AfterUpdate() 'this works
fine List2.RowSource = "select * from table2"
List2.SetFocus
List2.ListIndex = 1
End Sub

Private Sub List1_BeforeUpdate(Cancel As Integer)
' it you put the above code in here you will get a 2108 runtime error
End Sub

Maybe I'm the only person in the world who used the default "before"
and got into this mess - but, if there is a similar dummy out there,
this might save you some time.

Is there some reason you wouldn't just write

Me!List2.Selected(1) = True

?

 

[dave h]

Hi Dirk,

Thanks for the coding tip, you showed me another way to solve the problem.
Here is how I was actually using the code to iterate thru the entire list
box when there is no unique column:

List2.RowSource = "select * from table2"
Dim rowCount As Integer
Dim i As Long
rowCount = List2.ListCount
For i = 0 To rowCount - 1
Me!List2.Selected(i) = True
Debug.Print List2.Column(1)
Next i

The Setfocus approach will give an error unless used as I described before.
Your Selected() approach works in either of the cases I described.

Mostly, I was just trying to share my experience in getting that 2108 error
in case it puzzled someone else as much as it did me. However, the bonus
for me was finding out about how to use "selected()" in the fashion you
showed.

95% of my problems get solved by using this newsgroup - as opposed to the 5%
that I solve by thumbing through the pile of Access books I bought. The
books are generally a disappointment as they seldom have enough detail for
things like working with combo and list boxes in a more complex setting. Do
you recommend any books for Access and VBA?

 

[dave h]

After reading the "Selected()" documentation, I realized this processing
could be shortened to:

List2.RowSource = "select * from table2"
Dim i As Long
For i = 0 To List2.ListCount - 1
Debug.Print List2.Column(1, i)
Next i

 

[Dirk Goldgar]

Mostly, I was just trying to share my experience in getting that 2108
error in case it puzzled someone else as much as it did me.

That kind of sharing is what these newsgroups are all about.

However,
the bonus for me was finding out about how to use "selected()" in the
fashion you showed.
Great!

95% of my problems get solved by using this newsgroup - as opposed to
the 5% that I solve by thumbing through the pile of Access books I
bought. The books are generally a disappointment as they seldom have
enough detail for things like working with combo and list boxes in a
more complex setting. Do you recommend any books for Access and VBA?

That depends on where you are right now. I don'r have a big list of
books, because I learned mainly from the help files, from the
newsgroups, and from trial and error. John Viescas has a fine book,
_Access 2003 Inside Out_, from Microsoft Press, and the _Access
<version> Developer's Handbook_, by Getz et. al., from Sybex, is the
best and most comprehensive "how-to" book for serious developers that
I've ever seen. It's rather on the technical side, though.

 

[Dirk Goldgar]

After reading the "Selected()" documentation, I realized this
processing could be shortened to:

List2.RowSource = "select * from table2"
Dim i As Long
For i = 0 To List2.ListCount - 1
Debug.Print List2.Column(1, i)
Next i

You bet, if all you really want to do is print the data in the columns
of the list box.