Open Report with Criteria From Form

[magmike]

I have a form, a button, a listbox and a report ran by a query.

I'm trying to get it to where the user can select one or more choices
from the listbox (including the choice "All"), press the button and
the report opens showing information where the query field
[Deductions] matches any of the choices in the form's listbox [ded].
Not advanced enough to write the code for this.

Also, if possible, I would like to give the user the option of forcing
a new page for each group ([location] - the report is already set to
do this), or not, using a check box called [ForceNewPage]. By default,
the Force New Page property under the group section of the report is
set to "Before Section".

Any help would be appreciated.

Thanks in advance,

magmike

 

[Allen Browne]

Several parts to this question.

Firstly, you'll need to create the form with the unbound multi-select list
box. If you are not sure how to get the "All" into its RowSource, see:
http://www.mvps.org/access/forms/frm0043.htm
(Note the issue about data types: "All" is a text value, not a number, so
watch the data type of the list box's Bound Column.)

Next, you need code to loop through the ItemsSelected to create the
WhereCondition string. Here's more info about that:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html
You will need to modify that code so that if the value "All" is found as you
loop through the items selected, you abandon the creation of the filter
string.

Finally, you need to put code into the Open event of the report to:
a) see if the form is open, and if so,
b) see if the box is checked.
Set the ForceNewPage property of the appropriate Section to 0 (none) or 1
(before section.) Example:
Me.Section("GroupHeader0").ForceNewPage = CByte(1)

Alternatively, you can put a page break control in the desired section, and
toggle its Visible property in Report_Open.

 

[Duane Hookom]

I have a standard function in all of my apps.
Function BuildIn(lboListBox As ListBox, _
strField As String, strDelimiter As String) _
As String
'send in a list box control object
Dim strIn As String
Dim varItem As Variant

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " [" & strField & "] In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelimiter & lboListBox.ItemData(varItem) &
strDelimiter & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn
End Function

You would call it in your code that opens your report like:
Dim strWhere as String
strWhere = "1=1 "
strWhere = strWhere & " AND " & BuildIn(Me.ded, "Deductions", "'")
DoCmd.OpenReport "rptYourName", acPrintPreview, , strWhere

This assumes Deductions is a text string. If not change the last argument in
the call to:
BuildIn(Me.ded, "Deductions", "")
If no deductions are selected then all deductions will be included.

I generally use a page break control at the bottom of a section. Then in the
On Format event of the section I have code like:
Me.pgbrkctlName.Visible = (Forms!frmName.ForceNewPage = True)

 

[magmike]

I have a standard function in all of my apps.
Function BuildIn(lboListBox As ListBox, _
        strField As String, strDelimiter As String) _
        As String
    'send in a list box control object
    Dim strIn As String
    Dim varItem As Variant

    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " [" & strField & "] In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelimiter & lboListBox.ItemData(varItem) &
strDelimiter & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn
End Function

You would call it in your code that opens your report like:
  Dim strWhere as String
  strWhere = "1=1 "
  strWhere = strWhere & " AND " & BuildIn(Me.ded, "Deductions", "'")
  DoCmd.OpenReport "rptYourName", acPrintPreview, , strWhere

This assumes Deductions is a text string. If not change the last argumentin
the call to:
     BuildIn(Me.ded, "Deductions", "")
If no deductions are selected then all deductions will be included.

I generally use a page break control at the bottom of a section. Then in the
On Format event of the section I have code like:
  Me.pgbrkctlName.Visible = (Forms!frmName.ForceNewPage = True)

--
Duane Hookom
Microsoft Access MVP

I have a form, a button, a listbox and a report ran by a query.

I'm trying to get it to where the user can select one or more choices
from the listbox (including the choice "All"), press the button and
the report opens showing information where the query field
[Deductions] matches any of the choices in the form's listbox [ded].
Not advanced enough to write the code for this.

Also, if possible, I would like to give the user the option of forcing
a new page for each group ([location] - the report is already set to
do this), or not, using a check box called [ForceNewPage]. By default,
the Force New Page property under the group section of the report is
set to "Before Section".

Any help would be appreciated.

Thanks in advance,

magmike
.- Hide quoted text -

- Show quoted text -

Is the function in a module, or within the form?

 

[magmike]

Next, you need code to loop through the ItemsSelected to create the

WhereCondition string. Here's more info about that:
    Use a multi-select list box to filter a report
at:  http://allenbrowne.com/ser-50.html

My Value List only has 1 column. Will I need to modify your code? I
have it working, but it is super slow. Should it be? The query I am
using only produces 688 results.

 

[Duane Hookom]

Since it is for use for any multiselect list box, I save it in a standard
module named "modControlCode"


--
Duane Hookom
MS Access MVP

I have a standard function in all of my apps.
Function BuildIn(lboListBox As ListBox, _
strField As String, strDelimiter As String) _
As String
'send in a list box control object
Dim strIn As String
Dim varItem As Variant

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " [" & strField & "] In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelimiter & lboListBox.ItemData(varItem) &
strDelimiter & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn
End Function

You would call it in your code that opens your report like:
Dim strWhere as String
strWhere = "1=1 "
strWhere = strWhere & " AND " & BuildIn(Me.ded, "Deductions", "'")
DoCmd.OpenReport "rptYourName", acPrintPreview, , strWhere

This assumes Deductions is a text string. If not change the last argument
in
the call to:
BuildIn(Me.ded, "Deductions", "")
If no deductions are selected then all deductions will be included.

I generally use a page break control at the bottom of a section. Then in
the
On Format event of the section I have code like:
Me.pgbrkctlName.Visible = (Forms!frmName.ForceNewPage = True)

--
Duane Hookom
Microsoft Access MVP

I have a form, a button, a listbox and a report ran by a query.

I'm trying to get it to where the user can select one or more choices
from the listbox (including the choice "All"), press the button and
the report opens showing information where the query field
[Deductions] matches any of the choices in the form's listbox [ded].
Not advanced enough to write the code for this.

Also, if possible, I would like to give the user the option of forcing
a new page for each group ([location] - the report is already set to
do this), or not, using a check box called [ForceNewPage]. By default,
the Force New Page property under the group section of the report is
set to "Before Section".

Any help would be appreciated.

Thanks in advance,

magmike
.- Hide quoted text -

- Show quoted text -

Is the function in a module, or within the form?

 

[Allen Browne]

Using IN () in the WhereCondition is much more efficient than other
approaches, so you will need to trace where the slowness is coming from.

As for debugging, I have no idea how many results you should be seeing and
why. Perhaps you have other criteria, or perhaps you've excluded the nulls,
or perhaps data types are mismatched, or perhaps the inner joins are not
working as you expect, or perhaps ...