Multiple Listboxes

[Todd]

I have three multiselect listboxes.
I have coded them in VBA to pull the selections and then open a report (that
has an underlying query) using the WHERE property in the openreport event.
One problem was that my WHERE property has "OR" between each code for the
selected results of each listbox. However, the user may not choose a
selection from every listbox, which ruins the openreport event by resulting
in for ex.
OrgID In (34,343,67) OR OR.
So, I elected to populate 3 invisible text boxes with the results from the
selections of each listbox. I then built an expression in each of the
criteria of the fields in the underlying query for the report to pull the
values from the textboxes on the form when the report opened.
Example: The textbox holds 547,427,38,20 The query criteria for the OrgID
field shows "In ([Forms].[MyForm].[txtOrg])"
Well, the report opens with no records. However, if I enter criteria for the
field in the query to be "In (547,427,38,20)" the report opens fine with the
correct corresponding records. Also, if I only make one selection when the
query criteria set as "In ([Forms].[MyForm].[txtOrg])" the report opens
fine. It is just when I make multiple selections from a single listbox that
the report opens with nothing.
Any ideas? Greatly appreciated.
Todd

 

[Dale Fye]

Todd,

I usually do this in code, probably similiar to your code. I also have a
function I use to get the values from the listbox (see below).

So my code, in the click event of a command button might look something like:

Private Sub cmd_Report_Click

Dim varList1 as variant, varList2 as Variant, varList3 as variant
Dim varCriteria as variant

'If no items are selected in the listbox, the function will return a
NULL value
'By using the '+' to concatenate values, varList1 will be NULL if the
function
'returned a NULL
varList1 = "IN(" + fnMultiSelect(me.lst_1) + ")"
varList2 = "IN(" + fnMultiSelect(me.lst_2) + ")"
varList3 = "IN(" + fnMultiSelect(me.lst_3) + ")"

'By using the '+' to concatenate the "OR" value, you only include the "OR"
'if there is already some information in varCriteria
varCriteria = varList1
If not isnull(varList2) then varCriteria = (varCriteria + " OR ") &
varList2
If not isnull(varList3) then varCriteria = (varCriteria + " OR ") &
varList3

docmd.OpenReport "report_Name", acViewPreview, , varCriteria

End sub

Public Function fnMultiSelect(ctrl As ListBox, Optional varCol As Variant =
Null) As Variant

'This function requires a listbox control as the argument and optionally
'accepts the column (0 based) to pull the data from. The default column
'is the bound column for the list that is passed.

'It returns a list of items, separated by commas, and wraps text values
in quotes

Dim varMultiSelect As Variant
Dim varItem As Variant
Dim varWrapper As Variant

varMultiSelect = Null

'If varCol is not passed, assume that the column of interest is the bound
column
If IsNull(varCol) Then varCol = ctrl.BoundColumn - 1

For Each varItem In ctrl.ItemsSelected

If IsNumeric(ctrl.Column(varCol, varItem)) Then
varMultiSelect = (varMultiSelect + ",") & ctrl.Column(varCol,
varItem)
Else
varMultiSelect = (varMultiSelect + ",") & Chr$(34) &
ctrl.Column(varCol, varItem) & Chr$(34)
End If

Next

fnMultiSelect = varMultiSelect

End Function

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I have three multiselect listboxes.
I have coded them in VBA to pull the selections and then open a report (that
has an underlying query) using the WHERE property in the openreport event.
One problem was that my WHERE property has "OR" between each code for the
selected results of each listbox. However, the user may not choose a
selection from every listbox, which ruins the openreport event by resulting
in for ex.
OrgID In (34,343,67) OR OR.
So, I elected to populate 3 invisible text boxes with the results from the
selections of each listbox. I then built an expression in each of the
criteria of the fields in the underlying query for the report to pull the
values from the textboxes on the form when the report opened.
Example: The textbox holds 547,427,38,20 The query criteria for the OrgID
field shows "In ([Forms].[MyForm].[txtOrg])"
Well, the report opens with no records. However, if I enter criteria for the
field in the query to be "In (547,427,38,20)" the report opens fine with the
correct corresponding records. Also, if I only make one selection when the
query criteria set as "In ([Forms].[MyForm].[txtOrg])" the report opens
fine. It is just when I make multiple selections from a single listbox that
the report opens with nothing.
Any ideas? Greatly appreciated.
Todd

 

[Todd]

Thanks, works great. I didn't even think about using the '+' to concatenate
values and utilizing NULL values. I will remember next time.
Thanks again.

Todd,

I usually do this in code, probably similiar to your code. I also have a
function I use to get the values from the listbox (see below).

So my code, in the click event of a command button might look something
like:

Private Sub cmd_Report_Click

Dim varList1 as variant, varList2 as Variant, varList3 as variant
Dim varCriteria as variant

'If no items are selected in the listbox, the function will return a
NULL value
'By using the '+' to concatenate values, varList1 will be NULL if the
function
'returned a NULL
varList1 = "IN(" + fnMultiSelect(me.lst_1) + ")"
varList2 = "IN(" + fnMultiSelect(me.lst_2) + ")"
varList3 = "IN(" + fnMultiSelect(me.lst_3) + ")"

'By using the '+' to concatenate the "OR" value, you only include the
"OR"
'if there is already some information in varCriteria
varCriteria = varList1
If not isnull(varList2) then varCriteria = (varCriteria + " OR ") &
varList2
If not isnull(varList3) then varCriteria = (varCriteria + " OR ") &
varList3

docmd.OpenReport "report_Name", acViewPreview, , varCriteria

End sub

Public Function fnMultiSelect(ctrl As ListBox, Optional varCol As Variant
=
Null) As Variant

'This function requires a listbox control as the argument and optionally
'accepts the column (0 based) to pull the data from. The default column
'is the bound column for the list that is passed.

'It returns a list of items, separated by commas, and wraps text values
in quotes

Dim varMultiSelect As Variant
Dim varItem As Variant
Dim varWrapper As Variant

varMultiSelect = Null

'If varCol is not passed, assume that the column of interest is the
bound
column
If IsNull(varCol) Then varCol = ctrl.BoundColumn - 1

For Each varItem In ctrl.ItemsSelected

If IsNumeric(ctrl.Column(varCol, varItem)) Then
varMultiSelect = (varMultiSelect + ",") & ctrl.Column(varCol,
varItem)
Else
varMultiSelect = (varMultiSelect + ",") & Chr$(34) &
ctrl.Column(varCol, varItem) & Chr$(34)
End If

Next

fnMultiSelect = varMultiSelect

End Function

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I have three multiselect listboxes.
I have coded them in VBA to pull the selections and then open a report
(that
has an underlying query) using the WHERE property in the openreport
event.
One problem was that my WHERE property has "OR" between each code for the
selected results of each listbox. However, the user may not choose a
selection from every listbox, which ruins the openreport event by
resulting
in for ex.
OrgID In (34,343,67) OR OR.
So, I elected to populate 3 invisible text boxes with the results from
the
selections of each listbox. I then built an expression in each of the
criteria of the fields in the underlying query for the report to pull the
values from the textboxes on the form when the report opened.
Example: The textbox holds 547,427,38,20 The query criteria for the OrgID
field shows "In ([Forms].[MyForm].[txtOrg])"
Well, the report opens with no records. However, if I enter criteria for
the
field in the query to be "In (547,427,38,20)" the report opens fine with
the
correct corresponding records. Also, if I only make one selection when
the
query criteria set as "In ([Forms].[MyForm].[txtOrg])" the report opens
fine. It is just when I make multiple selections from a single listbox
that
the report opens with nothing.
Any ideas? Greatly appreciated.
Todd

 

[Dale Fye]

Glad I could help

--
Email address is not valid.
Please reply to newsgroup only.

Thanks, works great. I didn't even think about using the '+' to concatenate
values and utilizing NULL values. I will remember next time.
Thanks again.

Todd,

I usually do this in code, probably similiar to your code. I also have a
function I use to get the values from the listbox (see below).

So my code, in the click event of a command button might look something
like:

Private Sub cmd_Report_Click

Dim varList1 as variant, varList2 as Variant, varList3 as variant
Dim varCriteria as variant

'If no items are selected in the listbox, the function will return a
NULL value
'By using the '+' to concatenate values, varList1 will be NULL if the
function
'returned a NULL
varList1 = "IN(" + fnMultiSelect(me.lst_1) + ")"
varList2 = "IN(" + fnMultiSelect(me.lst_2) + ")"
varList3 = "IN(" + fnMultiSelect(me.lst_3) + ")"

'By using the '+' to concatenate the "OR" value, you only include the
"OR"
'if there is already some information in varCriteria
varCriteria = varList1
If not isnull(varList2) then varCriteria = (varCriteria + " OR ") &
varList2
If not isnull(varList3) then varCriteria = (varCriteria + " OR ") &
varList3

docmd.OpenReport "report_Name", acViewPreview, , varCriteria

End sub

Public Function fnMultiSelect(ctrl As ListBox, Optional varCol As Variant
=
Null) As Variant

'This function requires a listbox control as the argument and optionally
'accepts the column (0 based) to pull the data from. The default column
'is the bound column for the list that is passed.

'It returns a list of items, separated by commas, and wraps text values
in quotes

Dim varMultiSelect As Variant
Dim varItem As Variant
Dim varWrapper As Variant

varMultiSelect = Null

'If varCol is not passed, assume that the column of interest is the
bound
column
If IsNull(varCol) Then varCol = ctrl.BoundColumn - 1

For Each varItem In ctrl.ItemsSelected

If IsNumeric(ctrl.Column(varCol, varItem)) Then
varMultiSelect = (varMultiSelect + ",") & ctrl.Column(varCol,
varItem)
Else
varMultiSelect = (varMultiSelect + ",") & Chr$(34) &
ctrl.Column(varCol, varItem) & Chr$(34)
End If

Next

fnMultiSelect = varMultiSelect

End Function

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I have three multiselect listboxes.
I have coded them in VBA to pull the selections and then open a report
(that
has an underlying query) using the WHERE property in the openreport
event.
One problem was that my WHERE property has "OR" between each code for the
selected results of each listbox. However, the user may not choose a
selection from every listbox, which ruins the openreport event by
resulting
in for ex.
OrgID In (34,343,67) OR OR.
So, I elected to populate 3 invisible text boxes with the results from
the
selections of each listbox. I then built an expression in each of the
criteria of the fields in the underlying query for the report to pull the
values from the textboxes on the form when the report opened.
Example: The textbox holds 547,427,38,20 The query criteria for the OrgID
field shows "In ([Forms].[MyForm].[txtOrg])"
Well, the report opens with no records. However, if I enter criteria for
the
field in the query to be "In (547,427,38,20)" the report opens fine with
the
correct corresponding records. Also, if I only make one selection when
the
query criteria set as "In ([Forms].[MyForm].[txtOrg])" the report opens
fine. It is just when I make multiple selections from a single listbox
that
the report opens with nothing.
Any ideas? Greatly appreciated.
Todd