Sorry I didn't get back to you sooner, but I was traveling last week. Below is the code in a procedure which prepares all the data for the Report. I now notice that when the number of records in the detail band exceeds one page (12 records per page), that the next page repeats the last record on page 1 and then prints no further records. The 'gnTotalRooms' also then reflects the erroneous amount [13] instead of the true amount of records [20].
You will note a 'IF glTestMode = .T.' statement which allows me to confirm that the correct data is being retrieved, and all checks out at this point, yet it doesn't print.
I am not using a 'Private DS' in the Report and there are no tables in the data environment. All data comes either from the SQL statement below or from a FUNCTION call.
I have a FORM which allows the user to select the 'gcRmBlock' from a dropdown list and set other parameters. This FORM then calls the PROCEDURE below and afterwards runs the Report. Nothing is prepared in the Report itself.
The 'gnHotelCount' calls a second Report if the number of Hotels is greater than one, which wouldn't fit on the initial Report.
In the Report, each FIELD is addressed with the ALIAS (tmpConf, tmpAddr, tmpHotels) as applicable to avoid same field name conflicts.
Thanks in advance for any ideas you can send my way to resolve this issue. It's been very frustrating. I did better using FILTERS instead of SQL and am tempted to go back to them.
Adam
==========
*PROCEDURE prepconf_block
SELECT reserv.id_reserv, reserv.arvl, reserv.dept, reserv.no_people, ;
reserv.rmblock, reserv.cost_total, reserv.baserate, reserv.conf_no, ;
reserv.id_category, reserv.auto_pu, reserv.auto_do, reserv.auto_totaldue, ;
reserv.auto_totaldue, reserv.grade, reserv.size, reserv.madeby, ;
reserv.guest_1, reserv.guest_2, reserv.guest_3, reserv.guest_4, ;
reserv.id_group, reserv.id_hotel, ;
names.fname, names.lname, names.salut, names.mname, names.id_address, ;
states.state, ;
autos.car_category, autos.car_desc, autos.rate_day, autos.rate_week, ;
hotels.name_short, ;
addresses.add1, addresses.add2, addresses.city, addresses.id_state, ;
addresses.zip, addresses.country ;
FROM reserv ;
LEFT JOIN names ON names.id_system = reserv.guest_1 ;
LEFT JOIN addresses ON addresses.id_address = names.id_address ;
LEFT JOIN states ON states.id_state = addresses.id_state ;
LEFT JOIN hotels ON hotels.id_hotel = reserv.id_hotel ;
LEFT JOIN autos ON autos.id_category = reserv.id_category ;
WHERE (reserv.event_code = gcCurEvent ;
AND reserv.rmblock = gcRmBlock ;
AND reserv.cancel = {} ;
AND !DELETED()) ;
ORDER BY reserv.id_reserv ;
INTO CURSOR tmpConf
gnTotalRooms = _TALLY
SELECT tmpConf
IF glTestMode = .T.
BROWSE
ENDIF
* obtain the mailing address for the first (primary) record.
* this mailing address get printed in the Report Header on each page.
GO TOP
LOCAL lnAddrID
lnAddrID = tmpConf.id_address
SELECT addresses.add1, addresses.add2, addresses.city, addresses.id_state, addresses.zip, ;
states.state, states.country ;
FROM addresses ;
LEFT JOIN states ON states.id_state = addresses.id_state ;
WHERE addresses.id_address = lnAddrID ;
INTO CURSOR tmpAddr
IF glTestMode = .T.
BROWSE
ENDIF
* select all Hotel data for each hotel with reservations
* for this 'gcRmBlock' data set.
SELECT hotels.name_long, hotels.add1, hotels.add2, hotels.city, ;
hotels.state, hotels.zip, hotels.country, hotels.phone, ;
hotels.fax_guest, hotels.chg_maid, hotels.name_short, ;
hotels.tx_state, hotels.tx_city, hotels.tx_hotel, hotels.tx_misc ;
FROM hotels ;
WHERE hotels.id_hotel IN (SELECT DISTINCT tmpConf.id_hotel FROM tmpConf) ;
INTO CURSOR tmpHotels
IF glTestMode = .T.
BROWSE
ENDIF
gnHotelCount = _TALLY
SELECT tmpConf
GO TOP
Adam J Skowronski