Data Reports Breaking

[melvin74]

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

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

[Hack]

Moved to reporting

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

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.

RS2.Open strSQL, "provider=MSDataShape;data provider={MySQL ODBC 3.51 Driver};database=msds;Option=3;server=" & PathMySQL & ";uid=;pwd=;"

[melvin74]

Ok that makes sense and I am a little closer.
I have an error on this line

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?

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.

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.

RS2.Open strSQL, "provider=MSDataShape;data provider={MySQL ODBC 3.51 Driver};database=msds;Option=3;server=" & PathMySQL & ";uid=;pwd=;"

[melvin74]

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?

[melvin74]

Ok here is the most latest

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

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

[melvin74]

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.

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.

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

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

[melvin74]

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.

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"

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

[melvin74]

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!

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.