Multi-select list box

[access7]

Good Morning all

Quick question; I have a simple form consisting of one list box (2 columns; UserName | UserID).
I then have a text box which as the user clicks on the UserName will be populated by the corresponding UserID.
I have this code on the list box

Private Sub LstUserName_Click()
Me.txtUserID = Me.LstUserName.Column(1)
End Sub

It is working perfectly by populating the text box with the User's ID number. However, I am looking for the list box to be multiselect - I do not know how to get all of the UserID numbers in the text box.

I am thinking maybe a For Each, or a SQL statement of some kind to build up a string... I am hoping someone can point me in the right direction of the best way to handle this??

Thanks

 

[John Big Booty]

Perhaps the article here will give you some pointers.

 

[access7]

Took some playing around but I've got it working... thanks!

 

[access7]

I have another question now that I am onto the next part of my plan... again, hoping someone can help. I now have the User ID numbers showing nicely in my text box, all split by commas (e.g. if there have been 3 users picked it might be something like this [4,1,2).
For the next part of my task I am writing an Insert Into statement (as follows);

Sub CreateCCDiaryAction(liAction As Integer, liUser As Integer, lbComplete As Boolean)
Dim lsSQL As String

lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_DiaryCC ( DiaryActionID, UserID, Complete ) "
lsSQL = lsSQL & " VALUES ( " & liAction & ", '" & liUser & "', " & False & ")"

CurrentDb.Execute lsSQL

End Sub

The only problem now is that it is putting all the UserID's back together and inserting the value 412 into the table whereas what I would like it to do is create a separate record for each UserID...

Can this be done???

Thanks

 

[GregRun]

First, your liUser is no longer an integer, it's a string. You need to split the string into an array and then loop through and run your query for each variable in the array.

    Dim liUserArray() As String
    Dim x As Integer

    liUserArray = Split(liUser, ",")
    For x = 0 To UBound(liUserArray)
        lsSQL = ""
        lsSQL = lsSQL & " INSERT INTO Tbl_DiaryCC ( DiaryActionID, UserID, Complete ) "
        lsSQL = lsSQL & " VALUES ( " & liAction & ", '" & liUserArray(x) & "', " & False & ")"
    Next

 

[pbaldy]

If this is the end goal, it might be a more direct approach:

http://www.baldyweb.com/MultiselectAppend.htm