Master Detail using Data Reports 
Author Message
 Master Detail using Data Reports

I'm trying to use create a simple Purchase Order page by using the built in
Data Report designer that comes with VB6 and I'm having a real hard time
displaying the data correctly.  Here is my SQL statement and it works it
correctly returns the values that I want.

strSQL = "SHAPE {SELECT o.PONumber, o.JobID, a.Name, a.Address1, a.Address2,
a.City, " & _
    " a.Province, a.PostalCode, s.Name, s.Address1, s.Address2, " & _
    " s.City, s.Province, s.Postal, o.OrderDate, o.ShippingMethod, o.Terms,
o.OrderID" & _
    " FROM Orders o, ASL a, Shipping s WHERE PONumber = " & Val(lblPONumber)
& "} " & _
    " AS ParentCMD APPEND ({SELECT OrderID , Quantity, Description,
UnitPrice " & _
    " FROM [OrderDetails] } AS ChildCMD RELATE OrderID TO OrderID ) AS
ChildCMD"

Next I dynamically bind the controls in the Data Report to the recordset
that I create from the above sql statement.
I have no problem with the binding the parent data to the controls but I'm
running into a problem with the child data.
There are 3 pieces of information for each order detail that I want to
display for now (I'll add Line total later) Quantity, Description and Unit
Price
The SQL command returns these in the Child recordset no problem
I've added 3 RptTextboxes to hold the details data.  Here is my code

I'v commented out the loop for now

'  Do While Not rstDetails.EOF
        .Item(intCnt).DataMember = ""
        .Item(intCnt).DataField = rstDetails.Fields("Quantity").Name
        intCnt = intCnt + 1

        .Item(intCnt).DataMember = ""
        .Item(intCnt).DataField = rstDetails.Fields("Description").Name
        intCnt = intCnt + 1

        .Item(intCnt).DataMember = ""
        .Item(intCnt).DataField = rstDetails.Fields("UnitPrice").Name
        intCnt = intCnt + 1
     ' Loop
intcnt is used to get the correct control since I can't find another way to
access a control so I loop through all the controls in the sections heading.
This works for the parent section so no reason why it shouldn't work for the
child section.

When I run this I get DataField Quantity not found.  The name of the field
is correct I know that and I can't understand why its telling me its not
found.

Second problem is if I get this to work this I still have a problem with the
loop after I add the loop I will have to dynamically add RptTextboxes to
accomodate all the items in Order Details.

Is it possible to do a Master Details report using the Data Report?  Is my
approach all wrong?  Any help is much appreciated.

Bryan



Thu, 10 Feb 2005 04:18:36 GMT  
 Master Detail using Data Reports
Have you tried using a Data Environment Designer and just define the
relationships in it.  Then you could let it build it for you and do the
summing of totals.  This would save you a lot of time.


Quote:
> I'm trying to use create a simple Purchase Order page by using the built
in
> Data Report designer that comes with VB6 and I'm having a real hard time
> displaying the data correctly.  Here is my SQL statement and it works it
> correctly returns the values that I want.

> strSQL = "SHAPE {SELECT o.PONumber, o.JobID, a.Name, a.Address1,
a.Address2,
> a.City, " & _
>     " a.Province, a.PostalCode, s.Name, s.Address1, s.Address2, " & _
>     " s.City, s.Province, s.Postal, o.OrderDate, o.ShippingMethod,
o.Terms,
> o.OrderID" & _
>     " FROM Orders o, ASL a, Shipping s WHERE PONumber = " &
Val(lblPONumber)
> & "} " & _
>     " AS ParentCMD APPEND ({SELECT OrderID , Quantity, Description,
> UnitPrice " & _
>     " FROM [OrderDetails] } AS ChildCMD RELATE OrderID TO OrderID ) AS
> ChildCMD"

> Next I dynamically bind the controls in the Data Report to the recordset
> that I create from the above sql statement.
> I have no problem with the binding the parent data to the controls but I'm
> running into a problem with the child data.
> There are 3 pieces of information for each order detail that I want to
> display for now (I'll add Line total later) Quantity, Description and Unit
> Price
> The SQL command returns these in the Child recordset no problem
> I've added 3 RptTextboxes to hold the details data.  Here is my code

> I'v commented out the loop for now

> '  Do While Not rstDetails.EOF
>         .Item(intCnt).DataMember = ""
>         .Item(intCnt).DataField = rstDetails.Fields("Quantity").Name
>         intCnt = intCnt + 1

>         .Item(intCnt).DataMember = ""
>         .Item(intCnt).DataField = rstDetails.Fields("Description").Name
>         intCnt = intCnt + 1

>         .Item(intCnt).DataMember = ""
>         .Item(intCnt).DataField = rstDetails.Fields("UnitPrice").Name
>         intCnt = intCnt + 1
>      ' Loop
> intcnt is used to get the correct control since I can't find another way
to
> access a control so I loop through all the controls in the sections
heading.
> This works for the parent section so no reason why it shouldn't work for
the
> child section.

> When I run this I get DataField Quantity not found.  The name of the field
> is correct I know that and I can't understand why its telling me its not
> found.

> Second problem is if I get this to work this I still have a problem with
the
> loop after I add the loop I will have to dynamically add RptTextboxes to
> accomodate all the items in Order Details.

> Is it possible to do a Master Details report using the Data Report?  Is my
> approach all wrong?  Any help is much appreciated.

> Bryan



Fri, 11 Feb 2005 01:07:47 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Master-Detail with 2 seperate detail sections per master record, CR.NET

2. Master/Detail Data report based on stored procedures...!

3. Inserting detail rows in master-detail configuration (VB4)

4. Reporting on Master / Detail Records?

5. A report with 2 detail tables and 1 master

6. master and 2 detail reports

7. master-detail report

8. Link multiple datagrids to a single record (Master\Detail Form with multiple details)

9. Data Form Wizard with Master/Detail Form-Layout

10. Master/Detail Data Grid

11. Data Form Wizard Master/Detail

12. Master Detail form using Visual Basic

 

 
Powered by phpBB® Forum Software