listbox items selected not cycling in my loop

[cpberg1]

Hey all,

I'm trying to amend a piece of code originally from the Access cookbook (great practical examples BTW) that is used as a messaging feature between users of my database. It was written to create a single line in a table tblMessage for each UserMessage. I have amended the code to use a combobox with a UNION join to send message to all users. That was working great but didn't allow to conveniently send the message to a smaller group of users.

My idea now is to remove the combo and incorporate a multiselect listbox. I've got the listbox all created and the code amended to gather the To field from the appropriate column of the listbox. My problem is that the recordset variable "rstMail(To)" doesn't change as I would expect it to as I cycle through the items selected. EG.

For Each Varitem In frmMail.LbTo.ItemsSelected
some code here
Next Varitem

I thought that this would look at each of the rows of the listbox (that are selected) and do the some code here code.

The appropriate # of entries in the tblMessage are being created based on my listbox selections so I'm on the right track I believe. Why is the recordset variable "rstMail(To)" not changing each cycle through the items selected? The code currently fills the To field in all records with the last selected item from the listbox. I've also attached a photo of the form used.

I've read up on the VBA help file about listbox and item selected property but haven't made the break though. I've also tried changing the variable type of the listbox to control as well as object but it didn't have any positive effect.

My VBA and Access experience is limited, I can logically understand most code when I read it but would have a tough time to apply what I have learned to a complex problem written from scratch.

Public Function acbSendMail() As Integer

    ' Take the message and user from the
    ' frmMailSend form and send it to the mail
    ' backend
    
    On Error GoTo HandleErr
    
    Dim db As DAO.Database
    Dim rstMail As DAO.Recordset
    Dim frmMail As Form
    Dim rstUsers As DAO.Recordset
    Dim strSql As String
    Dim Varitem As Variant
    Dim LbTo As Object
        
Set frmMail = Forms("frmSendMail")
'If frmMail.cboTo <> "(All)" Then
'If frmMail.lbTo <> "(All)" Then
    Set db = CurrentDb()
    Set rstMail = db.OpenRecordset( _
     "tblMessage", dbOpenDynaset, dbAppendOnly)
    Set LbTo = frmMail.LbTo
   
   For Each Varitem In frmMail.LbTo.ItemsSelected
        rstMail.AddNew
        rstMail("From") = frmMail.txtFrom
        rstMail("FromDisplay") = frmMail.txtFromDisplay
'       rstMail("To") = frmMail.cboTo
        rstMail("To") = frmMail.LbTo.Column(1, LbTo.RowSource)
        rstMail("Subject") = frmMail.txtSubject
        rstMail("DateSent") = Now
        rstMail("Message") = frmMail.txtMessage
        rstMail("Link") = frmMail.txtLink
    rstMail.Update
    Next Varitem
'   frmMail.cboTo = Null
    frmMail.LbTo = Null
    frmMail.txtMessage = Null
    frmMail.txtLink = Null
    frmMail.txtSubject = Null

I appreciate the help and time of those more experienced and/or wiser than I! If anyone is interested in such a feature indicate so and I can post the remainder of the objects in a small sample database.

Best regards,
C

 

[JHB]

This line is wrong:

[FONT=&quot]rstMail("To") = frmMail.LbTo.Column(1, LbTo.RowSource)
[/FONT]

[FONT=&quot]Correct is:
[/FONT]

[FONT=&quot]rstMail("To") =[/FONT]frmMail.LbTo.Column(1, Varitem)