listbox Run-time error '7777' (1 Viewer)

sierra467

Registered User.
Local time
Yesterday, 21:41
Joined
Aug 1, 2005
Messages
66
I am getting a Run-time error '7777' that states "You've used the ListIndex property incorrectly." for code placed in an update button and it only occurs on the 1st entry in the listbox (all others entry's work fine).

The form that I have contains a listbox (lstIngredients), a textbox (txtIngredients), and an update button (cmdUpdate). lstIngredients contains a list of ingredients and in order to change the spelling of one or edit the text of an ingredient, the user clicks on an ingredient and it's contents is displayed in txtIngredient to be able to be edited. When the user edits the entry, they click cmdUpdate to:

1. Update the table
2. Reflect changes in the listbox
3. and finally highlight the newly edited item in the listbox.

The code for the update button is:
Code:
Private Sub cmdUpdate_Click()
'   Declare variables
    Dim strSQL As String
    Dim intNewListIndex As Long
    
'   Create sql to update existing record with the new value entered in txtNewIngredient
    strSQL = "UPDATE tblIngredients " & _
             "SET strIngredient='" & txtIngredient & "' " & _
             "WHERE id=" & lstIngredients.Column(0) & ";"
    'Run sql statement made above
    DoCmd.RunSQL strSQL
    
'   Update listbox with new value
    intNewListIndex = FindListIndex(lstIngredients, 1, words, Me.txtIngredient.Value)  
    With Me.lstIngredients
        .SetFocus   'give focus to be able to requery
        .Requery    'Reload values back into listbox
        [B].ListIndex = intNewListIndex[/B]      'Select updated value in listbox
    End With
    
End Sub
(The FindListIndex is a function I wrote to return the ListIndex of an entry in a listbox and is working correctly. It basically goes through each item in the listbox and compares the text given to it (txtIngredient) to the listbox's .Column(1) value until it makes a match. Once a match is made it returns the listindex of that item)

The problems comes in the 2nd to last line of my code (.ListIndex = intNewListIndex). If I try to set the .ListIndex=0 (the first item in the listbox) to select it , I get the runtime error. The funny thing is that the code above only gets the runtime error if editing the first entry in the listbox. Once getting the error, if I choose debug to go to the code where it is highlighted in yellow (.ListIndex = intNewListIndex) and then press F5 to continue running the code, it does not error out again. It instead finishes out the code and works correctly. It almost seemed like the code needed a break to do what it was supposed to do and then does it.

This peculiarity does not happen with any other item in lstIngredients, only the 1st one. Can someone please help me by explaining what is going on and how to go about fixing it. I have been working on it for a long while and can not figure it out.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 01:41
Joined
Sep 12, 2006
Messages
15,614
the combo box is zero -based, ie the first entry is list index(0).

is your function findlistindex returning 0 correctly, or is it returning 1 for the first item.

not sure if this would produce your error.

put a breakpoint before the findlistindex function and see whats happening line by line
 

sierra467

Registered User.
Local time
Yesterday, 21:41
Joined
Aug 1, 2005
Messages
66
Thanks for responding gemma-the-husky.

I know that listboxes and comboboxes are 0 based and my function is returning a zero. I know this for sure because I did step through the code and saw that it was returning a 0 (like you had suggested).

I actually thought that it might be returning an integer instead of a long datatype so I just inserted a 0 for the intNewListIndex in the 2nd to last line to see if that would make any difference, but that did not. I also tried a datatype conversion and that did not make any difference either.

Any other ideas would be greatly appreciated. Thanks.
 

sierra467

Registered User.
Local time
Yesterday, 21:41
Joined
Aug 1, 2005
Messages
66
Thanks for responding gemma-the-husky.

I know that listboxes and comboboxes are 0 based and my function is returning a zero. I know this for sure because I did step through the code and saw that it was returning a 0 (like you had suggested).

I actually thought that it might be returning an integer instead of a long datatype so I just inserted a 0 for the intNewListIndex in the 2nd to last line to see if that would make any difference, but that did not. I also tried a datatype conversion and that did not make any difference either.

Any other ideas would be greatly appreciated. Thanks.
 

gray

Registered User.
Local time
Today, 01:41
Joined
Mar 19, 2007
Messages
578
Hi All

I was experiencing this problem too... in fact sometimes it worked perfectly and other times it failed with the above error ..

I finally sussed that, having been manipulating the combobox's locked/enabled properties, the error appeared when I had the box locked.. and I suppose that makes perfect sense.. but it might have been nice of our Microsoft friends to have produced a more meaningful error maybe?

I spent hours on this... checking the focus, headers, bound columns you name it...

Hope this helps someone out there....
 

delikedi

Registered User.
Local time
Yesterday, 18:41
Joined
Apr 4, 2012
Messages
87
came across this post while searching for a solution myself.

apparently trying to set the listindex of a combo/listbox when that listbox does not have the focus also causes this error. a quick solution then is

[thatlistbox].setfocus
[thatlistbox].listindex = 2 or something

hope this info helps others.

edit: lol apparently sierra had already mentioned this, I feel kinda dumb now :eek:
 
Last edited:

craigachan

Registered User.
Local time
Yesterday, 18:41
Joined
Nov 9, 2007
Messages
282
I confirm... All I had to do was set the focus on the cbo first and everything went well.
 

walidzohair

New member
Local time
Today, 03:41
Joined
Jul 21, 2020
Messages
1
I believe the source of your faulty code comes from another web site (maybe bytes?). I am not sure but I guess it is different in excel vba than in access vba. At least in ms access vba, listindex is read only and combo.value = combo.itemdata (i) is what is used to set the selections.

below is the code used to cycle through combobox list items using arrow keys.

Code:
Public Sub ArrowKeys(ByRef KeyCode As Integer, combo As ComboBox, ByRef Flag As Boolean)
' flag is used to be able to toggle normal action in combo_change()
Select Case KeyCode
    Case vbKeyDown
        KeyCode = 0 ' make sure the action wont be duplicated
        If combo.ListIndex <> combo.ListCount - 1 Then
          combo.Value = combo.ItemData(combo.ListIndex + 1)
          Flag = True
          combo.Dropdown
        Else
          combo.Value = combo.ItemData(0)
          Flag = True
          combo.Dropdown
        End If
  
   Case vbKeyUp
        KeyCode = 0 ' make sure the action wont be duplicated
        If combo.ListIndex = -1 Then ' In case nothingg is selected yet (-1 index)
          combo.Value = combo.ItemData(combo.ListCount - 1)
          combo.Dropdown
        ElseIf combo.ListIndex <> 0 Then
          combo.Value = combo.ItemData(combo.ListIndex - 1)
          Flag = True
          combo.Dropdown
        Else
          combo.Value = combo.ItemData(combo.ListCount - 1)
          Flag = True
          combo.Dropdown
        End If
End Select
End Sub
 

Users who are viewing this thread

Top Bottom