Multi select list box & a query

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

Guest

Hi,
I'm new and I don't understand the MS Access Help Info that is provided for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which then a report is based on. Now I would like to change the list box to Multi Select. I have done that properly I think and now I need help to set it up so the query will read and reflect my selections
Form Name where the list box is: FRM-Areas
List Box Name: LBX-Departments
Query Name: QRY-Departments
Thanks
Kemper
 
You will need to provide the information via code to filter the report's
recordsource query. Something like this, perhaps:

Dim strFilter As String
Dim lngLoop As Long
strFilter = ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>



Kemper said:
Hi,
I'm new and I don't understand the MS Access Help Info that is provided
Click to expand...
for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which then a
Click to expand...
report is based on. Now I would like to change the list box to Multi Select.
I have done that properly I think and now I need help to set it up so the
query will read and reflect my selections
 
Sorry - typo in the code:

Dim strRptFilterAs String
Dim lngLoop As Long
strRptFilter= ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
You will need to provide the information via code to filter the report's
recordsource query. Something like this, perhaps:

Dim strFilter As String
Dim lngLoop As Long
strFilter = ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>



Kemper said:
Hi,
I'm new and I don't understand the MS Access Help Info that is provided
Click to expand...
for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which then a
Click to expand...
report is based on. Now I would like to change the list box to Multi Select.
I have done that properly I think and now I need help to set it up so the
query will read and reflect my selections
Form Name where the list box is: FRM-Areas
List Box Name: LBX-Departments
Query Name: QRY-Departments
Thanks
Kemper
Click to expand...
Click to expand...
 
Hey Ken,
SUCCESS. I got it to print only what I selected. Thank you very much.
I just have one little thing to ask Ken, could I go to Print Preview rather than going directly to Print and if so what would I need to change?
Thanks again, I have been needing to set this up for a very long time.
Kemper

Ken Snell said:
Sorry - typo in the code:

Dim strRptFilterAs String
Dim lngLoop As Long
strRptFilter= ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
You will need to provide the information via code to filter the report's
recordsource query. Something like this, perhaps:

Dim strFilter As String
Dim lngLoop As Long
strFilter = ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>



Kemper said:
Hi,
I'm new and I don't understand the MS Access Help Info that is provided
Click to expand...
for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which then a
Click to expand...
report is based on. Now I would like to change the list box to Multi Select.
I have done that properly I think and now I need help to set it up so the
query will read and reflect my selections
Form Name where the list box is: FRM-Areas
List Box Name: LBX-Departments
Query Name: QRY-Departments
Thanks
Kemper
Click to expand...
Click to expand...
Click to expand...
 
Sure - change the DoCmd.OpenReport line to this:

DoCmd.OpenReport "ReportName", acViewPreview, , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

Kemper said:
Hey Ken,
SUCCESS. I got it to print only what I selected. Thank you very much.
I just have one little thing to ask Ken, could I go to Print Preview
Click to expand...
rather than going directly to Print and if so what would I need to change?
Thanks again, I have been needing to set this up for a very long time.
Kemper

Ken Snell said:
Sorry - typo in the code:

Dim strRptFilterAs String
Dim lngLoop As Long
strRptFilter= ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
You will need to provide the information via code to filter the report's
recordsource query. Something like this, perhaps:

Dim strFilter As String
Dim lngLoop As Long
strFilter = ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>



Hi,
I'm new and I don't understand the MS Access Help Info that is provided
for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which then a
report is based on. Now I would like to change the list box to Multi Select.
I have done that properly I think and now I need help to set it up so the
query will read and reflect my selections
Form Name where the list box is: FRM-Areas
List Box Name: LBX-Departments
Query Name: QRY-Departments
Thanks
Kemper
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ken
If I have an addition question regaurding this same area in a couple of weeks or so would you find it if I used this exact transcript so I don't have to do to much explaining, or would I just ask a new question?
Thanks Again
Kemper

Ken Snell said:
Sure - change the DoCmd.OpenReport line to this:

DoCmd.OpenReport "ReportName", acViewPreview, , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

Kemper said:
Hey Ken,
SUCCESS. I got it to print only what I selected. Thank you very much.
I just have one little thing to ask Ken, could I go to Print Preview
Click to expand...
rather than going directly to Print and if so what would I need to change?
Thanks again, I have been needing to set this up for a very long time.
Kemper

Ken Snell said:
Sorry - typo in the code:

Dim strRptFilterAs String
Dim lngLoop As Long
strRptFilter= ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

You will need to provide the information via code to filter the report's
recordsource query. Something like this, perhaps:

Dim strFilter As String
Dim lngLoop As Long
strFilter = ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>



Hi,
I'm new and I don't understand the MS Access Help Info that is provided
for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which then a
report is based on. Now I would like to change the list box to Multi
Select.
I have done that properly I think and now I need help to set it up so the
query will read and reflect my selections
Form Name where the list box is: FRM-Areas
List Box Name: LBX-Departments
Query Name: QRY-Departments
Thanks
Kemper
Click to expand...
Click to expand...
Click to expand...
 
I typically monitor a thread for about 30 days or so, so you can reply back
to this one if you wish.

--

Ken Snell
<MS ACCESS MVP>

Kemper said:
Thanks Ken
If I have an addition question regaurding this same area in a couple of
Click to expand...
weeks or so would you find it if I used this exact transcript so I don't
have to do to much explaining, or would I just ask a new question?
Thanks Again
Kemper

Ken Snell said:
Sure - change the DoCmd.OpenReport line to this:

DoCmd.OpenReport "ReportName", acViewPreview, , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

Kemper said:
Hey Ken,
SUCCESS. I got it to print only what I selected. Thank you very much.
I just have one little thing to ask Ken, could I go to Print Preview
Click to expand...
rather than going directly to Print and if so what would I need to change?
Thanks again, I have been needing to set this up for a very long time.
Kemper

:

Sorry - typo in the code:

Dim strRptFilterAs String
Dim lngLoop As Long
strRptFilter= ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

You will need to provide the information via code to filter the report's
recordsource query. Something like this, perhaps:

Dim strFilter As String
Dim lngLoop As Long
strFilter = ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>



Hi,
I'm new and I don't understand the MS Access Help Info that is provided
for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which
Click to expand...
then
a
report is based on. Now I would like to change the list box to Multi
Select.
I have done that properly I think and now I need help to set it up
Click to expand...
so
the
query will read and reflect my selections
Form Name where the list box is: FRM-Areas
List Box Name: LBX-Departments
Query Name: QRY-Departments
Thanks
Kemper
Click to expand...
Click to expand...
Click to expand...
 
How do I apply to this filter a query?
Thanks



Ken Snell said:
Sorry - typo in the code:

Dim strRptFilterAs String
Dim lngLoop As Long
strRptFilter= ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>

Ken Snell said:
You will need to provide the information via code to filter the report's
recordsource query. Something like this, perhaps:

Dim strFilter As String
Dim lngLoop As Long
strFilter = ""
For lngLoop = 0 - [LBX-Departments].ColumnHeads To
[LBX-Departments].ListCount - 1
If [LBX-Departments].Selected(lngLoop) = True Then _
strRptFilter = strRptFilter & "FieldName=" & _
[LBX-Departments].ItemData(lngLoop) & " Or "
Next lngLoop
If Len(strRptFilter) > 4 Then strRptFilter = Left(strRptFilter,
Len(strRptFilter) - 4)
DoCmd.OpenReport "ReportName", , , strRptFilter

--

Ken Snell
<MS ACCESS MVP>



Kemper said:
Hi,
I'm new and I don't understand the MS Access Help Info that is provided
Click to expand...
for this setup. I don't know a lot about Visual Basics.
An unbound list box selects an item for a query to filter which then a
Click to expand...
report is based on. Now I would like to change the list box to Multi Select.
I have done that properly I think and now I need help to set it up so the
query will read and reflect my selections
Form Name where the list box is: FRM-Areas
List Box Name: LBX-Departments
Query Name: QRY-Departments
Thanks
Kemper
Click to expand...
Click to expand...
Click to expand...
 
Back
Top