Multi Select Report Problem

[aamer]

I am having a problem since I changed a combo box to a list box I am getting the blank result on the report. I know it’s a problem with coding, but unfortunately I could not figure it out.
Help will be appreciated.

I have attached SAMPLE DB for better understanding the problem. The FORM 1 is the form I was previously using and is working perfect.With this I select the account title, single item description and date range and I get the perfect report without any issue.

FORM 2 is the one I want to use now, as with this I cannot get the report of multiple items of an account title within specified date range. This is the one I am stuck at.

Code used on Form 1


Private Sub cmdOpenReportSingle_Click()
On Error GoTo Err_Handler



Const REPORTNAME = "Form1Report"
Const MESSAGETEXT = "Both a start and end date must be selected."
Dim strCriteria As String
Dim strDateFrom As String, strDateTo As String

' make sure a customer is selected
If Not IsNull(Me.cboDateFrom) And Not IsNull(Me.cboDateTo) Then
strDateFrom = "#" & Format(Me.cboDateFrom, "yyyy-mm-dd") & "#"
strDateTo = "#" & Format(DateAdd("d", 1, Me.cboDateTo), "yyyy-mm-dd") & "#"
' build string expression to filter report
' to selected data range
strCriteria = "PurDate >= " & strDateFrom & " And PurDate < " & strDateTo

' open report filtered to selected customer
DoCmd.OpenReport REPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub




SAMPLE DATABASE ATTACHED

Multi Select Sample DB.zip

[ranman256]

This is not the way to do it. The better way is to use the list box to add items to a 'picked' table. All the items in the picked table join to your source data to pull only those items.

no code, only queries.

[aamer]

how to do that can u guide

[aamer]

Take a look at this new sample I have attached in which FORM 3 is capable of multi select and is working.

what I presume is there is some change in code required for FORM 2 WHICH I NEED.

i.e combination of VB Code used to Generate report for Form 1 & Form 3 which are as follows:


Code used on Form 1
Private Sub cmdOpenReportSingle_Click()
On Error GoTo Err_Handler

Const REPORTNAME = "Form1Report"
Const MESSAGETEXT = "Both a start and end date must be selected."
Dim strCriteria As String
Dim strDateFrom As String, strDateTo As String

' make sure a customer is selected
If Not IsNull(Me.cboDateFrom) And Not IsNull(Me.cboDateTo) Then
strDateFrom = "#" & Format(Me.cboDateFrom, "yyyy-mm-dd") & "#"
strDateTo = "#" & Format(DateAdd("d", 1, Me.cboDateTo), "yyyy-mm-dd") & "#"
' build string expression to filter report
' to selected data range
strCriteria = "PurDate >= " & strDateFrom & " And PurDate < " & strDateTo

' open report filtered to selected customer
DoCmd.OpenReport REPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub





Code used on Form 3


Private Sub cmdOpenReportMultiple_Click()
On Error GoTo Err_Handler

Const REPORTNAME = "Form3Report"
Const MESSAGETEXT = "No Item's Selected"

Dim varItem As Variant
Dim strCustomerIDList As String
Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.CboFM3B

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strCustomerIDList = strCustomerIDList & ",""" & ctrl.ItemData(varItem) & """"
Next varItem

' remove leading comma
strCustomerIDList = Mid(strCustomerIDList, 2)

strCriteria = "[Purchase Description] In(" & strCustomerIDList & ")"

DoCmd.OpenReport REPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid operation"
End If
Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub



Multi Select Sample1 DB.zip

[ItsMe]

Why did you switch from a combobox to a listbox control? If you are using the Multiselect property as, =Yes, you will need to iterate your listbox control to get all of the values from the Selected Items. Here is some example code for multiselect listbox controls.
https://www.accessforums.net/forms/l...tml#post224893

And here is info on the ItemsSelected property of the multiselect listbox control
https://msdn.microsoft.com/en-us/lib...ffice.15).aspx

[aamer]

Thx for the link am checking it.

Why change from combo to list box.
its because lot of times we need report for multiple items with combo only one report per item

[ItsMe]

its because lot of times we need report for multiple items with combo only one report per item

So you will need to concatenate each selection onto an existing string as you iterate the listbox control.

so something like the following.

Dim strCustomerIDList as string
strCustomerIDList = ""

...

lngID = Me.List0.Column(0, varSelection)
strCustomerIDList = strCustomerIDList & lngID & ","

...

'remove trailing comma from strCustomerIDList
strCustomerIDList = Left(strCustomerIDList, Len(strCustomerIDList) - 1)
...
strCriteria = "[Purchase Description] In(" & strCustomerIDList & ")"

[ssanfu]

Might also see Allen Browne's site:

Use a multi-select list box to filter a report
http://www.allenbrowne.com/ser-50.html