My report's record source is:
SELECT tbl1Grants.ID, tbl1Grants.GrantName, tbl1Grants.Logo, tbl1Grants.StartDate, tbl1Grants.EndDate, tbl1Grants.PublicBudget, tbl1Grants.PrivateBudget, tbl1Grants.VendorBudget, tbl1Grants.ECPublic, tbl1Grants.ECPrivate, tbl1Grants.ECVendor, tbl3ApprovedGrantClasses.ClassID, tbl1TrainingProviders.Type, tbl1TrainingProviders.Company, tbl1Classes.ClassName, tbl3ApprovedGrantClasses.CurMaxApprovedSeats FROM tbl1Classes INNER JOIN (tbl1TrainingProviders INNER JOIN (tbl1Grants INNER JOIN tbl3ApprovedGrantClasses ON tbl1Grants.ID = tbl3ApprovedGrantClasses.GrantID) ON tbl1TrainingProviders.ProviderID = tbl3ApprovedGrantClasses.TrainerID) ON tbl1Classes.ClassID = tbl3ApprovedGrantClasses.ClassID WHERE (((tbl1Grants.ID)=[Forms]![frmMain]![cboGrant]));
It all works perfectly at the moment, except that I cannot get it to display the information for txtUsedSeats under each class (which is displayed in the "Detail" area). Is there a way to have it loop for each line that is displayed after that?
Here is the code I have, which steps through fine, but only performs once and sets all of the results the same.
Code:strClassName = [Reports]![rptGrantStatusWIP]![ClassName] strClassID = DLookup("ClassID". "tbl1Classes", "ClassName= '" & strClassName & "'")Code:strSeatCount = DCount("StudentID", "tbl2ClassTaken", "ClassID= " & strClassID) Reports!rptGrantStatusWIP!txtUsedSeats = strSeatCount If IsNull(Reports!rptGrantStatusWIP!txtUsedSeats) Then Reports!rptGrantStatusWIP!txtUsedSeats = 0 End If
* I notice that the single search that it is performing is the last result that is displayed.
* I have added a count field into the footer of one of the sections and it is now properly getting the seat count that I was looking for. But now it does not display all approved classes. It is only listing ones that have a value in the count.
* It seems as if when I add tbl2ClassTaken to the record source I no longer get all records returned.
* Without adding it to the record source, I cannot get the count to work properly.
* Moving the code to the "On Paint" event either for detail or for the group header the report is not displayed correctly.
* Moving the code to the "On Format" event for the detail and the group head both have no change from keeping it on the "Report Load" event
This one is stumping me....any help would be greatly appreciated.
EDIT: For some reason the code isnt getting put into one code box. Even retyped it here by itself. Strange