Multi Select List Box

[J-F]

How do I get the selections from a multi select box into a string seperated by a space?
ie "2 6 8 13"

Thanks in advance.

J-F

 

[raskew]

You'll need a command button which you'll click when you're satisfied with your selections. Code for the OnClick event could begin something like this:

Private Sub Command4_Click()
Dim Q As QueryDef, db As Database
Dim Criteria As String, strSQL as String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![List0]

'the following routine builds a comma-delimited string of items selected, e.g., if SSN is the bound column,
'Criteria might end up looking like this: "122321312, 333333333, 333333332"

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = ctl.ItemData(Itm)
Else
Criteria = Criteria & ", " & ctl.ItemData(Itm)
End If
Next Itm

 

[J-F]

Thanks a lot. it works like a dream.

What I want to do now is have criteria copied to the clipboard? What I have done, which works fine, is make an invisible TxtBox.Value as criteria and copy that. Like this:

'=====================================
'SELECT MULTIPLE PCS AND COPY TO PASTE
'=====================================

Private Sub copyPCs_Click()
Dim Criteria As String
Dim Itm As Variant

For Each Itm In Me.Officers.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Me.Officers.ItemData(Itm)
Else
Criteria = Criteria & " " & Me.Officers.ItemData(Itm)
End If
Next Itm
copyPCsTxt.Value = Criteria
DoCmd.GoToControl "copyPCsTxt"
DoCmd.RunCommand acCmdCopy

End Sub


Is there a way of saving criteria straight to the clipboard?

 

[raskew]

If what you've got now works, and it looks like it should, suggest you let it go at that.

Doing anything tricky with copy to clipboard leads into API calls and a lot of code and, in my opinion, is probably not worth the effort.

 

[Fornatian]

I would agree with Raskew on initiating API calls etc.. but wonder what you do with the item once it is in memory? If it is used in Access or another VBA compatible environment then you may be able to bypass the copying by directly referencing the variable.
E.g if you wanted to paste the variable value into an excel spreadsheet somewhere.

Ian