[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:
Re: New To DataReport Designing
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
Re: New To DataReport Designing
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:
Re: New To DataReport Designing
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
Re: New To DataReport Designing
Thank you very much for that as I can now filter a single record from both record sets successfully. :bigyello: :thumb:
Re: New To DataReport Designing
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:
Re: New To DataReport Designing
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
Re: New To DataReport Designing
Many thanks for that. I forgot to put that in. It works perfectly.
:) :) :)