DataReport with Two Tables

[smartchap]

Dear All

I want to have data from two tables to be shown on DataReport as the tables are formed. For clarity of my question I have attached a Word file. Please look at it and help me solve the problem.

[debasisdas]

you want the output of both the tables on a single report ?

i think it has something to do with page break.

[smartchap]

Thanks Debasisdas for taking interest. Actually I tried with various methods but could not secceed. I want output of both the tables in one report. I don't know about Page Break in DataReport. Can u plz tell me in detail about it & how to get required o/p.

Also is there any method to have two Details section in one report OR one report embedded inside other report, even then these things could be possible.

I can either use DataEnvironment with it or can get output programically. U can help me in either case. Programming one will be better because then data could be populated dynamically.

Thanks once again & hoping for an early reply.

[9815402440]

hi smartchap
what i got form the word file is that you want to display data form both tables. upper half of data report will show data from first table and lower half will show data from second table.
if this is the case, please let me know

regards
manpreet singh dhillon hoshiarpur

[smartchap]

Dear Mr. Dhillon

Yes actually I want the output in similar fashion as u understood. Besides these two tables o/p will have data from some labels, textboxes & combo boxes before as well as after the tables, i.e. some data before tables then tables then some data after tables.
Actually I have some data file with different ID for each record, for each ID have some data in two tables.
OK I am attaching a .zip file of .mdb file having three tabels. u can understand from this .mdb file.
If u help me for the problem as u understood, I think I could proceed further.
Thanks.

[9815402440]

hi
u may use following logic to show data from two tables . i have attached a sample datareport with this code

Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim rst3 As New ADODB.Recordset
''''''''''''''' ''''''''''''''' ''''''''''''''' ''''''''''''''' '''''''''''''
rst1.Fields.App end "a", adBSTR, , adFldIsNullable
rst1.Open
rst1.AddNew
rst1.Fields(0). Value = "Data from first table"
' in place of this block of code you will fetch data from first table
''''''''''''''' ''''''''''''''' ''''''''''''''' ''''''''''''''' '''''''''''''
rst2.Fields.App end "b", adBSTR, , adFldIsNullable
rst2.Open
rst2.AddNew
rst2.Fields(0). Value = "data From second table"
' in place of this block of code you will fetch data from second table
''''''''''''''' ''''''''''''''' ''''''''''''''' ''''''''''''''' '''''''''''''
'now add data from first table
rst3.Fields.App end "a", adBSTR, , adFldIsNullable
rst3.Open
rst1.MoveFirst
While Not rst1.EOF
rst3.AddNew
rst3.Fields(0). Value = rst1.Fields(0). Value
rst1.MoveNext
Wend

'following lines will separate data from both tables in the data report

rst3.AddNew
rst3.Fields(0). Value = "************** *************** *******"

'add data from second table
rst2.MoveFirst
While Not rst2.EOF
rst3.AddNew
rst3.Fields(0). Value = rst2.Fields(0). Value
rst2.MoveNext
Wend

'bind and show the report
With DataReport1
.DataMember = ""
Set .DataSource = rst3
.Sections("Sect ion4").Controls ("lblA").Captio n = "Text before tables"
.Sections("Sect ion5").Controls ("lblb").Captio n = "Text after tables"


.Sections("Sect ion1").Controls ("txtA").DataMe mber = ""
.Sections("Sect ion1").Controls ("txtA").DataFi eld = "a"
.Refresh
.Show
.SetFocus
End With


regards
manpreet singh dhillon hoshiarpur

[smartchap]

Thanks Mr Dhillon
Please attach file again as it is corrupt file. Also please tell me how to fetch data from tables.
Thanks once again.

[9815402440]

hi smartchap

actually attached file is not a .zip file but .rar file. because this site dont support .rar files so i changed the extention to .zip. to use this please change its extention to .rar.

use following code to fetch data from tables

dim cn as new adodb.connectio n
with cn
.Provider = "MsDataShap e"
.Properties("Da ta Provider") = "Microsoft.Jet. OLEDB.4.0"
.Properties("Da ta Source") = "database path and name"
.Open
end with
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
With cmd
.ActiveConnecti on = cn
.CommandType = adCmdText
.CommandText = "select * from table1"
End With
rst.CursorLocat ion = adUseServer
rst.Open cmd, , adOpenKeyset, adLockOptimisti c, 8

with rst
if not (.eof and .bof) then
.movefirst
while not .eof
debug.print .fields(0).valu e
.movenext
wend
endif
end with

there are some other ways to open recordset. but i usually open recordset this way.

regards
manpreet singh dhillon hoshiarpur

[smartchap]

Dear Mr. Dhillon
Thanks a lot for such a wonderful code and above all helping me with full code. I have modified the coe a lot as per my requirement.
I have drawn lines also around each textbox in detail section and now will try to hide border lines for last textbox for table1 since in Table 1 there are only 6 fields & in table2 there are 7 fields. So for displaying data for first table last textbox border will not be required.
This thing I will try & if required will ask u for help.
Thanks once again.

[smartchap]

Dear Debasis
Though Mr. Dhillon has provided me the solution but I think u may help me designing the DataReport, as I required, using Page Break. If possible tell me more about it as I will be happy to know about it as it will add to my knowledge.

Thanks.

[smartchap]

Dear Mr. Dhillon

Tried but could not succeed. I want to have data for both the tables separately, i.e. if I add a .AddNew line it will add a blank record. But as I am using rptLine lines around each textbox (in Detail Section) to get tabular look in output, a blank record which is added in between two tables is also having border lines, i.e. cells displayed. I want to hide / remove these lines for that blank record. Plz help.

Thanks.

[9815402440]

hi smartchap
sorry for the delay
please let me know if the problam is not resolved.
if not then i will suggest you the alternate.

regards
manpreet singh dhillon hoshiarpur

[smartchap]

Till date I could not resolve the problem. When I set the BorderStyle or BorderColor property of Line or Shape as 0 (Transparent / White) it reflects in whole report & not for some records / table. Please suggest me some method. With the help of ur code I have modified it a lot to suit my requirement and learned a lot. Now everyday I try to achieve solution for separate tables but could not succeed. Still I am trying it.
Thanks. Waiting for a fruitful suggestion.

[smartchap]

Dear Mr. Dhillon
One more thing. When I run program separately it works fine (except problem of borders) but when I use this subroutine in another (big) program, it shows report for previous entered ID? Not the current ID. Any help.

[9815402440]

hi smartchap
sorry again for the delayed replay

another solution for the problam:

make a union query for the two tables as illustrated

SHAPE {SELECT 'Table1' AS strTableID, strFld1, strfld2 FROM Table1 union all SELECT 'Table2' AS strTableID, strFld1, strfld2 FROM Table2} AS rstGrouped COMPUTE rstGrouped BY strTableID

to use shape command provider must be MsDataShape.

suppose your recordset is rstRpt

now bind the datareport following way


With rptTest
.DataMember = ""
Set .DataSource = rstRpt
.Sections("GH") .Controls("txtG H_strTableID"). DataMember = ""
.Sections("GH") .Controls("txtG H_strTableID"). DataField = "strTableID "
'Bind Controls in Details Section
.Sections("Det" ).Controls("txt Det_strFld1").D ataMember = "rstGrouped "
.Sections("Det" ).Controls("txt Det_strFld1").D ataField = "strFld1"
.Sections("Det" ).Controls("txt Det_strfld2").D ataMember = "rstGrouped "
.Sections("Det" ).Controls("txt Det_strfld2").D ataField = "strfld2"
On Error GoTo ErrHndlr
.Refresh
.Show
.SetFocus
End With

i am also attaching the sample report with this mail
please rename the test.zip to test.rar

secret is in the field strTableID. this field will have same value for all the records of table 1 and different value for all the records of table2

and we grouped the recordset using strTableID

enjoy..........

regards
Manpreet Singh Dhillon Hoshiarpur