Generate report based on list box selections

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Win xp and Access 2003 - I have a report called churchreport based on a query
called churchquery (which is two tables joined). I have list box on a form
called countryselectform with the list box called lista2. In my report I
have a grouped field called districtcountry. The user is going to select
multiple countries in the list box and I want the report to only display
churches that are in those countries from the list box.

I have the following code when I open the report.

Private Sub Report_Open(Cancel As Integer)

Me.Filter = districtcountry = "& [Forms]![countryselectform]![Lista2]"
Me.FilterOn = True

End Sub

When I click my command button on the form to preview this report, I get an
error that says false and asks for input. Not sure where I am going wrong.
 
You have to build a string based upon the selections in the listbox and use
that string in an IN clause for the recordsource of the report. Here's an
example which sets the recordsource of a subform, but the idea is the same:

Private Sub lstElevation_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "SELECT Blah, Blah2 FROM Mytable WHERE MyTable.lID) In (" &
Me.txtSelected & ");"

Me.subItems.Form.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

BLTibbs said:
Win xp and Access 2003 - I have a report called churchreport based on a query
called churchquery (which is two tables joined). I have list box on a form
called countryselectform with the list box called lista2. In my report I
have a grouped field called districtcountry. The user is going to select
multiple countries in the list box and I want the report to only display
churches that are in those countries from the list box.

I have the following code when I open the report.

Private Sub Report_Open(Cancel As Integer)

Me.Filter = districtcountry = "& [Forms]![countryselectform]![Lista2]"
Me.FilterOn = True

End Sub

When I click my command button on the form to preview this report, I get an
error that says false and asks for input. Not sure where I am going
Click to expand...
wrong.
 
Forgive my ignorance once again, but what does your comment, "an IN clause
for the recordsource" mean? where is the IN clause

Brian

Arvin Meyer said:
You have to build a string based upon the selections in the listbox and use
that string in an IN clause for the recordsource of the report. Here's an
example which sets the recordsource of a subform, but the idea is the same:

Private Sub lstElevation_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "SELECT Blah, Blah2 FROM Mytable WHERE MyTable.lID) In (" &
Me.txtSelected & ");"

Me.subItems.Form.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

BLTibbs said:
Win xp and Access 2003 - I have a report called churchreport based on a query
called churchquery (which is two tables joined). I have list box on a form
called countryselectform with the list box called lista2. In my report I
have a grouped field called districtcountry. The user is going to select
multiple countries in the list box and I want the report to only display
churches that are in those countries from the list box.

I have the following code when I open the report.

Private Sub Report_Open(Cancel As Integer)

Me.Filter = districtcountry = "& [Forms]![countryselectform]![Lista2]"
Me.FilterOn = True

End Sub

When I click my command button on the form to preview this report, I get an
error that says false and asks for input. Not sure where I am going
Click to expand...
wrong.
Click to expand...
 
It's illustrated in Arvin's example.

Take a look at what he assigns to the variable strSQL:

strSQL = "SELECT Blah, Blah2 FROM Mytable WHERE MyTable.lID) In (" &
Me.txtSelected & ");"

Rather than saying "... WHERE MyTable.IId = " &
[Forms]![countryselectform]![Lista2], which doesn't work for a multiselect
list box, he's building a list of all of the selected values in the listbox,
and using an IN clause.

By the same token, you could use his code in conjunction with a filter:

Private Sub Report_Open(Cancel As Integer)

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
strList = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
End If
End With
Me.Filter = "districtcountry IN ("& strList & ")"
Me.FilterOn = True

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BLTibbs said:
Forgive my ignorance once again, but what does your comment, "an IN clause
for the recordsource" mean? where is the IN clause

Brian

Arvin Meyer said:
You have to build a string based upon the selections in the listbox and use
that string in an IN clause for the recordsource of the report. Here's an
example which sets the recordsource of a subform, but the idea is the same:

Private Sub lstElevation_Click()

Dim varItem As Variant
Dim strList As String
Dim strSQL As String

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

strSQL = "SELECT Blah, Blah2 FROM Mytable WHERE MyTable.lID) In (" &
Me.txtSelected & ");"

Me.subItems.Form.RecordSource = strSQL

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

BLTibbs said:
Win xp and Access 2003 - I have a report called churchreport based on
Click to expand...
a
query
called churchquery (which is two tables joined). I have list box on a form
called countryselectform with the list box called lista2. In my report I
have a grouped field called districtcountry. The user is going to select
multiple countries in the list box and I want the report to only display
churches that are in those countries from the list box.

I have the following code when I open the report.

Private Sub Report_Open(Cancel As Integer)

Me.Filter = districtcountry = "& [Forms]![countryselectform]![Lista2]"
Me.FilterOn = True

End Sub

When I click my command button on the form to preview this report, I
Click to expand...
get
an
error that says false and asks for input. Not sure where I am going
Click to expand...
wrong.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List Box to create a Report based on crosstab query 6
Filtering subreports on main report 0
Combo Box to select a record and populate fields in a form 2
Access Access Report Works on One Machine but not Another 3
Create filters for report based on list box 3
default is chosen or highlighted in the list box 3
Trouble generating Filter for emailing report... 2
Report of the contents of a List Box on a Form 4

Back
Top