DataReport with Recordset


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: DataReport with Recordset

  1. #1
    Miroslav St. Jeliazkov Guest

    DataReport with Recordset

    Hi All,

    I don't know is this the right place for this question but I
    posted it on other servers and get no ansewr.

    So in my project I create a recordset for 3 tables with
    SELECT statement and I want to create a datareport from the
    recordset. Do I have to use DataEnvironment or I can do it
    without that.

    Actually I don't know how tocreate the Report. I read the
    MSDN but there its explained only for a table not for
    recordset.

    If anyone has an idea or solution please post it here



  2. #2
    Marko Guest

    Re: DataReport with Recordset


    Set con1 = New ADODB.Connection
    Set rs1 = New ADODB.Recordset

    con1.ConnectionString = CConnectStr
    con1.Provider = "MSDataShape"
    con1.CursorLocation = adUseClient
    con1.Open


    strSQL = "SHAPE {SELECT * FROM ...} AS cmdCommand1 APPEND ..." '
    you must use SHAPE commands for hierarhical recordsets

    rs.open strSQL ,con1, adOpenStatic, adLockReadOnly

    Set Report1.DataSource = rs1
    With Report.Sections("Section1")
    '.Controls("Text1").DataMember = "cmdCommand1"
    .Controls("Text1").DataField = "UID"
    .Controls("Text2").DataField = "F_NAME"
    .Controls("Text3").DataField = "L_NAME"
    .Controls("Text4").DataField = "SERNUM"
    .Controls("Label14").Visible = False
    .Controls("Label3").Caption = "Some Caption"
    End With

    With Report1.Sections("Section2")
    .Controls("Text103").DataMember = "cmdCommand2"
    .Controls("Text103").DataField = "DATE_SP"
    .Controls("Text104").DataMember = "cmdCommand2"
    .Controls("Text104").DataField = "TIME_SP"
    .Controls("Text105").DataMember = "cmdCommand2"
    .Controls("Text105").DataField = "LOCATION_SP"
    .Controls("Text106").DataMember = "cmdCommand2"
    .Controls("Text106").DataField = "SOME_FIED_NAME"
    End With

    Report1.Show
    Set rs1 = Nothing


  3. #3
    Miroslav St. Jeliazkov Guest

    Re: DataReport with Recordset

    Thanks Marko,

    I see your idea is great. I'll try it soon.

    Miro
    P.S. Thanks again

    "Marko" <mare.sink@mailcity.com> wrote in message
    news:3965b8ca$1@news.devx.com...
    >
    > Set con1 = New ADODB.Connection
    > Set rs1 = New ADODB.Recordset
    >
    > con1.ConnectionString = CConnectStr
    > con1.Provider = "MSDataShape"
    > con1.CursorLocation = adUseClient
    > con1.Open
    >
    >
    > strSQL = "SHAPE {SELECT * FROM ...} AS cmdCommand1 APPEND

    ...." '
    > you must use SHAPE commands for hierarhical recordsets
    >
    > rs.open strSQL ,con1, adOpenStatic, adLockReadOnly
    >
    > Set Report1.DataSource = rs1
    > With Report.Sections("Section1")
    > '.Controls("Text1").DataMember = "cmdCommand1"
    > .Controls("Text1").DataField = "UID"
    > .Controls("Text2").DataField = "F_NAME"
    > .Controls("Text3").DataField = "L_NAME"
    > .Controls("Text4").DataField = "SERNUM"
    > .Controls("Label14").Visible = False
    > .Controls("Label3").Caption = "Some Caption"
    > End With
    >
    > With Report1.Sections("Section2")
    > .Controls("Text103").DataMember = "cmdCommand2"
    > .Controls("Text103").DataField = "DATE_SP"
    > .Controls("Text104").DataMember = "cmdCommand2"
    > .Controls("Text104").DataField = "TIME_SP"
    > .Controls("Text105").DataMember = "cmdCommand2"
    > .Controls("Text105").DataField = "LOCATION_SP"
    > .Controls("Text106").DataMember = "cmdCommand2"
    > .Controls("Text106").DataField = "SOME_FIED_NAME"
    > End With
    >
    > Report1.Show
    > Set rs1 = Nothing
    >




  4. #4
    Miroslav St. Jeliazkov Guest

    Re: DataReport with Recordset

    Sorry Marco but the code is too complicated for me. I tried
    to filled the ... and to specify the strSQL and so on but I
    can't figure out what is cmdCommand1 standing for and what
    to do in Section2 with .DataMember= and .DataField

    The code now is

    ==Code begin===========
    Public con1 As ADODB.Connection
    Public rs1 As ADODB.Recordset

    Private Sub Form_Load()
    Set con1 = New ADODB.Connection
    Set rs1 = New ADODB.Recordset

    con1.CursorLocation = adUseClient
    con1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source = C:\Temp\Orders.mdb"



    strSQL = "SHAPE {SELECT * FROM OrderedParts" & _
    "WHERE OrderNumber = 5}" & _
    "AS cmdCommand1"
    'you must use SHAPE commands for hierarhical recordsets

    rs1.Open strSQL, con1, adOpenStatic, adLockReadOnly

    Set Report1.DataSource = rs1
    With Report1.Sections("Section1")
    .Controls("Text1").DataMember = "cmdCommand1"
    .Controls("Text1").DataField = "PartID"
    .Controls("Text2").DataField = "Quantity"
    .Controls("Label1").Caption = "Part Code"
    .Controls("Label2").Caption = "Quantity"
    End With

    With Report1.Sections("Section2")
    .Controls("Text103").DataMember = "cmdCommand2"
    .Controls("Text103").DataField = "DATE_SP"
    .Controls("Text104").DataMember = "cmdCommand2"
    .Controls("Text104").DataField = "TIME_SP"
    .Controls("Text105").DataMember = "cmdCommand2"
    .Controls("Text105").DataField = "LOCATION_SP"
    .Controls("Text106").DataMember = "cmdCommand2"
    .Controls("Text106").DataField = "SOME_FIED_NAME"
    End With

    Report1.Show
    Set rs1 = Nothing

    End Sub

    ===Code end==========
    When I run it it gives me an error: Invalid SQL statement,
    expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE. I
    don't get it!

    The hole idea is I have a table in which there are ordered
    parts by the users. It's like that:

    OrderNumber PartID Quantity
    1 0000001 12
    1 0000002 1
    1 0000015 10
    2 0000001 2

    I want to print in a datareport in Section1 only those which
    are OrderNumber = 1 (for example)
    From another table I have to get the name and the number of
    the client and to put them in Section4 as labels
    I use
    ADO "SELECT * FROM OrderedParts WHERE OrderNumber = " &
    intNumber
    for creating recordset with the PartID by OrderNumber.

    BUT I DON't KNOW HOW TO PUT ALL THE DATA IN THE DATAREPORT!

    If you have any time for it please help me! Or just direct
    me where to read more about that.
    Miro







  5. #5
    Joe Kendall Guest

    Re: DataReport with Recordset


    Can you please explain why you used cmdCommand1? Did you set it up in the
    Data Environment? This will help me with a problem I have had. Thanks!

    Joe

    "Miroslav St. Jeliazkov" <jeliaskoff@earthling.net> wrote:
    >Sorry Marco but the code is too complicated for me. I tried
    >to filled the ... and to specify the strSQL and so on but I
    >can't figure out what is cmdCommand1 standing for and what
    >to do in Section2 with .DataMember= and .DataField
    >
    >The code now is
    >
    >==Code begin===========
    >Public con1 As ADODB.Connection
    >Public rs1 As ADODB.Recordset
    >
    >Private Sub Form_Load()
    >Set con1 = New ADODB.Connection
    >Set rs1 = New ADODB.Recordset
    >
    > con1.CursorLocation = adUseClient
    > con1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    > "Data Source = C:\Temp\Orders.mdb"
    >
    >
    >
    > strSQL = "SHAPE {SELECT * FROM OrderedParts" & _
    > "WHERE OrderNumber = 5}" & _
    > "AS cmdCommand1"
    >'you must use SHAPE commands for hierarhical recordsets
    >
    > rs1.Open strSQL, con1, adOpenStatic, adLockReadOnly
    >
    > Set Report1.DataSource = rs1
    > With Report1.Sections("Section1")
    > .Controls("Text1").DataMember = "cmdCommand1"
    > .Controls("Text1").DataField = "PartID"
    > .Controls("Text2").DataField = "Quantity"
    > .Controls("Label1").Caption = "Part Code"
    > .Controls("Label2").Caption = "Quantity"
    > End With
    >
    > With Report1.Sections("Section2")
    > .Controls("Text103").DataMember = "cmdCommand2"
    > .Controls("Text103").DataField = "DATE_SP"
    > .Controls("Text104").DataMember = "cmdCommand2"
    > .Controls("Text104").DataField = "TIME_SP"
    > .Controls("Text105").DataMember = "cmdCommand2"
    > .Controls("Text105").DataField = "LOCATION_SP"
    > .Controls("Text106").DataMember = "cmdCommand2"
    > .Controls("Text106").DataField = "SOME_FIED_NAME"
    > End With
    >
    > Report1.Show
    > Set rs1 = Nothing
    >
    >End Sub
    >
    >===Code end==========
    >When I run it it gives me an error: Invalid SQL statement,
    >expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE. I
    >don't get it!
    >
    >The hole idea is I have a table in which there are ordered
    >parts by the users. It's like that:
    >
    >OrderNumber PartID Quantity
    > 1 0000001 12
    > 1 0000002 1
    > 1 0000015 10
    > 2 0000001 2
    >
    >I want to print in a datareport in Section1 only those which
    >are OrderNumber = 1 (for example)
    >From another table I have to get the name and the number of
    >the client and to put them in Section4 as labels
    >I use
    > ADO "SELECT * FROM OrderedParts WHERE OrderNumber = " &
    >intNumber
    >for creating recordset with the PartID by OrderNumber.
    >
    >BUT I DON't KNOW HOW TO PUT ALL THE DATA IN THE DATAREPORT!
    >
    >If you have any time for it please help me! Or just direct
    >me where to read more about that.
    >Miro
    >
    >
    >
    >
    >
    >



  6. #6
    m Guest

    Re: DataReport with Recordset

    this the way I have used my report at run-time:
    str is a SQL string I built at run-time, reqgen is defined to use "temp" as
    source. req is a QueryDef


    Set req = bdd.CreateQueryDef("temp", str)
    DoCmd.OpenReport "reqgen", acViewPreview
    bdd.QueryDefs.Delete ("temp")




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center