Solved

Binding DataReport (in VB6) to custom recordset

Posted on 1999-02-22
3,887 Views
Last Modified: 2013-12-25
I nave designed a report using DataReport designer in VB6 (NOT Crystal). This report is bound to some DataEnvironment in design time. I want to bind my report in runtime to my custom ADODB.Recordset (having just the same structure) How to do it?
0
Question by:pavel_dolgov
    8 Comments
     
    LVL 1

    Expert Comment

    by:eelpark
    Basically you have to cycle through each report section and reset the datamember/source. This is from article is from

    http://support.microsoft.com/support/kb/articles/q190/4/11.asp

    I hope this helps.

    Here is the offical answer with example:
    -----------------------------------------------------------------------------------
    HOWTO: Bind a DataReport To an  ADO Recordset at Run Time

      The information in this article applies to:

           Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0

      SUMMARY

      The DataReport is a powerful tool and it's easy to build complex reports by dragging and
      dropping fields out of the DataEnvironment window. However, there are times when you
      may want to bind the DataReport directly to an ActiveX Data Objects (ADO) recordset
      rather than to the DataEnvironment. For example, you may have built a hierarchical query
      with ADO, or you may have an n-tier application that receives a recordset from a business
      object.

      This article helps you understand how to bind a DataReport directly to an ADO recordset.

      MORE INFORMATION

      First, build a hierarchical query with the DataEnvironment. Next, create a simple DataReport
      that is based on your query and bound to the DataEnvironment.

      Use the DataEnvironment to connect to the Northwind database (NWind.mdb) that is
      included with Visual Basic by following these steps:

         1.Create a new Standard EXE project in Visual Basic.

         2.Add a DataEnvironment to that project and rename it deCustomerOrders.

         3.Rename the initial connection to cnNWind

         4.Set the connection to use the Microsoft.Jet.OLEDB.3.51 OLE DB provider.

         5.Locate the Northwind database on your machine.

         6.Add a command to the connection and rename it Customers.

         7.Set the Customers command to query the Customers table.

         8.Add a child command to the Customers command and rename it Orders.

         9.Set the Orders command to query the Orders table.

        10.Relate the two commands on the CustomerID field on the Relation tab.

        11.Add a DataReport to the project and rename it rptCustomerOrders.

        12.Set the DataSource property of the DataReport to deCustomerOrders.

        13.Set the DataMember property of the DataReport to Customers.

        14.Right-click on the DataReport and clear "Show Report Header/Footer".

        15.Right-click on the DataReport and clear "Show Page Header/Footer".

        16.Right-click on the DataReport and select "Insert Group Header/Footer".

        17.Drag the CustomerID and CompanyName fields from the Customers command in the
           DataEnvironment onto the Group Header section.

        18.Drag the OrderID and OrderDate fields from the Orders command in the
           DataEnvironment onto the Detail section.

        19.Add a CommandButton to your form.

        20.Add the following code to your form:

              Private Sub Command1_Click()
                  rptCustomerOrders.Show
              End Sub


        21.Run the project, click on the CommandButton and you should see the report with the
           customer and order information.

        22.To bind the DataReport directly to the hierarchical recordset generated by the
           DataEnvironment, add the following code:

              Private Sub Form_Load()
                  Dim intCtrl As Integer
                  With rptCustomerOrders
                      Set .DataSource = Nothing
                      .DataMember = ""
                      Set .DataSource = deCustomerOrders.rsCustomers
                      With .Sections("Section2").Controls
                          For intCtrl = 1 To .Count
                              If TypeOf .Item(intCtrl) Is RptTextBox Or _
                                 TypeOf .Item(intCtrl) Is RptFunction Then
                                  .Item(intCtrl).DataMember = ""
                              End If
                          Next intCtrl
                      End With
                      .Show
                  End With
              End Sub


      Note: If you omit steps 13 and 14, you need to change "Section2" to "Section6" in the
      preceding code.

         1.Run the project, and you should see the report with the customer and order
           information.

      The DataReport uses the DataSource and DataMember properties to find the top-level
      command on which the report is based. For example, if you have a hierarchical query in the
      DataEnvironment containing Customers, Orders, and Order Details information but you only
      want to show the Orders and Order Details information, then you should set the DataSource
      property to be the DataEnvironment, and the DataMember property to be the Orders
      command.

      Each field on the DataReport has two properties that allow the DataEnvironment to
      determine what information to show on the report:

      - DataMember - DataField.

      Use the DataMember property to select the level of the hierarchy that contains the
      information you want to display. Use the DataField property to select the field you want to
      display.

      For example, the CustomerID field is in both the Customers and the Orders table. If you
      want to show the CustomerID field with the rest of the customer information, set
      DataMember to Customers. If you want to show the CustomerID with the rest of the Order
      information, set DataMember to Orders.

      When you bind directly to a recordset object as shown in step 21, the DataSource property
      of the DataReport should be set to the recordset object and the DataMember property
      should be set to an empty string. For the fields on the report, the DataMember property of
      the top-level recordset information (customer information in this case) should be set to an
      empty string. For information other than that which is in the top-level recordset (Order
      information in this case), the DataMember property of the report TextBoxes should be set
      to the name of the command (Orders in this case).



    0
     

    Author Comment

    by:pavel_dolgov
    Maybe it works but it isn't what I need. I want to use MY OWN recordset having no relation to DataEnvironment at all. I know that I should use hierarhical recordset but how can I create it ?
    0
     
    LVL 1

    Expert Comment

    by:eelpark
    The point is pavel_dolgov you have to use the dataenvironment to set up the report in the reportdesigner. Then you can reset the recordsource/datamember at runtime to another recordset whether that recordset is in the dataenvironment or is a standalone recordset.

    This example just happens to be for a hierarchial dataset. It doesn't have to be.

    You had it correct in that it has to be the same structure.

    For the example above:

      Set .DataSource = deCustomerOrders.rsCustomers

    replace that with

      Set .DataSource = rsMyVeryOwnRecordsetObjectICreatedMyself

    That example just happened to be as bad as it gets.

    If you can't get that to work I will e-mail you a smaller example.

    Hope this helps.
    0
     
    LVL 3

    Expert Comment

    by:vbWhiz

    (You don't actually have to use the Data Environment to create a report - Just start adding fields to a report and type in the field name rather than selecting from the drop-down list)

    This is a simple sample for creating your own unbound recordset, populating it with data and setting it to be the datareport's datasource.

    For this code to run as-is you would need a datareport named MyReportsName with no group sections and four fields in the detail section (MyField1, MyField2, MyField3, MyField4)

    Dim Rst As Recordset
    Dim myRpt As datareport

    Set Rst = New Recordset
    Set myRpt = New MyReportsName

    Rst.Fields.Append "MyField1", adChar, 50
    Rst.Fields.Append "MyField2", adChar, 50
    Rst.Fields.Append "MyField3", adChar, 50
    Rst.Fields.Append "MyField4", adChar, 50

    Rst.Open

    Rst.AddNew

    Rst!MyField1 = "Some Text"
    Rst!MyField2 = "Some Text"
    Rst!MyField3 = "Some Text"
    Rst!MyField4 = "Some Text"

    Rst.Update

    Rst.AddNew

    Rst!MyField1 = "Some More Text"
    Rst!MyField2 = "Some More Text"
    Rst!MyField3 = "Some More Text"
    Rst!MyField4 = "Some More Text"

    Rst.Update

    Set myRpt.Source = Rst

    myRpt.Show

    0
     
    LVL 1

    Expert Comment

    by:eelpark
    That is correct you do not have to use the dataenvironment. It is just easier (for me anyway) to do it that way to test the report especially if you have a lot of grouping levels.

    The question was "I want to bind my report in runtime to my custom ADODB.Recordset (having just the same structure) How to do it?"

    12 ways to skin a cat. ;-)
    0
     
    LVL 3

    Accepted Solution

    by:
    4 ways to skin this cat that I can think of:

    Way #1:

    'The path of least resistance'

    Step 1:

    Create your commands in the data environment.

    Step 2:

    Create a DataReport based on these commands.

    Step 3:

    Find out what the entire SQL statement looks like for your commands (In ADO child command's SQL is embedded into the parent's SQL using the SHAPE command)

    Dim myCmd as Command

    For each myCmd in DataEnvironment1.Commands
       If myCmd.Name = "[Insert the name of your parent command here]" Then
          Debug.Print myCmd.CommandText
          Exit For
       End IF
    Next myCmd

    Step 4:

    Determine what it is you want to change about this command or sub-commands and locate those spots in the command text.

    Step 5:

    Write a report-printing routine

    Now you can write a routine that replaces the command text in the parent command with the changes you wanted to make.

    That routine might look something like this....

    SAMPLE CODE:

    Public Sub PrintThatReport(Optional Byval CompanyID as String)

       Dim myRpt as DataReport
       Dim myCmd as Command

       If CompanyID <> "" Then
          Set myRpt = New CompanyReport

          For Each myCmd In DataEnvironment1
             If myCmd.Name = "cmdCompany" Then
                myCmd.CommandText = "SELECT tblCompany.* FROM tblCompany WHERE tblCompany.fldCompanyID = '" & CompanyID & "'"
                myCmd.Execute
                myRpt.Refresh
                myRpt.Show
                Exit For
             End If
          Next myCmd
       Else
          myRpt.Show
       End If

    End Sub
         
         
    0
     
    LVL 3

    Expert Comment

    by:vbWhiz
    Way #2:

    'The purist method'

    Step 1:

    Create your commands in the data environment.

    Step 2:

    Create a DataReport based on these commands.

    Step 3:

    Find out what the entire SQL statement looks like for your commands (In ADO child command's SQL is embedded into the parent's SQL using the SHAPE command)

    Dim myCmd as Command

    For each myCmd in DataEnvironment1.Commands
       If myCmd.Name = "[Insert the name of your parent command here]" Then
          Debug.Print myCmd.CommandText
          Exit For
       End IF
    Next myCmd

    Step 4:

    Determine what it is you want to change about this command or sub-commands and locate those spots in the command text.

    Step 5:

    Write a report-printing routine that creates a recordset rather than depends on the DataEnvironment

    That routine might look something like this....

    SAMPLE CODE:

    Public Sub PrintThatReport(Optional Byval CompanyID as String)

       Dim myDBConn as ADODB.Connection
       Dim myCompRst as Recordset
       Dim myRpt as DataReport

       Set myDBConn = New ADODB.Connection
       myDBConn.Open "Database Connection String Goes Here!"
       If CompanyID <> "" Then
          Set myRpt = New CompanyReport

          myCompRst.Open "SELECT tblCompany.* FROM tblCompany WHERE tblCompany.fldCompanyID = '" & CompanyID & "'", myDBConn, adOpenStatic, adLockReadOnly
          Set myRpt.DataSource = myCompRst
          myRpt.Refresh
          myRpt.Show
       Else
          myCompRst.Open "SELECT tblCompany.* FROM tblCompany", myDBConn, adOpenStatic, adLockReadOnly
          Set myRpt.DataSource = myCompRst
          myRpt.Refresh
          myRpt.Show
       End If

    End Sub



    Step 6:

    Remove the reference to the DataEnvironment's command as the report datasource in the designer and remove that stinkin' DataEnvironment from the project! (You can still keep it around if you want to modify the report but it isn't necessary to be in the compiled version)
    0
     
    LVL 3

    Expert Comment

    by:vbWhiz
    Way #3:

    'The way of no bounds'

    Step 1:

    Create your commands in the data environment.

    Step 2:

    Create a DataReport based on these commands.

    Step 3:

    Examine the command structure especially the resulting field lists. Create a routine that builds an unbound recordset containing that same field structure.

    Dim Rst As Recordset

    Set Rst = New Recordset

    Rst.Fields.Append "fldCompanyID", adChar, 10
    Rst.Fields.Append "fldCompanyName", adChar, 50
    Rst.Fields.Append "fldCompanyAddress1", adChar, 50
    Rst.Fields.Append "fldCompanyAddress2", adChar, 50
    Rst.Fields.Append "fldCompanyCity", adChar, 50
    Rst.Fields.Append "fldCompanyState", adChar, 2
    Rst.Fields.Append "fldCompanyZip", adChar, 10

    Rst.Open


    Step 4:

    Load the report data into this unbound recordset.

    [-NOTE- The advantage of the 'way of no bounds' is that the report data doesn't actually have to come from a database, or be retrieved with one recordset]

    Dim Rst As Recordset

    Set Rst = New Recordset

    Rst.Fields.Append "fldCompanyID", adChar, 10
    Rst.Fields.Append "fldCompanyName", adChar, 50
    Rst.Fields.Append "fldCompanyAddress1", adChar, 50
    Rst.Fields.Append "fldCompanyAddress2", adChar, 50
    Rst.Fields.Append "fldCompanyCity", adChar, 50
    Rst.Fields.Append "fldCompanyState", adChar, 2
    Rst.Fields.Append "fldCompanyZip", adChar, 10

    Rst.Open

    Rst.AddNew

    Rst!fldCompanyID = "1001"
    Rst!fldCompanyName = "Boundless Company"
    Rst!fldCompanyAddress1 = "12324 Willow Lane"
    Rst!fldCompanyAddress2 = ""
    Rst!fldCompanyCity = "New York"
    Rst!fldCompanyState = "NY"
    Rst!fldCompanyZip = "12345"

    Rst.Update

    Step 5:

    Write a report-printing routine that creates an unbound recordset, fills it with data, and then generates the report

    That routine might look something like this....

    SAMPLE CODE:

    Public Sub PrintThatReport()

       Dim myRpt as DataReport
       Dim Rst As Recordset

       Set Rst = New Recordset

       Rst.Fields.Append "fldCompanyID", adChar, 10
       Rst.Fields.Append "fldCompanyName", adChar, 50
       Rst.Fields.Append "fldCompanyAddress1", adChar, 50
       Rst.Fields.Append "fldCompanyAddress2", adChar, 50
       Rst.Fields.Append "fldCompanyCity", adChar, 50
       Rst.Fields.Append "fldCompanyState", adChar, 2
       Rst.Fields.Append "fldCompanyZip", adChar, 10

       Rst.Open

       Rst.AddNew

       Rst!fldCompanyID = "1001"
       Rst!fldCompanyName = "Boundless Company"
       Rst!fldCompanyAddress1 = "12324 Willow Lane"
       Rst!fldCompanyAddress2 = ""
       Rst!fldCompanyCity = "New York"
       Rst!fldCompanyState = "NY"
       Rst!fldCompanyZip = "12345"

       Rst.Update
       Set myRpt = New CompanyReport
       Set myRpt.DataSource = Rst
       myRpt.Refresh
       myRpt.Show

    End Sub


    Step 6:

    Remove the reference to the DataEnvironment's command as the report datasource in the designer and remove that stinkin' DataEnvironment from the project! (You can still keep it around if you want to modify the report but it isn't necessary to be in the compiled version)

    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now