Report of the contents of a List Box on a Form

[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?

 

[Stefan Hoffmann]

hi,

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:

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

Use the SQL from the ListBox RowSource to build a criteria like this:

"WHERE ItemID IN (" & _
"SELECT [ItemID] " & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.ByActStat = '" & ActionLbx.Value & "' " & _
"AND ItemMasterTbl.ProjectNum = '" & PrjNumTxt.Value & "'" & _
")"


mfG
--> stefan <--

 

[Guest]

Would that still work if ActionLbx was a null? If not is there a way to
make it still work?

hi,

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:

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

Use the SQL from the ListBox RowSource to build a criteria like this:

"WHERE ItemID IN (" & _
"SELECT [ItemID] " & _
"FROM ItemMasterTbl " & _
"WHERE ItemMasterTbl.ByActStat = '" & ActionLbx.Value & "' " & _
"AND ItemMasterTbl.ProjectNum = '" & PrjNumTxt.Value & "'" & _
")"


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Would that still work if ActionLbx was a null?

... then your ListBox was also empty?


mfG
--> stefan <--

 

[Bob Howard]

I would convert your select query to a regular Access query. Use the query
in both the Listbox and in a report. That way, if you make a change to the
query, it'll immediately be reflected in both places (easier program
maintenance). Bob