Data Reports Breaking

Printable View

Jan 30th, 2007, 11:50 AM
melvin74

Data Reports Breaking
Very new to VB6 and here is what i am trying to achieve.

I am making a report with the data reports tool.

Here is some code
VB Code:
```
Private Sub cmdshow_Click()
'Create a RS
Dim RS2 As ADODB.Recordset
Set RS2 = New ADODB.Recordset
 
'Create a basic Sql string
Dim strSQL As String
strSQL = "SELECT * FROM msds where lastmodified > '2007-01-18' order by companyname"
 
'Open the Recordset
RS2.Open strSQL, "driver={MySQL ODBC 3.51 Driver};database=msds;Option=3;server=" & PathMySQL & ";uid=;pwd=;"
 
'Bind to the report
Set DataReport2.DataSource = RS2
 
'Preview the report
DataReport2.Show vbModal
 
'Cleanup
RS2.Close
Set RS2 = Nothing
```
I want to break when the company name changes.
For example sample data

COMPANYA CHEMICALA
COMPANYA CHEMICALB
COMPANYB CHEMICALA

I would want
COMPANYA CHEMICALA
COMPANYA CHEMICALB

And when i am done listing chemicals for that company start a new page
COMPANYB CHEMICALA

Hopefully i am clear enough. Thanks!!
Jan 30th, 2007, 12:18 PM
Hack

Re: Data Reports Breaking

Moved to reporting
Jan 30th, 2007, 12:39 PM
brucevde

Re: Data Reports Breaking
The only way to create a page break is to have a Group Header/Footer (set the ForcePageBreak property).

The only way to Group the report is to use a Hierarchical recordset created with the Shape command.
VB Code:
```
strSQL = "Shape {SELECT CompanyName FROM msds where lastmodified > '2007-01-18'} " & _
"Append ({Select CompanyName, ChemicalField From msds} As Details " & _
"Relate CompanyName To CompanyName)"
```
You also need to change your connection string to use the MSDataShape provider.
VB Code:
```
RS2.Open strSQL, "provider=MSDataShape;data provider={MySQL ODBC 3.51 Driver};database=msds;Option=3;server=" & PathMySQL & ";uid=;pwd=;"
```
Jan 30th, 2007, 01:34 PM
melvin74

Re: Data Reports Breaking
Ok that makes sense and I am a little closer.
I have an error on this line
VB Code:
```
RS2.Open strSQL, "provider=MSDataShape;data provider={MySQL ODBC 3.51 Driver};database=msds;Option=3;server=" & PathMySQL & ";uid=;pwd=;"
```
It tells me class is not registered. Perhaps there are some more objects i need to add to my references?
Quote:
Originally Posted by brucevde

The only way to create a page break is to have a Group Header/Footer (set the ForcePageBreak property).

The only way to Group the report is to use a Hierarchical recordset created with the Shape command.

VB Code:

strSQL = "Shape {SELECT CompanyName FROM msds where lastmodified > '2007-01-18'} " & _
"Append ({Select CompanyName, ChemicalField From msds} As Details " & _
"Relate CompanyName To CompanyName)"

You also need to change your connection string to use the MSDataShape provider.

VB Code:

RS2.Open strSQL, "provider=MSDataShape;data provider={MySQL ODBC 3.51 Driver};database=msds;Option=3;server=" & PathMySQL & ";uid=;pwd=;"
Jan 30th, 2007, 02:22 PM
melvin74

Re: Data Reports Breaking

OK i got it running better now.
By changing
data provider={MySQL ODBC 3.51 Driver};
over to driver={MySQL ODBC 3.51 Driver};

I am now breaking on companyname.
However if i try to put the field prodname in the detail line i get an error

Datafield(prodname) not found. I will slowly get this worked out!

By way msdsnum is the key in the file, do i need to incorporate that into the sql?

Re: Data Reports Breaking

Ok here is the most latest :)

VB Code:

Dim RS2 As adodb.Recordset
Set RS2 = New adodb.Recordset
Dim product As String
 
'Create a basic Sql string
Dim strSQL As String
'strSQL = "SELECT * FROM msds where lastmodified > '2007-01-18' order by companyname"
strSQL = "Shape {SELECT  msdsnum, CompanyName, prodname FROM msds where lastmodified > '2007-01-23' order by companyname} " & _
"Append ({Select msdsnum, CompanyName, prodname From msds} As Details " & _
"Relate msdsnum To msdsnum)"
 
'Open the Recordset
RS2.Open strSQL, "provider=MSDataShape;driver={MySQL ODBC 3.51 Driver};database=msds;Option=3;server=" & PathMySQL & ";uid=;pwd=;"
 
'Bind to the report
Set DataReport2.DataSource = RS2
 
'Preview the report
DataReport2.Show vbModal
 
 
'Cleanup
RS2.Close
Set RS2 = Nothing

I am very close, but its now printing on 3 pages. Not putting the top two together.

CompanyA
CompanyA chemical1

CompanyA
CompanyA Chemical2

CompanyB
CompanyB Chemical3

Jan 30th, 2007, 03:08 PM
brucevde

Re: Data Reports Breaking

How many records does this query return?

SELECT msdsnum, CompanyName, prodname FROM msds where lastmodified > '2007-01-23'

That will be the number of Groups in your report and if you are page breaking after a group the number of pages as well.
Jan 30th, 2007, 03:17 PM
melvin74

Re: Data Reports Breaking

This returns 3 records.
However I truly only want two groups.

I want page one to be
CompanyA

CompanyA chemical1
CompanyA Chemical2

and i want page 2 to be

CompanyB

CompanyB Chemical3

I want one page to contain all chemicals for that company.

Quote:

Originally Posted by brucevde

How many records does this query return?

SELECT msdsnum, CompanyName, prodname FROM msds where lastmodified > '2007-01-23'

That will be the number of Groups in your report and if you are page breaking after a group the number of pages as well.
Jan 30th, 2007, 03:20 PM
brucevde

Re: Data Reports Breaking
Don't use the msdsnum field. Since you have to create a Master/Detail from one table you will need to create the Master group based on the Company.
VB Code:
```
strSQL = "Shape {SELECT  Distinct CompanyName FROM msds where lastmodified > '2007-01-23} " & _
"Append ({Select CompanyName, prodname From msds} As Details " & _
"Relate CompanyName To CompanyName)"
```
Jan 30th, 2007, 03:31 PM
melvin74

Re: Data Reports Breaking
Quote:
Originally Posted by brucevde

Don't use the msdsnum field. Since you have to create a Master/Detail from one table you will need to create the Master group based on the Company.

VB Code:

strSQL = "Shape {SELECT Distinct CompanyName FROM msds where lastmodified > '2007-01-23} " & _
"Append ({Select CompanyName, prodname From msds} As Details " & _
"Relate CompanyName To CompanyName)"
The issue i have now is when i have a textbox in the detail section for prodname i get "datafield(prodname) not found"

:)
Jan 30th, 2007, 05:04 PM
brucevde

Re: Data Reports Breaking

When you have a Hierarchical recordset you basically have recordsets within recordsets. You need to specify which recordset contains the field you want to display. To do that you use the DataMember property of the Control. In your case you named the Child recordset Details, so set the DataMember property to Details.

The DataMember property of controls that display fields from the Master recordset can be left blank.
Jan 31st, 2007, 08:51 AM
melvin74

Re: Data Reports Breaking

That definitely gave me the desired results that i wanted!
Only weird behavior i see happening is it there are some records being reported that dont fall in the query date range but they are still making it through.

Thanks alot for all your help! Very helpful forum!

Quote:

Originally Posted by brucevde

When you have a Hierarchical recordset you basically have recordsets within recordsets. You need to specify which recordset contains the field you want to display. To do that you use the DataMember property of the Control. In your case you named the Child recordset Details, so set the DataMember property to Details.

The DataMember property of controls that display fields from the Master recordset can be left blank.