Item Not Found In This Collection (ListBox)

[Voodeux2014]

I am getting the error of "Item not found in this collection" on my listbox. I have listed code and row source information below.

Private Sub cmdFinish_Click() 
Dim MyDB As dao.Database
Dim varItem As Variant
Dim lst As ListBox


Dim rst As dao.Recordset


If lstApprovedClasses.ItemsSelected.Count = 0 Then
MsgBox "Please select at least 1 (one) class to add to this grant.", vbOKOnly, "Error"
Exit Sub
End If


Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tbl2GrantTrainers", dbOpenDynaset, dbAppendOnly)


With rst
For Each varItem In lstApprovedClasses.ItemsSelected
.AddNew
![GrantID] = [Forms]![frmAddNewGrant]![ID]
![ClassID] = lstApprovedClasses.ItemData(varItem)
![CurSuggestedHours] = InputBox("Enter suggested hours for [lstApprovedClasses(varItem)", "Suggested Course Length")
![CurMaxApprovedSeats] = InputBox("Enter available seats for [lstApprovedClasses(varItem)", "Available Seats")
.Update
Next varItem
End With


rst.Close
Set rst = Nothing


MsgBox "The training providers have been successfully added to the new grant.", vInformation, "Information"


DoCmd.Maximize
End Sub

I am getting the error on this line:

![ClassID] = lstApprovedClasses.ItemData(varItem)

Here is the SQL statement for the listbox (lstApprovedClasses):
SELECT tbl2TrainingOffered.ClassID, tbl1Classes.ClassName FROM tbl1Classes INNER JOIN tbl2TrainingOffered ON tbl1Classes.ClassID = tbl2TrainingOffered.ClassID WHERE (((tbl2TrainingOffered.ProviderID)=[Forms]![frmAddClassestoGrant]![cboTrainingProvider])) ORDER BY tbl1Classes.ClassName;

[pbaldy]

The error would imply that ClassID is not a field in the table the recordset was opened on. Is it?

[orange]

Did you try stepping through the code and seeing the values of variables?

[Voodeux2014]

thanks pbaldy, didnt even think to look at that. I copied code from a different ocations and didnt change the data i wanted it to be looking into. Thanks!

[pbaldy]

Happy to help!

[Voodeux2014]

I know I marked this complete, but I would like to know for the InputBoxes I have coming into play for each selection in the listbox, if there is a way to display which selection the InputBox is for?

for example:

"Please enter the available seats for [class name displayed here]."

The ListBox is bound to the ClassID, but displays the ClassName. Is there a way to have the InputBox display the ClassName for the current selection that is is in the "For each varItem" loop?

[pbaldy]

Hey, you already marked this as solved!!

Presuming the name is in the second column try:

"Please...for " & lstApprovedClasses.Column(1, varItem)

[Voodeux2014]

Worked Brilliantly! Thanks again!

[pbaldy]

No problemo!