multi-select list box into a query, have code

[finneyz]

I am in need of some expertise..
I have the code for the report .. having difficulty pushing it into a query
If possible can someone help me correct my code so it will then push it into the qryallemployees?


Option Compare Database
Option Explicit

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If

'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptEmployees", acPreview, , "EmpID IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub






Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

Dim stDocName As String

stDocName = "rptEmployees"
DoCmd.OpenReport stDocName, acViewReport

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Resume Exit_Command4_Click

End Sub





Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

'Dim stDocName As String

'stDocName = "qryAllEmployees"
'DoCmd.OpenQuery stDocName, acNormal, acEdit


Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 employee"
Exit Sub
End If

'add selected values to string
Set ctl = Me.lstEmployees
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
' DoCmd.OpenQuery strWhere, acNormal, acEdit
DoCmd.OpenQuery qryAllEmployees

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

 

[apr pillai]

Change the following line:

strWhere = strWhere & ctl.ItemData(varItem) & ","

to read as

strWhere = strWhere & ctl.ItemData([B]0[/B],varItem) & ","

varItem must be a Variant type (it is correctly defined).

0 - return value from the first column
1 - second column

check this link for details: Selected Listbox Items and Dynamic Query

 

[finneyz]

thanks for the update APR, But unfortunately . It is giving me an error at that line..
Again, just trying to use the run query button for a print.
just as the report button works.

error is compile error wrong number of arguments or invalid property assignment.


attached is the zip file.

I appreciate your assistance.

 

[apr pillai]

I am sorry, there was a correction in the solution which I have posted above. The following statement:

ctl.ItemData(0,varItem) & ","

should read as

ctl.Column(0,varItem) & ","

But, I have modified your programs and both report and query is working now. Modified database is attached.

 

[finneyz]

Ah thank you.. Working on learning sql.. Much much appreciated.
Will make life useful!. And save me some hunting on the web
have a super duper day.
I hope others in the future will make use of it.
I am using baldy in my reports, whomever wrote it.. I thank them.