DataReport with Recordset

[Miroslav St. Jeliazkov]

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

[Marko]

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

[Miroslav St. Jeliazkov]

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
>

[Miroslav St. Jeliazkov]

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

[Joe Kendall]

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
>
>
>
>
>
>

[m]

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")