Report of the contents of a list box

[Guest]

I havent had much luck with this, and perhaps its the way Im asking the
question. I have a list box on a form. I would like to run a report of the
contents of the list box. All the contents, not one or two records I select
but whatever is shown in the list box. The filter for my list box is as such:


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

Now I have been trying to creat a button that will do a report of the
contents of this list box and this is the code I have most recently used:


Private Sub IntReport_Click()
On Error GoTo Err_IntReport_Click

Dim stDocName As String

stDocName = "Interior Report"
DoCmd.OpenReport stDocName, acPreview, "WHERE ItemID=" & Me.ItemsLbx


Exit_IntReport_Click:
Exit Sub

Err_IntReport_Click:
MsgBox Err.Description
Resume Exit_IntReport_Click

End Sub

However this does a report of the entire table not whats filtered into the
list box. Can anyone assist me with this?

 

[Douglas J. Steele]

Your condition for opening the report is incorrect. The word "WHERE"
shouldn't be there. As well, assuming the list box allows multiselect, you
can't simply refer to it like that (that reference will always return Null,
even if only one row is selected in the multiselect list box)

You need something like:

Private Sub IntReport_Click()
On Error GoTo Err_IntReport_Click

Dim stDocName As String
Dim strCriteria As String
Dim varSelected As Variant

stDocName = "Interior Report"

For Each varSelected in Me.ItemsLbx.ItemsSelected
strCriteria = strCriteria & Me.ItemsLbx.ItemData(varSelected) & ", "
Next varSelected
If Len(strCriteria) > 0 Then
strCriteria = "ItemID IN (" & Left$(strCriteria, Len(strCriteria) - 2) &
")"
End If

DoCmd.OpenReport stDocName, acPreview, strCriteria

Exit_IntReport_Click:
Exit Sub

Err_IntReport_Click:
MsgBox Err.Description
Resume Exit_IntReport_Click

End Sub

 

[Guest]

I think I tried to help you before with this. It is not how you are asking
the question, it is that you are not understanding the anwser.

Two things.
I notice you are doing a requery after assigning the row source to the list
box. That is not necessary. When you change the row source, a requery
occurs automatically.

Now the answer.

The row source of your list box is a query. Your report has to have a
record source. The record source is what presents the data to the report.
The row source of a list box is what presents the data to the list box. They
are identical. All you need to do is use that same select statement as the
record source of your report. You don't even have to create a stored query
for it. You can copy/pasted it from your VBA code into the row source
property box in the properties dialog for the report. The only change you
will need to make is to fully qualify your references to the controls on the
form.

For example:
ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
will need to be:
ItemMasterTbl.ProjectNum ='" & Forms!MyFormName!PrjNumTxt

Notice I removed Value. It is not necessary as it is the default property
of the control. Also when you are using it in the form for the list box, it
should have a reference to the current form:
ItemMasterTbl.ProjectNum ='" & Me.PrjNumTxt.Value

Now, this part concerns me:

"WHERE ItemMasterTbl.ByActStat ='" & ActionLbx.Value

If ActionLbx is the name of the list box you want the report to emulated, it
may not work if it is a multi select list box. A multi select list box will
not return a value. It will return Null. You get values from it by using
the ItemsSelected collection of the list box.

If ActionLbx is a different single select list box, then just adjust the
syntax as you will for PrjNumTxt and it should work.
--
Dave Hargis, Microsoft Access MVP

I havent had much luck with this, and perhaps its the way Im asking the
question. I have a list box on a form. I would like to run a report of the
contents of the list box. All the contents, not one or two records I select
but whatever is shown in the list box. The filter for my list box is as such:


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

Now I have been trying to creat a button that will do a report of the
contents of this list box and this is the code I have most recently used:


Private Sub IntReport_Click()
On Error GoTo Err_IntReport_Click

Dim stDocName As String

stDocName = "Interior Report"
DoCmd.OpenReport stDocName, acPreview, "WHERE ItemID=" & Me.ItemsLbx


Exit_IntReport_Click:
Exit Sub

Err_IntReport_Click:
MsgBox Err.Description
Resume Exit_IntReport_Click

End Sub

However this does a report of the entire table not whats filtered into the
list box. Can anyone assist me with this?

 

[Guest]

Thankyou Klatuu that helps me big time. The ActionLbx is a single select box
that filters the main list box called ItemsLbx. I have one more question
though. If i have multiple listboxes that further filter ItemsLbx that means
my select statement will have changed. Would I have to have multiple print
buttons for every filtered version of the list box?

I think I tried to help you before with this. It is not how you are asking
the question, it is that you are not understanding the anwser.

Two things.
I notice you are doing a requery after assigning the row source to the list
box. That is not necessary. When you change the row source, a requery
occurs automatically.

Now the answer.

The row source of your list box is a query. Your report has to have a
record source. The record source is what presents the data to the report.
The row source of a list box is what presents the data to the list box. They
are identical. All you need to do is use that same select statement as the
record source of your report. You don't even have to create a stored query
for it. You can copy/pasted it from your VBA code into the row source
property box in the properties dialog for the report. The only change you
will need to make is to fully qualify your references to the controls on the
form.

For example:
ItemMasterTbl.ProjectNum ='" & PrjNumTxt.Value
will need to be:
ItemMasterTbl.ProjectNum ='" & Forms!MyFormName!PrjNumTxt

Notice I removed Value. It is not necessary as it is the default property
of the control. Also when you are using it in the form for the list box, it
should have a reference to the current form:
ItemMasterTbl.ProjectNum ='" & Me.PrjNumTxt.Value

Now, this part concerns me:

"WHERE ItemMasterTbl.ByActStat ='" & ActionLbx.Value

If ActionLbx is the name of the list box you want the report to emulated, it
may not work if it is a multi select list box. A multi select list box will
not return a value. It will return Null. You get values from it by using
the ItemsSelected collection of the list box.

If ActionLbx is a different single select list box, then just adjust the
syntax as you will for PrjNumTxt and it should work.
--
Dave Hargis, Microsoft Access MVP

I havent had much luck with this, and perhaps its the way Im asking the
question. I have a list box on a form. I would like to run a report of the
contents of the list box. All the contents, not one or two records I select
but whatever is shown in the list box. The filter for my list box is as such:


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

Now I have been trying to creat a button that will do a report of the
contents of this list box and this is the code I have most recently used:


Private Sub IntReport_Click()
On Error GoTo Err_IntReport_Click

Dim stDocName As String

stDocName = "Interior Report"
DoCmd.OpenReport stDocName, acPreview, "WHERE ItemID=" & Me.ItemsLbx


Exit_IntReport_Click:
Exit Sub

Err_IntReport_Click:
MsgBox Err.Description
Resume Exit_IntReport_Click

End Sub

However this does a report of the entire table not whats filtered into the
list box. Can anyone assist me with this?