How to print the contents of a List Box

[Robert B via AccessMonster.com]

I have a query form which will allow selections of fields for display, filer
and sort, develop a SQL query and populate a list box. The box also gets its
column widths from a table of fields and corresponding widths via a lookup
function which builds the column width string. Unfortunately, there seems to
be no way to oputput this list to a printer. The print icon or DoCmd.
Printout simply prints the screen. Since each query differs, an infinite
variety of forms would be required. I can create a Querydef or a Recordset
from the SQL statement, but I can't figure out how to print the data as I
could from an open Querydef or form. Anyone have a clue?

Bob Boyden

 

[Guest]

Hi, Robert.

One of the VIP's might have a more elegant solution, you can get at the list
box' BoundColumn's values with the following code, which uses the ListCount
and ItemData properties of the combo box.

This example simply displays a message box with each value, but, depending
on what you want the list for, you could either print them to the Debug
window or insert them as values into a table. Once inserted, a pre-defined
query could join the table to the source table to enable printing the other
fields associated with each record.

Dim ctlList As Control
Dim varItem As Variant
Dim intIndex As Integer

Set ctlList = Me!YourListBox
For intIndex = 0 To ctlList.ListCount - 1
MsgBox "Item " & intIndex & " is " & Me!YourListBox.ItemData(intIndex)
Next intIndex

Hope that helps.
Sprinks

 

[Robert B via AccessMonster.com]

I seem to have solved my own problem, so though I shgould pas it along.



Public Sub cmdPrintList_Click()


Dim qdfTemp As QueryDef
Dim strQName As String


' Set the database
Set dbs = CurrentDb()

strQName = InputBox("Enter Name of List", "Header Name")
' strQName = strSite + " - " + strQName

' Open the Query
Set qdfTemp = dbs.CreateQueryDef(strQName, strSQL) ' strSQL is the SQL
string used for the list box
DoCmd.SelectObject acQuery, strQName, True
DoCmd.PrintOut
dbs.QueryDefs.Delete qdfTemp.Name
DoCmd.SelectObject acForm, "Universal Locations / Photo Log Query"

' ' Set up the Printer
' Dim prtFirst As Printer
' Set prtFirst = Application.Printers(2)
' With prtFirst
' .TopMargin = 1000
' .BottomMargin = 1000
' .LeftMargin = 300
' .RightMargin = 300
' .Orientation = acPRORLandscape
' End With

End Sub

 

[Robert B via AccessMonster.com]

My last posting got a bit garbled ... sorry.
I've partially solved the problem myself ... to print from a command button:

Public Sub cmdPrintList_Click()


Dim qdfTemp As QueryDef
Dim strQName As String

' Set up the database
Set dbs = CurrentDb()

' Get a name for the printout
strQName = InputBox("Enter Name of List", "Header Name")

' Open a Query using that name in the header
Set qdfTemp = dbs.CreateQueryDef(strQName, strSQL) 'strSQL is the SQL
string from the List Box

' Make the new query the active object
DoCmd.SelectObject acQuery, strQName, True

' Print the list
DoCmd.PrintOut

' Delete the Query
dbs.QueryDefs.Delete qdfTemp.Name

' Return the display to the List Box screen
DoCmd.SelectObject acForm, "List Box Screen Name"

' I tried using the code below to set up the printer but can't get it to
work yet.
' ' Set up the Printer
' Dim prtFirst As Printer
' Set prtFirst = Application.Printers(2)
' With prtFirst
' .TopMargin = 1000
' .BottomMargin = 1000
' .LeftMargin = 300
' .RightMargin = 300
' .Orientation = acPRORLandscape
' End With

End Sub

This does about the same job of printing as would printing a query diplayed
on the screen using a "Printer" icon. I'd like to be able to individual
column widths, typeface, orientation, Etc.

All help is very much appreciated

Bob Boyden