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.Append "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.Append "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.Append "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("Section4").Controls("lblA").Caption = "Text before tables"
.Sections("Section5").Controls("lblb").Caption = "Text after tables"


.Sections("Section1").Controls("txtA").DataMember = ""
.Sections("Section1").Controls("txtA").DataField = "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.connection
with cn
.Provider = "MsDataShape"
.Properties("Data Provider") = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "database path and name"
.Open
end with
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = "select * from table1"
End With
rst.CursorLocation = adUseServer
rst.Open cmd, , adOpenKeyset, adLockOptimistic, 8

with rst
if not (.eof and .bof) then
.movefirst
while not .eof
debug.print .fields(0).value
.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("txtGH_strTableID").DataM ember = ""
.Sections("GH").Controls("txtGH_strTableID").DataF ield = "strTableID"
'Bind Controls in Details Section
.Sections("Det").Controls("txtDet_strFld1").DataMe mber = "rstGrouped"
.Sections("Det").Controls("txtDet_strFld1").DataFi eld = "strFld1"
.Sections("Det").Controls("txtDet_strfld2").DataMe mber = "rstGrouped"
.Sections("Det").Controls("txtDet_strfld2").DataFi eld = "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

[smartchap]

Thanks Mr. Dhillon
I will try it and will tell you about outcome.

[smartchap]

Hi
As soon as F5 is pressed I get error 'Invalid Data Source' and can not debug the program because even if I use F8 soon I get the error without displaying the line of error.

[9815402440]

hi smartchap
for this use following code to open connection
with cnn
.Provider = "MsDataShape"
.Properties("Data Provider") = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = DataBasePath
.Open
end with
regards
manpreet singh dhillon hoshiarpur

[smartchap]

Already used the similar (or should say same) code, with different variables, but error. Only after that I asked u why that error. Ok, tomorrow I will again see what is wrong with my project.

[smartchap]

Dear Mr. Dhillon
Please see the attached files and help exact problem. As the error occurs even before the form is displayed, it means there is some error with datareport, but I could not find it.
Someone please help.

[9815402440]

hi smartchap
from the structure of the database if could not understand what data you want to retreive. to illustrate the logic i have combined tables and shown in report.

message 'invalid data source' appeared because you called rptTest on startup

i am attaching the modified project.
do not forget to change extention to .rar

regards
manpreet singh dhillon hoshiarpur

[smartchap]

Thanks Mr. Dhillon for quick and accurate help.
Actually I am very poor in SQL & DataReports. In last few days I have learnt a lot about DataReports and something about SQL. Before seeing reply from u, I changed SQL query a lot (with the helps got on net), but could not understand why report is opening before loading the form. Actually I didn't set it as startup, what I did was that I opened the report by u by double clicking, then added a form and pasted (& written) code in that. Due to this rptTest was set as startup which I could never imagine. Thanks for telling me the problem.
One more thing, will u please explain a little about sql query u wrote. Specially what is the use of SELECT 'Table 1' AS strTableID and similarly for Table 2.
Now I will try to modify the code as per my requirement and will let u know.

Thanks once again.
Hope zooming problem is solved as reqd by you.

[smartchap]

Use of strTableID is clear. My first table contains 4 fields, second 6 fields & third 7 fields (excluding IDs). How to show them in report with / without line borders around each field. Borders are required for only 4 fields for table1 output, for table2 around only 6 fields and around all 7 fields for table3. Please help me how to achieve this.

[narendersingh]

hi smart chap. I think dhillon has solved ur query.