ListIndex property is reset if I SetFocus

[Jon Ley]

I have a form with a listbox which has AfterUpdate code behind it. The code
collects data from various columns in the list box to populate other controls
on the form (ready for the user to edit) and sets the focus to another
control. The SetFocus command is called before the other controls have
finished being populated, but as soon as the SetFocus is called, the
ListIndex property of the ListBox is reset to -1 and I cannot retrieve any
further information from the ListBox columns (all return Null).

This code has worked fine for ages in Access 2002, but fails when I open the
same database in 2007. Also, if I break the code and set the focus back to
the ListBox, the ListIndex property is restored to its correct value and I
can continue to populate the remaining controls.

 

[Dale Fye]

Can you post your code? If your listbox is not multi-select, you really
shouldn't need to refer to the controls listindex. My guess is that you are
doing something like:

me.txt_First_Name = me.lst.column(1, me.lst.listindex)
me.txt_Last_Name = me.lst.column(2, me.lst.listindex)

since the row element of this is optional, you can probably just replace
this with:

me.txt_First_Name = me.lst.column(1)
me.txt_Last_Name = me.lst.column(2)

HTH
Dale

 

[Jon Ley]

Dale,

No, the list box is not multi-select, and I am using the .Column property.
The code is:

*** Start of code ***
Private Sub lstScores_AfterUpdate()

If lstScores.ListIndex < 0 Then
Exit Sub
End If
cboCategoryID = lstScores.Column(0)
Call cboCategoryID_AfterUpdate 'xxxx
cboSectionID = lstScores.Column(1)
Call cboSectionID_AfterUpdate
cboItemID = lstScores.Column(2)
Call cboItemID_AfterUpdate
cboScore = lstScores.Column(4)
cboAction = lstScores.Column(6)
txtComments = DLookup("Comment", "tmptblAuditDetails", _
"AuditID = " & txtAuditID & " And " & _
"ItemID = " & cboItemID)

End Sub
*** End of code ***

The line that is commented 'xxxx is where the SetFocus gets called. All
attempts to get at any .Column(x) entries after that point return Null.
However if I break the code immediately after 'xxxx and reset the focus to
the listbox, the ListIndex value is restored to what I would expect it to be
and all the subsequent .Column(x) calls work fine.

Thanks,

Jon.

 

[Douglas J. Steele]

Can you not just set focus back to the list box then?

Me.lstScores.SetFocus

 

[Jon Ley]

Douglas,

Yes I can, but this is a difference in behaviour between Access 2002 and
Access 2007. I have several large database systems in use throughout our
organisation, all currently running quite happily on 2002 or 2003. As we buy
new machines we are having to get 2007 with them.

It is going to take me a very long time to trawl through all my code to find
other places where I might encounter the same problem.

Jon.

 

[Dale Fye]

How about adding a lngIndex variable, and setting it to the listboxes
listindex right after the listindex < 0 test. Then you can refer to the
lngIndex in you column properties.

If lstScores.ListIndex < 0 Then Exit Sub

lngIndex = lstScore.listindex
cboCategoryID = lstScores.Column(0, lngIndex)
Call cboCategoryID_AfterUpdate 'xxxx
cboSectionID = lstScores.Column(1, lngIndex)
Call cboSectionID_AfterUpdate
cboItemID = lstScores.Column(2, lngIndex)
Call cboItemID_AfterUpdate
cboScore = lstScores.Column(4, lngIndex)
cboAction = lstScores.Column(6, lngIndex)

HTH
Dale

 

[Jon Ley]

Dale,

Perfectly good suggestion, thank you. However, as I said to Doug, my problem
is not really how to work around this particular occurrence of the problem. I
am more concerned about having to trawl through dozens of forms and hundreds,
if not thousands, of lines of code to find other places where this might be
an issue. We only have one or two users currently on Access 2007, but as more
users, using different databases, transfer over from XP/2003 I can see that I
am going to suddenly start getting an rush of similar problems.

I was just wondering why 2007 has suddenly started acting differently to
previous versions in this respect, and whether it is regarded as a 'bug' that
might be fixed at some point.

Regards,

Jon.

 

[Dale Fye]

Have not started using 2007 yet. At work, we don't normally move to a new
operating system or new Office Suite until at least 18 months after its
initial release. At that point we are generally on SP2 or 3 and a lot of
these little nuisances have been addressed.

Dale