[RESOLVED] New To DataReport Designing

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. :confused:

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

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? :confused:

This is a DE SQL example

Code:

---

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

Code:

---

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

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

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.

Code:

---

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.

:confused:

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

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

:) :) :)