MultiSelect Listbox

[mohammadagul]

how to use this code in a multiselect listbox to pass paramete toa report

dim frm as form, ctl as control
dim caritem as vairant
dim strsql as sting
set frm = form!frmMyForm
setctl = frm!lbMultiselectListbox
strSQL = "Select * from Employees where [EmpID] = "
for each varitem in ctl.itemselected
strsql = strsql & ctl.itemdata(varitem) & " OR [EmpId] ="
next caritem

strsql = left$(strSQL, len(strSQL)-12))

 

[Mile-O]

I gave you a link to this thread at the start of August here.

All you had to do was change the object names as the code works fine.

 

[Hayley Baxter]

I imagine you are placing the code behind a command button on a form and then stating the name of the report in which to return the selected values in the listbox.

Private Sub cmdPrintPreview_Click()
Dim vItm As Variant
Dim stWhat As String
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef

stWhat = "": stCriteria = ","
For Each vItm In Me!lstSelectGrant.ItemsSelected
stWhat = stWhat & Me!lstSelectGrant.ItemData(vItm)
stWhat = stWhat & stCriteria
Next vItm
If IsNull(lstSelectGrant.Value) Then
MsgBox "Please select an entry"
Me!txtCriteria = Null
End If
Me!txtCriteria = CStr(Left$(stWhat, Len(stWhat) - Len(stCriteria)))
Set loqd = CurrentDb.QueryDefs("qryMultiSelect")
stSQL = "SELECT * "
stSQL = stSQL & "FROM qryProjectTitle WHERE ProjectID"
stSQL = stSQL & " IN (" & Me!txtCriteria & ")"
loqd.SQL = stSQL
loqd.Close
DoCmd.OpenReport "rptByGrant", acViewPreview
End Sub

I use the above code for this very purpose. You may notice that there is a textbox on the form called txtcriteria, all this does is allows the user to view the item(s) they have chosen in the listbox prior to returning the results in the report to allow them to see if they have chosen the correct items and give them time to deselect anything.

You may find it useful to include this to ensure you are returning the correct values.

HTH
Hay

 

[Mile-O]

Dim loqd As QueryDef

DAO.QueryDef

 

[Mile-O]

You might find it a better idea to make a Class out of this so that you can just use that anytime you want by passing the required object names into the Class.

 

[Hayley Baxter]

DAO.QueryDef

Aye Aye captain