filter report from listbox without having to select any

  • Thread starter Thread starter Panos
  • Start date Start date
P

Panos

I have a listbox which dynamically shows records when i enter criteria in a
texbox
using the textbox's on change event
After that i want like to be able to filter a report based on the records
that are currently displayed in the listbox without having to select any.
Just the ones i can see at the moment. I allready have the code which gives
me the ones that are selected and i need to modify it in case i dont select
anything
Code below------
Private Sub Command22_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub
 
Use the ItemData property. It is a collection of all rows in the list box:

Dim lngX as Long

For lngX = 0 to Me.List0.ListCount - 1
strWhere = strWhere & Me.List0.Column(0, lngX) & ","
Next lngX

Now, as to using the Change event of the text box. Move the code to the
after update event. The change event fires for every keystroke, so you are
doing a lot of extra work that will only slow down your app with all the
extra database hits.
 
Well the code seems to work but it outputs the total number of records
What i need is the report to show only records that are filtered in the
listbox,
(after using the on change or the afterupdate event of the text box ) and
without having to select any record.
i should have mentioned that this happens when i click a "Print" button in
the form
so i am figuring that the code will go to the on click event of this button.
Sorry maybe i didn't get something right..........

Panos--


Klatuu said:
Use the ItemData property. It is a collection of all rows in the list box:

Dim lngX as Long

For lngX = 0 to Me.List0.ListCount - 1
strWhere = strWhere & Me.List0.Column(0, lngX) & ","
Next lngX

Now, as to using the Change event of the text box. Move the code to the
after update event. The change event fires for every keystroke, so you are
doing a lot of extra work that will only slow down your app with all the
extra database hits.
--
Dave Hargis, Microsoft Access MVP


Panos said:
I have a listbox which dynamically shows records when i enter criteria in a
texbox
using the textbox's on change event
After that i want like to be able to filter a report based on the records
that are currently displayed in the listbox without having to select any.
Just the ones i can see at the moment. I allready have the code which gives
me the ones that are selected and i need to modify it in case i dont select
anything
Code below------
Private Sub Command22_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub
Click to expand...
Click to expand...
 
I guess you are going to have to better define what you want to get from the
list box. The code I posted will include all records in the list. What do
you mean by "filtered in the list box"?

Yes, the code could go in the click event of a command button.
--
Dave Hargis, Microsoft Access MVP


Panos said:
Well the code seems to work but it outputs the total number of records
What i need is the report to show only records that are filtered in the
listbox,
(after using the on change or the afterupdate event of the text box ) and
without having to select any record.
i should have mentioned that this happens when i click a "Print" button in
the form
so i am figuring that the code will go to the on click event of this button.
Sorry maybe i didn't get something right..........

Panos--


Klatuu said:
Use the ItemData property. It is a collection of all rows in the list box:

Dim lngX as Long

For lngX = 0 to Me.List0.ListCount - 1
strWhere = strWhere & Me.List0.Column(0, lngX) & ","
Next lngX

Now, as to using the Change event of the text box. Move the code to the
after update event. The change event fires for every keystroke, so you are
doing a lot of extra work that will only slow down your app with all the
extra database hits.
--
Dave Hargis, Microsoft Access MVP


Panos said:
I have a listbox which dynamically shows records when i enter criteria in a
texbox
using the textbox's on change event
After that i want like to be able to filter a report based on the records
that are currently displayed in the listbox without having to select any.
Just the ones i can see at the moment. I allready have the code which gives
me the ones that are selected and i need to modify it in case i dont select
anything
Code below------
Private Sub Command22_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub
Click to expand...
Click to expand...
Click to expand...
 
OK here's the deal
-I enter criteria in a text box
-this text box requeries a multiselect list box as i enter text using the on
change event (or the after update for better perfomance)
-after that i have certain records that are currently displayed in the list
box
i want those records to output to a report when i click the "Print" cmdbutton
So far i have it and it works just fine
The problem is that i must select the records in the listbox so that they
come up in the report
Is it possible that the print button outputs the records currently displayed
without having to select them

Need to modify code below---------

Private Sub cmdPrint_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub

Thank you for your time......
Panos

Klatuu said:
I guess you are going to have to better define what you want to get from the
list box. The code I posted will include all records in the list. What do
you mean by "filtered in the list box"?

Yes, the code could go in the click event of a command button.
--
Dave Hargis, Microsoft Access MVP


Panos said:
Well the code seems to work but it outputs the total number of records
What i need is the report to show only records that are filtered in the
listbox,
(after using the on change or the afterupdate event of the text box ) and
without having to select any record.
i should have mentioned that this happens when i click a "Print" button in
the form
so i am figuring that the code will go to the on click event of this button.
Sorry maybe i didn't get something right..........

Panos--


Klatuu said:
Use the ItemData property. It is a collection of all rows in the list box:

Dim lngX as Long

For lngX = 0 to Me.List0.ListCount - 1
strWhere = strWhere & Me.List0.Column(0, lngX) & ","
Next lngX

Now, as to using the Change event of the text box. Move the code to the
after update event. The change event fires for every keystroke, so you are
doing a lot of extra work that will only slow down your app with all the
extra database hits.
--
Dave Hargis, Microsoft Access MVP


:

I have a listbox which dynamically shows records when i enter criteria in a
texbox
using the textbox's on change event
After that i want like to be able to filter a report based on the records
that are currently displayed in the listbox without having to select any.
Just the ones i can see at the moment. I allready have the code which gives
me the ones that are selected and i need to modify it in case i dont select
anything
Code below------
Private Sub Command22_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub
Click to expand...
Click to expand...
Click to expand...
 
OK here's the deal
-I enter criteria in a text box
-this text box requeries a multiselect list box as i enter text
using the on change event (or the after update for better
perfomance) -after that i have certain records that are currently
displayed in the list box
i want those records to output to a report when i click the
"Print" cmdbutton So far i have it and it works just fine
The problem is that i must select the records in the listbox so
that they come up in the report
Is it possible that the print button outputs the records currently
displayed without having to select them

Need to modify code below---------

Private Sub cmdPrint_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub

Thank you for your time......
Panos
Click to expand...

how about
Private Sub cmdPrint_Click()
Dim strwhere As String, varitem As Variant
If Me.List0.ItemsSelected.Count = 0 Then
For varitm = 0 To Me.List0.ListCount - 1
strwhere = strwhere & Me.List0.Column(0, varitem) & ","
Next itm
Else
For Each varitem In Me.List0.ItemsSelected
strwhere = strwhere & Me.List0.Column(0, varitem) & ","
Next varitem
End If
strwhere = "[ID] IN (" & strwhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strwhere

End Sub
 
It still outputs the total number of records not just the ones currently
displayed
..........can't figure it out
Guess i'm going to have to work with selecting the records
It's not a big deal after all

Bob Quintal said:
OK here's the deal
-I enter criteria in a text box
-this text box requeries a multiselect list box as i enter text
using the on change event (or the after update for better
perfomance) -after that i have certain records that are currently
displayed in the list box
i want those records to output to a report when i click the
"Print" cmdbutton So far i have it and it works just fine
The problem is that i must select the records in the listbox so
that they come up in the report
Is it possible that the print button outputs the records currently
displayed without having to select them

Need to modify code below---------

Private Sub cmdPrint_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub

Thank you for your time......
Panos
Click to expand...

how about
Private Sub cmdPrint_Click()
Dim strwhere As String, varitem As Variant
If Me.List0.ItemsSelected.Count = 0 Then
For varitm = 0 To Me.List0.ListCount - 1
strwhere = strwhere & Me.List0.Column(0, varitem) & ","
Next itm
Else
For Each varitem In Me.List0.ItemsSelected
strwhere = strwhere & Me.List0.Column(0, varitem) & ","
Next varitem
End If
strwhere = "[ID] IN (" & strwhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strwhere

End Sub
Click to expand...
 
You are still trying to use the ItemsSelected collection. That is not
correct. In my original post, I used the ItemData collection. That includes
all rows in the list box regardless of whether they are selected or not.




Private Sub cmdPrint_Click()
Dim strWhere As String
Dim lngX as Long

For lngX = 0 to Me.List0.ListCount - 1
strWhere = strWhere & Me.List0.Column(0, lngX) & ","
Next lngX
strWhere = Left(strWhere,Len(strWhere) -1)
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub

If this doesn't work, then there is some other problem we are not seeing.

--
Dave Hargis, Microsoft Access MVP


Panos said:
It still outputs the total number of records not just the ones currently
displayed
.........can't figure it out
Guess i'm going to have to work with selecting the records
It's not a big deal after all

Bob Quintal said:
OK here's the deal
-I enter criteria in a text box
-this text box requeries a multiselect list box as i enter text
using the on change event (or the after update for better
perfomance) -after that i have certain records that are currently
displayed in the list box
i want those records to output to a report when i click the
"Print" cmdbutton So far i have it and it works just fine
The problem is that i must select the records in the listbox so
that they come up in the report
Is it possible that the print button outputs the records currently
displayed without having to select them

Need to modify code below---------

Private Sub cmdPrint_Click()
Dim strWhere As String, varitem As Variant
For Each varitem In Me.List0.ItemsSelected
strWhere = strWhere & Me.List0.Column(0, varitem) & ","
Next varitem
strWhere = "[ID] IN (" & strWhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strWhere
end sub

Thank you for your time......
Panos
Click to expand...

how about
Private Sub cmdPrint_Click()
Dim strwhere As String, varitem As Variant
If Me.List0.ItemsSelected.Count = 0 Then
For varitm = 0 To Me.List0.ListCount - 1
strwhere = strwhere & Me.List0.Column(0, varitem) & ","
Next itm
Else
For Each varitem In Me.List0.ItemsSelected
strwhere = strwhere & Me.List0.Column(0, varitem) & ","
Next varitem
End If
strwhere = "[ID] IN (" & strwhere & ")"
DoCmd.OpenReport "ReportList", acViewPreview, , strwhere

End Sub
Click to expand...
Click to expand...
 
Back
Top