Solved

ListIndex property problem - works fine except for Access 2003

Posted on 2006-06-22
9
570 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

739 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