DataReports Question 1

[BPMan]

I have a table in Microsoft access that has three datafields:
Commodity
EDGroup
Vehicle
I have three combo boxes that a user can use to select a Commodity or a EDGroup or a Vehicle.
I want to print a report based on the user's selection.
So if the Commodity combo box is equal to Battery and the
EDGroup is equal to ED6 and the
Vehicle is equal to All
I want to print a report that has all the entries where
Commodity = Battery and
EDGroup = ED6
I am using an SQL statement to display the results with a MSFlexGrid buy can't for the life of me figure out how to print out the results....
thanks

 

[Norris68]

You will probably want to use a DataReport. This can be set up for a specific recordset if you use the DataEnvironment, or assigned at run-time. After opening the report, set it's recordset property to the flexgrid recordset.

e.g. something like ...
Load DataReport1
Set DataReport1.Recordset = FlexGrid1.Recordset
DataReport1.Show

 

[BPMan]

this does not work......
anyone else have some advice

 

[Norris68]

Sorry - I meant "Set DataReport1.[red]DataSource[/red] = FlexGrid1.Recordset"

Have you set your textbox DataField property to one of the field names? And if you are using hierarchical recordset you will need to set the DataMember property to the appropriate recordset.

Here is a section of code that uses a hierarchical recordset. LinesSQL is a string that has been previously initialised with a SHAPE SQL statement. ShapeConn is a connection using the MS Data Shape provider and is already open.

'Open The Recordset
Set rsLines = New ADODB.Recordset
With rsLines
    .CursorLocation = adUseClient
    .Open LinesSQL, ShapeConn, adOpenStatic, adLockReadOnly
End With

'Set Up The Printer
NeedDefaultPrinterRestore = False
If PrinterName <> &quot;&quot; Then
    If Not SetSystemDefaultPrinter(PrinterName) Then
        MsgBox &quot;Error Setting Default Printer To &quot; & PrinterName & vbCr & _
               &quot;The Report Will Use The Default Printer &quot; & Printer.DeviceName, vbInformation
    Else
        NeedDefaultPrinterRestore = True
    End If
End If

Load rptSalesOrder
With rptSalesOrder
    Set .DataSource = rsLines
    .Title = App.ProductName & &quot; - Sales Order &quot; & CStr(SOID)
    'Set Report Header Label Captions
    .Sections(1).Controls(&quot;lblReportName&quot;).Caption = &quot;Sales Order &quot; & CStr(SOID)
    'Set Report Footer Label Captions
    .Sections(7).Controls(&quot;lblTermsAndDelivery&quot;).Caption = TermsAndDelivery
    .Sections(7).Controls(&quot;lblTotalInvoice&quot;).Caption = Format$(TotalGoods, &quot;Currency&quot;)
    'etc, etc.

    If Preview Then
        .Show vbModal, frmMDIMain
    Else
        .PrintReport
    End If
End With

'Restore Printer If Required
If NeedDefaultPrinterRestore Then RestoreSystemDefaultPrinter

 

[BPMan]

That was helpful but i am still confused. I don't i have clearly stated my problem.
Here is the exact problem.
I have an SQL statement that i build every time the user changes a combo box on my form. I build this by making a String called strSQL. So i have this string that looks like this...
SELECT CSIPS.Commodity, CSIPS.EDGroup FROM CSIPS WHERE Commodity = 'Battery'

and I want to print the results of this.....
how would i go about that?
thanks for all your help....

 

[Norris68]

Dim rs As ADODB.Recordset
Dim SQL As String
...
SQL = "SELECT Commodity, EDGroup FROM CSIPS WHERE Commodity = 'Battery'"
rs.Open SQL 'and the other parameters e.g. Conn, LockType
If Not rs.EOF Then
    Load DataReport1
    With DataReport1
        Set .DataSource = rs
        .Show 'to preview or .PrintReport to print directly
    End With
Else
    MsgBox "No data to display", vbInformation
End If
rs.Close
Set rs = Nothing
...

Make sure your report has textboxes within the Detail section that bind to "Commodity" and "EDGroup" - in the .DataField property. Leave the DataReport .DataSource blank in the design, and leave all .DataMember properties blank (unless using a hierarchical recordset)

P.S. Thanks for the star whoever it was!

 

[BPMan]

thanks a ton norris68
i gave you the star....i have spent a ton of time trying to get this to work and with your help i finially did..........
one more question thought related to this.........
i want the report to print out in landscape........
but whenever i make my datareport's width bigger than 8.5 inches i get and error...
even if i set the printer to landscape..........
anymore good advice????
thanks again...

 

[Norris68]

LOL!
Extract from one of my applications ...

'Set Up The Printer For Landscape
'See Microsoft Knowledge Base Articles Q197915 & Q198901 For
'Why We Are Using This Object !
'(Printer Object Does Not Set Default Printer Settings Persistantly
'The DataReport Object Uses System Default Printer, Not Printer Object Settings !)
Set PS = New PageSet.PrinterControl
On Error Resume Next
PS.ChngOrientationLandscape
...
PS.ReSetOrientation
Set PS = Nothing

Basically you will need to download a Microsoft 'cludge'

 

[BPMan]

ummmmmmmmmmmmmmmmmmm
i must be missing something cause i cant get this to work..........
i do not know what a pageset is......
basicially i have a command button that goes like this............
Private Sub cmdPrint_Click()
DataReport1.PrintReport True
End Sub

how can i get this to allow me to print in landscape??????
with the dataReport being wider than 8.5inches........
thanks

 

[Norris68]

Look at:
PRB: Report Width is Larger than the Paper Width (Q197915)

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q197915

FIX: Error Message "Report Width Is Larger Than the Paper Width" When Showing Data Report in Landscape (Q261193)

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q261193

SAMPLE: PageSet.exe Programmatically Changes Default Printer Orientation (Q198901)

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q198901.

The pageset.dll download is available from

http://download.microsoft.com/download/vb60pro/Utility/1/WIN98/EN-US/Pageset.exe

 

[BPMan]

I can't get this to work......
when i go to the add printer wizard there aren't any of the options that it tells me to select......
what am i doing wrong?????????????
thanks

 

[Norris68]

The pageset dll is an ActiveX object that will change and restore the paper orientation of the system default printer. You will need to download it, install it and set a reference to it in your project references. Then declare a variable and start calling its methods. See the code sample in my 4th post - it contains code as well as comments. Insert your call to print your report where I have put the '...'.

The reason for this is that setting the Printer.Orientation will not have any effect on the DataReport because it uses the system default printer settings. Changes within code to the VB Printer object properties are not reflected in the default printer.

So, use the pageset object to switch orientation to landscape before you open your report and you won't get the error that you have been doing.

You don't need to install any new printers.

 

[bubarooni]

Can I actually set the data report datasource to the flexgrid?
I get an error when I do it this way:

Set DataReport1.DataSource = MSFlexGrid1.Recordset

It doesn't recognize the 'MSFlexGrid1.Recordset'

 

[Norris68]

How are you getting the data into the FlexGrid?

 

[bjd4jc]

Where are you setting the recordset to?

You may need to specify

frm???.MSFlexGrid1.Recordset

Just a thought

 

[bubarooni]

on the Form_load event I create an ado recordset that populates the flexgrid. there is a button on the form that user clicks to print the report. the ado recordset is created with local variables in the form_load event. the connection and recordset are created and set back to nothing in that event so there is actually nothing to even pass to the command button.

 

[bubarooni]

just trying to keep thread at top of list where Norris68 or someone else might see it and have an answer...

 

[Norris68]

How about:
Save your SQL that you used to populate the FlexGrid. Then when you want to do the report, create a recordset using the saved SQL statement and set the report DataSource to it as in my third post?

 

[bubarooni]

Works like a charm.