Solved

ListIndex property problem - works fine except for Access 2003

Posted on 2006-06-22
9
564 Views
Last Modified: 2008-01-09
i have the following code that cycles through a listbox based on a letter someone types in a textbox.  It's part of the change event of the textbox.  It works beautifully on Access 2000, 2002, but NOT on 2003.  Does anyone have a clue as to why?  500 points for urgency.

here's the code:

Private Sub txtGo2_Change()
Dim s As String, p As String
Dim i As Integer, prevI As Integer
If txtGo2.Text = "" Then  'nothing typed
    Exit Sub
End If
s = Me.txtGo2.Text

'Note the use of the ListIndex property of the ListBox
'If the ListIndex is -1 means nothing selected
'If 0 means the first item selected
Me.lstProperty.SetFocus
Me.lstProperty.ListIndex = 0

For i = 0 To lstProperty.ListCount - 1
If i = 1000 Then Exit Sub
    'use the LIKE operator to compare
    'convert both to Uppercase as well so case does not matter
    p = lstProperty.Column(1, lstProperty.ListIndex)
    If UCase(p) Like UCase(s & "*") Then
        On Error Resume Next
       
        Forms("frmMain").Controls("lstProperty").ListIndex = i
        Me.txtGo2.SetFocus
        Me.txtGo2.SetFocus
        Me.txtGo2.SelStart = Len(Me.txtGo2.Text)
        'SendKeys "+{F2}"
        SetGlobalEye (i)
        Exit Sub
    End If
    'On Error GoTo Err_Controller
    lstProperty.ListIndex = lstProperty.ListIndex + 1
Next

Err_Exit:
    Exit Sub
Err_Controller:
      msgbox "There was an error"
        Exit Sub

End Sub
0
Comment
Question by:normenclature
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Author Comment

by:normenclature
ID: 16965749
P.S.  I get an error 7777- "You've used the ListIndex Property incorrectly"
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
ID: 16966126
Hi Normenclature,

I believe your code is failing at this line:
>Me.lstProperty.ListIndex = 0

To set the selected item in a listbox, you need to use the Selected property.  So to set the first row as the selected row:
Me.lstProperty.Selected(0) = True
0
 
LVL 61

Expert Comment

by:mbizup
ID: 16966177
It looks like you have a few other places that could use the same correction.  Use the ListIndex property to retrieve the selected row, and the Selected property to set the selected row.

Private Sub txtGo2_Change()
Dim s As String, p As String
Dim i As Integer, prevI As Integer
If txtGo2.Text = "" Then  'nothing typed
    Exit Sub
End If
s = Me.txtGo2.Text

'Note the use of the ListIndex property of the ListBox
'If the ListIndex is -1 means nothing selected
'If 0 means the first item selected
Me.lstProperty.SetFocus
Me.lstProperty.Selected(0) = True                              '*** Here

For i = 0 To lstProperty.ListCount - 1
If i = 1000 Then Exit Sub
    'use the LIKE operator to compare
    'convert both to Uppercase as well so case does not matter
    p = lstProperty.Column(1, lstProperty.ListIndex)
    If UCase(p) Like UCase(s & "*") Then
        On Error Resume Next
       
        Forms("frmMain").Controls("lstProperty").Selected(i) = true          '****** Here
        Me.txtGo2.SetFocus
        Me.txtGo2.SetFocus
        Me.txtGo2.SelStart = Len(Me.txtGo2.Text)
        'SendKeys "+{F2}"
        SetGlobalEye (i)
        Exit Sub
    End If
    'On Error GoTo Err_Controller
    lstProperty.Selected ( lstProperty.ListIndex + 1) = True                   '**** here
Next

Err_Exit:
    Exit Sub
Err_Controller:
      msgbox "There was an error"
        Exit Sub

End Sub
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16966192
Without going into a disection of the code I believe it is this line that is producing the error...

        lstProperty.ListIndex = lstProperty.ListIndex + 1

I'm betting the error occures when the value you attempt to set the .ListIndex property to exceeds the maximum value possible (.ListCount -1)

If you add the following code you'll avoid the error you're getting (I think) although as some have mentioned there are other issues apparent in the code worthy of review.

    If Me.lstProperty.ListIndex > (Me.lstProperty.ListCount - 1) Then   'Don't set the list index greater than the number of items avail...
        lstProperty.ListIndex = (lstProperty.ListIndex + 1)
    End If

Rick

0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 400 total points
ID: 16966626
After looking over the code a little more carefully I think I see the essence of what you are trying to do.  Here's a revision of your code I think you'll find works a lot better, (it's faster and doesn't error).

'********************
'*** Now for the Code ***
'********************

Option Compare Text    'This goes at the header of the module and will avoid the need to use the UCase Statement when comparing text within the module
Option Explicit              'This is just generally good practice.  It insures that any variables used are explicitly declared.

Private Sub txtGo2_Change()
    Dim strText As String       'Previous variable was named "s".  Changed to provide a more meaningful variable name
    Dim strProperty As String   'Previous variable was named "p".  Changed to provide a more meaningful variable name
    Dim lngIndex As Long        'Previous variable was named "i" and was originally defined as an integer
                                'Changed to long because list boxes can have up to 65536 rows
                                'If the list box exceeded 32768 rows (the largest # an Integer can store) the code would crash.
                                'By using a Long we are assured this will not happen.
    strText = Me.txtGo2.Text
   
    'If a Column Header was present the list should start counting at 1 not 0.
    'By adding the Absolute Value of the .ColumnHeads Property (would be 0 or -1)
    'We insure that we start counting at 0 or 1 as appropriate.
    With Me.lstProperty
        For lngIndex = 0 + Abs(.ColumnHeads) To (.ListCount - 1)    'If there is a header we must start counting at 1 not 0 Hence the use of Abs(.ColumnHeads)
            strProperty = .Column(1, lngIndex)
            If (strProperty Like strText & "*") Then                'If we put Option Compare Text at the module header we don't need the UCase Statements
                .SetFocus
                .ListIndex = lngIndex
               
                With Me.txtGo2
                    .SetFocus
                    .SelStart = Len(strText)
                End With
                Exit For
            End If
        Next lngIndex 'Was just next (by declaring it as "Next lngIndex" it's sort of like a comment (it's telling us what this is for)
    End With
End Sub
0
 
LVL 1

Author Comment

by:normenclature
ID: 16979419
Thanks to all!   I will check these today and finalize this tonight.  I'm sure one of these will work.  Will advise soon--

Norm
0
 
LVL 1

Author Comment

by:normenclature
ID: 16986110
Hi all--

mbizup:  your code statement seems to work, but then when it tries to set the focus on the txtGo2 textbox again, it "blows up" on me, in that Access closes and it offers to create a backup.

Any ideas?

n
0
 
LVL 1

Author Comment

by:normenclature
ID: 16986251
Rick,

Thanks pal!  your code worked great!!  What is interesting is that your code works even in Access 2003 with the .ListIndex setting the long integer, but your code also works with mbizup's suggestion of using .Selected(i) = True , so Assisted points for mbizup

Thanks again!
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16988737
Glad you liked it. :)

Rick
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now