Solved

ListIndex property problem - works fine except for Access 2003

Posted on 2006-06-22
9
571 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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

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.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

624 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