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:
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
' Update listbox with new value
intNewListIndex = FindListIndex(lstIngredients, 1, words, Me.txtIngredient.Value)
.SetFocus 'give focus to be able to requery
.Requery 'Reload values back into listbox
.ListIndex = intNewListIndex 'Select updated value in listbox
(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.