asked on

Printing Problem

Hi All!

I'm trying to print from a Recordset. I cannot get the columns to line up correctly. Here's my code. Any help is appreciated!

Private Sub cmdPrint_Click()
Dim ctr As Integer
Dim oRS As ADODB.Recordset
Dim oCn As ADODB.Connection
Dim lOrigin As Long
Dim lDest As Long
Dim sOrigin As String
Dim sDest As String

On Error GoTo PRINT_EH

Set oRS = New ADODB.Recordset
Set oCn = New ADODB.Connection
oCn.Open ConString

oRS.Open "SELECT Origin, Destination, Amount FROM Fares", oCn, adOpenForwardOnly, adLockReadOnly

ctr = 1

Printer.Font = "Courier"
Printer.FontSize = 10
Printer.Print " "
Printer.Print " "
Printer.Print " " & FormatDateTime(Date, vbLongDate)
Printer.Print " "
Printer.FontSize = 12
Printer.Print " Fares Listing"
Printer.Print " "
Printer.Print " "
Printer.FontSize = 10
With oRS
Do
If .Fields("Destination") <> "No Show" Then
sOrigin = Mid$(.Fields("Origin"), 1, 30)
sDest = Mid$(.Fields("Destination"), 1, 30)
lOrigin = Len(sOrigin)
lDest = Len(sDest)
Printer.Print sOrigin & Space(30 - lOrigin) & Chr(9) & sDest & Space(30 - lDest) & Chr(9) & Format(Format(.Fields("Amount"), "###,##0.00"), "@@@@@@@@@@@")
If Printer.CurrentY > Printer.ScaleHeight - 1000 Then
Printer.Print " "
Printer.Print " "
Printer.Print " Page " & ctr
ctr = ctr + 1
Printer.NewPage
End If
End If
.MoveNext
Loop Until .EOF
End With
Printer.EndDoc

oRS.Close
oCn.Close
Set oRS = Nothing
Set oCn = Nothing

Exit Sub
PRINT_EH:
MsgBox "Error Printing Report", vbCritical, "Printing Error"
Err.Clear
End Sub

Thanks in advance!
Alice

3Mann

Hi, have you considered using a reporting tool? One is available with VB6, DataReport. Or u may use Crystal Reports from Seagate.

Cheers

ASKER CERTIFIED SOLUTION

inthedark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alicelknight

ASKER

3Mann: I do not want to use a reporting tool. I find Data Reports too time consuming for the benefit they provide. Also the inability to dynamically set the connection string is another downfall. But that is another topic.....

InTheDark: Thanks! This is just what I needed.

inthedark

Hi alicelknight, thanks for the points. In 1996, before classes, I created a Module to handle printing.

For me to create a report now I just type:

SQL = "Select * from MtyTable;"
ok = prPrintReport("REPNAME",SQL,"Help Topic","Help Sub Topic")

I then just answer questions to comple the rest of the process. It takes just seconds.

How long does it take to reate a report using Data Reports?

alicelknight

ASKER

InTheDark: Shi... Trying to use DataReports to create one simple report may take hours. Aligning controls and such... I've come to detest it. Thanks to your code I can come with a simple solution as you have. It's better too because I don't have to depend on a "connection string...." I've yet to figure that one out. I've tried many different ways, examples, that I've seen but none worked. So I stay far from DataReports! And Data Environments! Right now I have to redo code that was done using a Data Environment at work. The reports are next.... ;) I can't tell you how your code has helped me! :)
Alice