[RESOLVED] New To DataReport Designing

[computerman]

How do you transfer data from a form that accesses 2 different recordsets to a printed report using vb6 datareport.

ie rsCustomer and rsBookings
1 ------> many

rsBookings is a child of rsCustomer linked by Reference Number.

I have tried doing it via the dataenvironment, but it only gives me access to one record set, ie rsCustomer when I need access to both recordsets.

Any help would be appreciated.

Computerman.

[wes4dbt]

There are several ways, depending on the situation.

1. Use an "Inner Join" in the SQL statement of the DE
2. Add a "Child" command to the DE an set the "relation" value.
3. Create a new recordset from the two other recordsets and assign it as the reports DataSource at runtime.

hope this helps

[computerman]

Many thanks for that. I thought there might have been a more efficient way of transfering the data without having to create another recordset.

If I was to use the DE for my SQL statement, how would I pass the variable ReferenceNumber from the form to the SQL statement?

[wes4dbt]

This is a DE SQL example

select distinct mid(apn,1,6) as apnmap,apn from lots where mid(apn,1,6)>=? and mid(apn,1,6)<=? order by apn

The "?" is used to indicate a parameter. Use the "Parameter" tab to name them.

Then when you call the report

If deApnmap.Recordsets.Item(1).State = adStateOpen Then
            deApnmap.Recordsets.Item(1).Close
        End If
        deApnmap.Commands("Command1_Grouping").Parameters("param1").Value = frmUrange!txtStart
        deApnmap.Commands("Command1_Grouping").Parameters("param2").Value = frmUrange!txtEnd
        rptApnmap.Show

Hope this is clear enough, I just copied an example for one of my app's

[computerman]

Thank you very much for that as I can now filter a single record from both record sets successfully.

[computerman]

The problem that I am now having is that when I pass a different value for ReferenceNumber to the query and print the report, I get the previous set of data.

DataEnvironment1.Commands("Booking").Parameters("ReferenceNumber").Value = frmBookingForm!txtAccountNumber
DataReport1.Show

What is required so that I loose the previous data and get the new data into the report.

[wes4dbt]

Did you close the DE recordset before printing as I showed in the previous post? If so then please post the code.

If deLotsdouble.rslots.State = adStateOpen Then
deLotsdouble.rslots.Close
End If

[computerman]

Many thanks for that. I forgot to put that in. It works perfectly.