Need help with SQL statement please

[access7]

Good Morning and Happy Friday

I am hoping someone may be able to point me in the right direction with the following - I hope I explain it clearly enough but if anyone needs any further information please let me know...

I am working on a diary system at the moment in which users set themselves tasks as part of a contact management system. I have been asked to include a 'cc' function so that other users may be 'copied in' to other peoples diary actions.
I have written some code that inserts the data I need into a separate table so I know which actions have been 'CC'd and who to.

The problem I am having is if the user chooses to copy in more than one user (which will happen)... I need the insert into statement to insert a new record in the table for EACH userID that has been selected... at the moment it takes the user ID's and puts them together. For example if user's 1 & 5 have been selected then it will insert '15' into the table. What I need is for it to insert one row with the information for user ID 1 and another for user ID 5.

I hope someone understands what I am trying to do and may be able to shed some light on how I go about splitting the insert into statement in this way??

Code as follows: the first procedure is how we retrieve the user IDs, the second is my insert into statement....

Dim lsSQL As StringPrivate Sub CmdOK_Click()
Dim oItem As Variant
Dim sID As Variant
Dim iCount As Integer

iCount = 0

If Me.LstUserName.ItemsSelected.Count <> 0 Then
For Each oItem In Me.LstUserName.ItemsSelected
If iCount = 0 Then
sID = sID & Me.LstUserName.ItemData(oItem)
iCount = iCount + 1
Else
sID = sID & "," & Me.LstUserName.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me.txtUserID.Value = sID

Me.Form.Visible = False

End Sub

Sub CreateCCDiaryAction(liUser As Integer, lbComplete As Boolean)
' needs working on yet - as value for User ID needs splitting into separate records for each user...
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_DiaryCC ( DiaryActionID, CompanyRef, UserID, Complete ) "
lsSQL = lsSQL & " VALUES ( " & Forms!SubFrm_CCUser!txtDiaryID & "," & ICompanyRef & ", '" & liUser & "', " & False & ")"

CurrentDb.Execute lsSQL

DoCmd.Close acForm, "SubFrm_CCUser"

End Sub

Thank you for taking the time to read this post.

 

[VilaRestal]

Have you tried putting the CreateCCDiary sub into the loop?
(Without the DoCmd.Close line though, you don't want to do that more than once)
(or the lsSQL = "" line either, not necessary)

Something like this perhaps:

Private Sub CmdOK_Click()
    If Me.LstUserName.ItemsSelected.Count <> 0 Then
        Dim oItem As Variant
        For Each oItem In Me.LstUserName.ItemsSelected
            CreateCCDiaryAction Me.LstUserName.ItemData(oItem)
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub 'Nothing was selected
    End If
    Me.Form.Visible = False
End Sub

Sub CreateCCDiaryAction(liUser As Integer)
    Dim lsSQL As String
    lsSQL = "INSERT INTO Tbl_DiaryCC ( DiaryActionID, CompanyRef, UserID, Complete ) "
    lsSQL = lsSQL & " VALUES ( " & Forms!SubFrm_CCUser!txtDiaryID & "," & ICompanyRef & ", '" & liUser & "', 0)"
    CurrentDb.Execute lsSQL
End Sub

 

[access7]

Thanks, have done as suggested and it is working like a dream - just need to amend one of my other queries slightly now and I should be good to go.
Thank you for your time, very much appreciated!

 

[VilaRestal]

My hundredth thanks
Thank you for that