Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ListIndex property problem - works fine except for Access 2003

Posted on 2006-06-22
9
Medium Priority
?
574 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 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 1600 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

773 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