Running a report on a filtered Listbox

G

Guest

I have a form that has a listbox wich
displays a filtered table. I would like to print a report of the contents
of that
listbox. This seems like an easy task but I'm struggling here. Can you
help?
 
G

Guest

Do you want to include everything in the list box or only selected items?
Is it a multi-select list box?
What else do you want in the report?
 
G

Guest

I want to show everything that is in the listbox. For example my list box
contains a running list of items that need to be completed on a construction
project. I have a combo box that has all the catagories for the items. If I
select on type of item like "Work In Progress", the list box will show me a
filter of only the "Work In Progress" items. I would like to create a button
that will print out whats currently filtered in that list box.
 
G

Guest

Assuming your list box row source is a table or query, all you need to do is
use the same table or query for the report.
 
G

Guest

When I do that it prints all the records from the table instead of what was
filtered in the list box.
 
G

Guest

I will be filitering this list box called ItemsLbx multiple ways. Initially
the user will filter it by an Action Code:

Private Sub ActionLbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value&
" '"

Call ActionLbx.Requery
End Sub

Then the user will have the ability to sort it by a few different other
fields based on combo boxes:

Private Sub SrtLevelCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Level = '" & SrtLevelCbx.Value
& "'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtLevelCbx.Requery

End Sub

Private Sub SrtRoomCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtRoomCbx.Requery
End Sub

Private Sub SrtAORCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.AOR ='" & SrtAORCbx.Value & "'"
& _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtAORCbx.Requery
End Sub

So basically I would want to be able to hit a button at anytime during the
filtering process and just capture whats in the list box. Is there a way i
can get it to do a report and list only item IDs that are currently in the
list box?
 
G

Guest

Here is a function that returns a string that can be used as a Where
condition from a list box. It only includes selected items or if no items
are selected, it returns an empty string. You may have to modify it make it
do exactly what you want.
--
Dave Hargis, Microsoft Access MVP


mreckley said:
I will be filitering this list box called ItemsLbx multiple ways. Initially
the user will filter it by an Action Code:

Private Sub ActionLbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value&
" '"

Call ActionLbx.Requery
End Sub

Then the user will have the ability to sort it by a few different other
fields based on combo boxes:

Private Sub SrtLevelCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Level = '" & SrtLevelCbx.Value
& "'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtLevelCbx.Requery

End Sub

Private Sub SrtRoomCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtRoomCbx.Requery
End Sub

Private Sub SrtAORCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.AOR ='" & SrtAORCbx.Value & "'"
& _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtAORCbx.Requery
End Sub

So basically I would want to be able to hit a button at anytime during the
filtering process and just capture whats in the list box. Is there a way i
can get it to do a report and list only item IDs that are currently in the
list box?


Klatuu said:
How did you filter the list box?
The report needs to be filtered the same way.
Click to expand...
Click to expand...
 
G

Guest

Dave, I cant seem to find the function you posted, can you post it again?

Klatuu said:
Here is a function that returns a string that can be used as a Where
condition from a list box. It only includes selected items or if no items
are selected, it returns an empty string. You may have to modify it make it
do exactly what you want.
--
Dave Hargis, Microsoft Access MVP


mreckley said:
I will be filitering this list box called ItemsLbx multiple ways. Initially
the user will filter it by an Action Code:

Private Sub ActionLbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value&
" '"

Call ActionLbx.Requery
End Sub

Then the user will have the ability to sort it by a few different other
fields based on combo boxes:

Private Sub SrtLevelCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Level = '" & SrtLevelCbx.Value
& "'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtLevelCbx.Requery

End Sub

Private Sub SrtRoomCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtRoomCbx.Requery
End Sub

Private Sub SrtAORCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.AOR ='" & SrtAORCbx.Value & "'"
& _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtAORCbx.Requery
End Sub

So basically I would want to be able to hit a button at anytime during the
filtering process and just capture whats in the list box. Is there a way i
can get it to do a report and list only item IDs that are currently in the
list box?


Klatuu said:
How did you filter the list box?
The report needs to be filtered the same way.
--
Dave Hargis, Microsoft Access MVP


:

When I do that it prints all the records from the table instead of what was
filtered in the list box.

:

Assuming your list box row source is a table or query, all you need to do is
use the same table or query for the report.
--
Dave Hargis, Microsoft Access MVP


:

I want to show everything that is in the listbox. For example my list box
contains a running list of items that need to be completed on a construction
project. I have a combo box that has all the catagories for the items. If I
select on type of item like "Work In Progress", the list box will show me a
filter of only the "Work In Progress" items. I would like to create a button
that will print out whats currently filtered in that list box.

:

Do you want to include everything in the list box or only selected items?
Is it a multi-select list box?
What else do you want in the report?
--
Dave Hargis, Microsoft Access MVP


:

I have a form that has a listbox wich
displays a filtered table. I would like to print a report of the contents
of that
listbox. This seems like an easy task but I'm struggling here. Can you
help?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes, I guess it would help if I actually posted the function.

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function
--
Dave Hargis, Microsoft Access MVP


mreckley said:
Dave, I cant seem to find the function you posted, can you post it again?

Klatuu said:
Here is a function that returns a string that can be used as a Where
condition from a list box. It only includes selected items or if no items
are selected, it returns an empty string. You may have to modify it make it
do exactly what you want.
--
Dave Hargis, Microsoft Access MVP


mreckley said:
I will be filitering this list box called ItemsLbx multiple ways. Initially
the user will filter it by an Action Code:

Private Sub ActionLbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value&
" '"

Call ActionLbx.Requery
End Sub

Then the user will have the ability to sort it by a few different other
fields based on combo boxes:

Private Sub SrtLevelCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Level = '" & SrtLevelCbx.Value
& "'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtLevelCbx.Requery

End Sub

Private Sub SrtRoomCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value
& "'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtRoomCbx.Requery
End Sub

Private Sub SrtAORCbx_AfterUpdate()
ItemsLbx.RowSource = "SELECT
[ItemID],[EnteredBy],[Level],[Room],[AOR],[System],[Sub],[Own_Arch],[ByActStat],[WTC],[PL],[ProjectNum],[Notes],[Date]" & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.AOR ='" & SrtAORCbx.Value & "'"
& _
"AND ItemMasterTbl.ByActStat ='" & ActionLbx.Value &
"'" & _
"AND ItemMasterTbl.Room = '" & SrtRoomCbx.Value &
"'" & _
"AND ItemMasterTbl.Level ='" & SrtLevelCbx.Value & "
'" & _
"AND ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
& " '"
Call SrtAORCbx.Requery
End Sub

So basically I would want to be able to hit a button at anytime during the
filtering process and just capture whats in the list box. Is there a way i
can get it to do a report and list only item IDs that are currently in the
list box?


:

How did you filter the list box?
The report needs to be filtered the same way.
--
Dave Hargis, Microsoft Access MVP


:

When I do that it prints all the records from the table instead of what was
filtered in the list box.

:

Assuming your list box row source is a table or query, all you need to do is
use the same table or query for the report.
--
Dave Hargis, Microsoft Access MVP


:

I want to show everything that is in the listbox. For example my list box
contains a running list of items that need to be completed on a construction
project. I have a combo box that has all the catagories for the items. If I
select on type of item like "Work In Progress", the list box will show me a
filter of only the "Work In Progress" items. I would like to create a button
that will print out whats currently filtered in that list box.

:

Do you want to include everything in the list box or only selected items?
Is it a multi-select list box?
What else do you want in the report?
--
Dave Hargis, Microsoft Access MVP


:

I have a form that has a listbox wich
displays a filtered table. I would like to print a report of the contents
of that
listbox. This seems like an easy task but I'm struggling here. Can you
help?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Cannot select items in listbox 1
Populate Textbox 1
Listbox SQL problem 2
Listbox won't display contents until I click on it 4
Filter listbox using combo box 1
ListBox focus 4
Marking items as selected in a Muli-Select listbox 3
unbound listbox... how do i make the current record selected 4

Top