Adding all items in ListBox

[Voodeux2014]

Private Sub cmdSaveClass_Click()
Dim MyDB As DAO.Database
Dim varItem As Variant
Dim lst As ListBox


Dim rst As DAO.Recordset


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


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


With rst
For Each varItem In lstSelectedStudents
.AddNew
![StudentID] = lstSelectedStudents.ItemData(varItem)
![ClassID] = cboClassSelection
![ClassDate] = txtClassDate
![HoursTaken] = txtHoursTaken
.Update
Next varItem
End With


DoCmd.Close acForm, "frmAddClassRoster"


rst.Close
Set rst = Nothing


DoCmd.Maximize
End Sub

I am getting the following error on the "For Each varItem In lstSelectedStudents" line

Run-Time Error '438'
Object doesn't support this property or method


Now I know this procedure works as I am using it on a different form. I changed all the information to reflect the new table I am trying to save to as well as referring to what fields I am trying to gather that information from. The other time it is used it just for selections in a listbox. This time I have created 2 listboxes. The first listbox contains all students that have ever been entered into the database. The second listbox contains only those that have been moved to the listbox from list1, removing it from list1 in the process. Any ideas on how to have the listbox cycled through similar to when .ItemsSelected is being used, but this time to have all that are listed.

[pbaldy]

A For/Next loop:

For x = 0 To Me.ListboxName.ListCount - 1

[Voodeux2014]

Thanks. That got me past that error and it appears to be stepping through the code properly now. Only issue is after the code is run and the form is closed. I go to check the table for verification and no data is added.

[June7]

Maybe need to programmatically select each item in the listbox. Otherwise, you aren't looking at each item in the listbox, just running the loop however many times as there are items in the list. But those items are not selected and the code is not pulling values. However, I would have expected that many records with the StudentID field blank - unless it is a required field, in which case the update fails.

Me.lstSelectedStudents.Selected(x) = True

[Voodeux2014]

That makes complete sense.

I have switched it up and added your line just below the "for x = 0" line
It appears to accept the code line, but still errors after highlighting the ".update"
It is giving an error to please enter number only.
The only field that isnt an exact number would be the ClassDate field, which is set up for Date/Time.

[Voodeux2014]

It seemed to be a validation rule I had entered a while back, that I have removed and it now works...partially...

It now adds new data and appears to be working. But it does not seem to change the StudentID. All new records that are added match the first entry in the listbox.

[pbaldy]

You haven't shown the new code, but you'd need to use the For/Next counter variable.

[Voodeux2014]

Here is the new code:

Private Sub cmdSaveClass_Click()
Dim MyDB As DAO.Database
Dim varItem As Variant
Dim lst As ListBox


Dim rst As DAO.Recordset


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


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


With rst
For x = 0 To Me.lstSelectedStudents.ListCount - 1
Me.lstSelectedStudents.Selected(x) = True
.AddNew
![StudentID] = lstSelectedStudents.ItemData(varItem)
![ClassID] = cboClassSelection
![ClassDate] = txtClassDate
![HoursTaken] = txtHoursTaken
.Update
Next
End With


DoCmd.Close acForm, "frmAddClassRoster"


rst.Close
Set rst = Nothing


DoCmd.Maximize
End Sub

I am unsure of how to have it switch to the new variable during the process.

[June7]

Is the second listbox set for multi-select, try single?

Code options:

Me.lstSelectedStudents.ListIndex = x

Or set value of listbox and just reference the listbox value.

Me.lstSelectedStudents = Me.lstSelectedStudents.ItemData(x)
...
![StudentID] = Me.lstSelectedStudents

[Voodeux2014]

Multi-select is set to none.

Should it be set to simple or extended?

[pbaldy]

You're using varItem instead of x.

[Voodeux2014]

Switching it to x instead of varItem seems to have fixed it. Thanks for the help again guys!

[pbaldy]

No problem.