MultiSelect ListBox

[Rita]

Hi,
I want to use a criteria form that has a multi-select list box where a user
can select several companies and also type in a date range for several
different reports. Each report is built on a different query or queries. The
reports would not all be run at the same time. I know I have to loop through
the items in the list box. I found this sample code on Allen Browne's
website that will use a list box to select multiple categories in a report,
but the multi-select criteria form opens only one report. Is it possible to
use this in reverse? Open a report and have the report call the criteria
form?

If I can open the desired report and have it open the multi-select criteria
form I can use such a form for many reports that all have a different record
source while selecting what companies I want the report for. I apologize if
I'm not explaining this clearly.

The sample code is below is in the cmdPreview button on the criteria form
from Mr. Browne's website.

strDoc = "Products by Category" ' (my comment, this report name would not
always be the same)

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

Thank you in advance.
RT

 

[Allen Browne]

It is possible to recode this so that the report's Open event opens the form
in dialog mode. That pauses the Report_Open code until you close the form.

You then need a way to pass strWhere back to the report. So you need a
public string variable (declared in the General Declaraions section of a
standard module.) Once you close the dialog form, the Report_Open code can
read the public string variable, and set the Filter of the report.

Personally, I find it easier to work the other way. Place an option group on
your form, with an option button for each report you wish to open. In the
Tag property of each option button, put the name of the report it is
associated with. You can then get the name of the report to open like this:

For Each ctl In Me.grpReport.Controls
If ctl.ControlType = acOptionButton Then
If ctl.OptionValue = lngReport Then
strDoc = ctl.Tag
Exit For
End If
End If
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I want to use a criteria form that has a multi-select list box where a
user can select several companies and also type in a date range for
several different reports. Each report is built on a different query or
queries. The reports would not all be run at the same time. I know I have
to loop through the items in the list box. I found this sample code on
Allen Browne's website that will use a list box to select multiple
categories in a report, but the multi-select criteria form opens only one
report. Is it possible to use this in reverse? Open a report and have the
report call the criteria form?

If I can open the desired report and have it open the multi-select
criteria form I can use such a form for many reports that all have a
different record source while selecting what companies I want the report
for. I apologize if I'm not explaining this clearly.

The sample code is below is in the cmdPreview button on the criteria form
from Mr. Browne's website.

strDoc = "Products by Category" ' (my comment, this report name would
not always be the same)

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

Thank you in advance.
RT

 

[Rita]

Allen,
Thanks, but it is the company or companies I want to select first. Would I
then have two option groups? One
to first select the company or companies, and the second to select the
report I want to view?

Thank you.
Rita

It is possible to recode this so that the report's Open event opens the
form in dialog mode. That pauses the Report_Open code until you close the
form.

You then need a way to pass strWhere back to the report. So you need a
public string variable (declared in the General Declaraions section of a
standard module.) Once you close the dialog form, the Report_Open code can
read the public string variable, and set the Filter of the report.

Personally, I find it easier to work the other way. Place an option group
on your form, with an option button for each report you wish to open. In
the Tag property of each option button, put the name of the report it is
associated with. You can then get the name of the report to open like
this:

For Each ctl In Me.grpReport.Controls
If ctl.ControlType = acOptionButton Then
If ctl.OptionValue = lngReport Then
strDoc = ctl.Tag
Exit For
End If
End If
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I want to use a criteria form that has a multi-select list box where a
user can select several companies and also type in a date range for
several different reports. Each report is built on a different query or
queries. The reports would not all be run at the same time. I know I have
to loop through the items in the list box. I found this sample code on
Allen Browne's website that will use a list box to select multiple
categories in a report, but the multi-select criteria form opens only one
report. Is it possible to use this in reverse? Open a report and have the
report call the criteria form?

If I can open the desired report and have it open the multi-select
criteria form I can use such a form for many reports that all have a
different record source while selecting what companies I want the report
for. I apologize if I'm not explaining this clearly.

The sample code is below is in the cmdPreview button on the criteria form
from Mr. Browne's website.

strDoc = "Products by Category" ' (my comment, this report name would
not always be the same)

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

Thank you in advance.
RT

 

[Allen Browne]

You cannot use the Open event of the report to choose the report: the report
has already been chosen at that point.

If you want to be able to select multiple companies, use a multi-select list
box to choose the company. Details in:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks, but it is the company or companies I want to select first. Would
I then have two option groups? One
to first select the company or companies, and the second to select the
report I want to view?

Thank you.
Rita

It is possible to recode this so that the report's Open event opens the
form in dialog mode. That pauses the Report_Open code until you close the
form.

You then need a way to pass strWhere back to the report. So you need a
public string variable (declared in the General Declaraions section of a
standard module.) Once you close the dialog form, the Report_Open code
can read the public string variable, and set the Filter of the report.

Personally, I find it easier to work the other way. Place an option group
on your form, with an option button for each report you wish to open. In
the Tag property of each option button, put the name of the report it is
associated with. You can then get the name of the report to open like
this:

For Each ctl In Me.grpReport.Controls
If ctl.ControlType = acOptionButton Then
If ctl.OptionValue = lngReport Then
strDoc = ctl.Tag
Exit For
End If
End If
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I want to use a criteria form that has a multi-select list box where a
user can select several companies and also type in a date range for
several different reports. Each report is built on a different query or
queries. The reports would not all be run at the same time. I know I
have to loop through the items in the list box. I found this sample code
on Allen Browne's website that will use a list box to select multiple
categories in a report, but the multi-select criteria form opens only
one report. Is it possible to use this in reverse? Open a report and
have the report call the criteria form?

If I can open the desired report and have it open the multi-select
criteria form I can use such a form for many reports that all have a
different record source while selecting what companies I want the report
for. I apologize if I'm not explaining this clearly.

The sample code is below is in the cmdPreview button on the criteria
form from Mr. Browne's website.

strDoc = "Products by Category" ' (my comment, this report name would
not always be the same)

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

Thank you in advance.
RT

 

[Rita]

I am turned around and back where I began with my first post but I reread
your previous answer re the option group and I think I misunderstood what
you meant. I'll read all I can on option groups and retry.

Thank you.

You cannot use the Open event of the report to choose the report: the
report has already been chosen at that point.

If you want to be able to select multiple companies, use a multi-select
list box to choose the company. Details in:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
Thanks, but it is the company or companies I want to select first. Would
I then have two option groups? One
to first select the company or companies, and the second to select the
report I want to view?

Thank you.
Rita

It is possible to recode this so that the report's Open event opens the
form in dialog mode. That pauses the Report_Open code until you close
the form.

You then need a way to pass strWhere back to the report. So you need a
public string variable (declared in the General Declaraions section of a
standard module.) Once you close the dialog form, the Report_Open code
can read the public string variable, and set the Filter of the report.

Personally, I find it easier to work the other way. Place an option
group on your form, with an option button for each report you wish to
open. In the Tag property of each option button, put the name of the
report it is associated with. You can then get the name of the report to
open like this:

For Each ctl In Me.grpReport.Controls
If ctl.ControlType = acOptionButton Then
If ctl.OptionValue = lngReport Then
strDoc = ctl.Tag
Exit For
End If
End If
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I want to use a criteria form that has a multi-select list box where a
user can select several companies and also type in a date range for
several different reports. Each report is built on a different query or
queries. The reports would not all be run at the same time. I know I
have to loop through the items in the list box. I found this sample
code on Allen Browne's website that will use a list box to select
multiple categories in a report, but the multi-select criteria form
opens only one report. Is it possible to use this in reverse? Open a
report and have the report call the criteria form?

If I can open the desired report and have it open the multi-select
criteria form I can use such a form for many reports that all have a
different record source while selecting what companies I want the
report for. I apologize if I'm not explaining this clearly.

The sample code is below is in the cmdPreview button on the criteria
form from Mr. Browne's website.

strDoc = "Products by Category" ' (my comment, this report name would
not always be the same)

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """,
"
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[CategoryID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

Thank you in advance.
RT