Printing Reports Using VB6

[Kurt Nes]

Hi,
I am currently trying to finish my assignment for college. I have to create a VB6 application using ADO to front an MS Access database. It is a based on a group of solicitors , and i have to allow add,delete edit records ect.

I have almost finished the assignment, but one of the main things we need to beable to do is to print Reports from an SQL query. i.e "All Solicitor Details Grouped by Grade".

The problem i am having is i just cannot for the life of me, get the report to print out neatly. the 1st few records come out neatly, but then it goes wonky, and whatever i try, just does not work.
Could somebody please help me as this is the last thing i need to do to finish the assignment, and i am really struggling.

Private Sub cmdPrintReport_Click()

On Error Resume Next
    sqlReport = "SELECT * FROM SolicitorQuery"
        Set rs1 = cn1.Execute(sqlReport)
    
    ' Printer Headers
    rs1.MoveFirst
     While Not rs1.EOF
       
        Printer.FontBold = True    ' makes font bold
        Printer.FontSize = 18      ' sets the font size for the headers
            pline = String(62, " ")
                Mid(pline, 27, 35) = "Solicitor Details in order of Grade"
        Printer.Print pline
        Printer.FontSize = 12
        Printer.FontBold = False
            pline = String(121, "*")
        Printer.Print pline
        Printer.Print ""
            pline = String(121, " ")
                Mid$(pline, 12, 12) = "Solicitor ID"
                Mid$(pline, 35, 5) = "Title"
                Mid$(pline, 50, 7) = "Surname"
                Mid$(pline, 66, 8) = "Initials"
                Mid$(pline, 81, 5) = "Grade"
                Mid$(pline, 93, 7) = "Partner"
                Mid$(pline, 107, 11) = "Description"
        Printer.Print pline
        Printer.Print ""
            pline = String(150, ".")
        Printer.Print pline
'Print Data

    While Not rs1.EOF
       Printer.Print ""
            pline = String(140, " ")
                Mid$(pline, 12, 5) = rs1!Solicitor_ID
                Mid$(pline, 38, 7) = rs1!Title
                Mid$(pline, 55, 20) = rs1!Surname
                Mid$(pline, 78, 7) = rs1!Initials
                Mid$(pline, 92, 5) = rs1!Grade
                Mid$(pline, 103, 8) = rs1!Partner
                Mid$(pline, 115, 19) = rs1!Description
        Printer.Print pline
        
        rs1.MoveNext
        Wend
    Wend
Printer.EndDoc

End Sub

[BruceG]

The problem is the use of a proportional font (you seem to be defaulting to Arial, which is typical). For this type of printout, you want to use a non-proportional (monospaced) font. Try putting this code at the beginning of your routine:

   Dim intX As Integer

    For intX = 0 To Printer.FontCount - 1
        If Printer.Fonts(intX) Like "Courier*" Then
            Printer.FontName = Printer.Fonts(intX)
            Exit For
        End If
    Next

[Kurt Nes]

YESSSSSS. thanks m8, thats done the trick. just need to readjust my mid(pline 15, 12) settings now.

Cheers again

[BruceG]

My pleasure!

[leinad31]

Just so your aware, another alternative would be the use of a data report...

[Kurt Nes]

Just so your aware, another alternative would be the use of a data report...

not been shown how to do Data Reports. How would i make one then ? I created one, and added some headers ect, but how would i link it ? so i could just click a button on my form and it would then print off the report based on the sql query ?

[leinad31]

Don't know if there's a better sample, here's one that's not design time bound

http://www.vbforums.com/showthread.p...ataenvironment

Just make sure you name the datareport textboxes accordingly so its not confusing when you reference them progmatically. For displaying the records in the recordset, you will need data report textboxes in the details section. For heirarchical queries using SHAPE sql command you may need to create textboes in the relevant group section.

[Hack]

Moved to reporting section