Results 1 to 4 of 4
  1. #1
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130

    Exclamation Report Count Issue ... Trying to get a count of seats used and cannot get report to work properly.

    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
    Last edited by June7; 01-13-2016 at 12:16 PM. Reason: code correction

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,506
    Yes, that is odd, forum throws in extra CODE tags. I also tried to fix.

    Code that sets value of unbound textbox will display the same value in all records because there is only one textbox.

    Textbox would have to call a function that returns a value based on some dynamic input to the function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Voodeux2014 is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Nov 2014
    Location
    Slidell, LA
    Posts
    130
    Well I have this crossposted on another forum as well.

    I seemed to have got it working by moving the code to the OnPaint event of the detail section.

    But now it is stuck in an ugly horrible refresh rate flickr. I have tried using the DoCmd.Echo False/True around the code, but it does not change it.

    Code:
        strClassName = [Reports]![rptGrantStatusWIP]![ClassName]    strClassID = DLookup("ClassID", "tbl1Classes", "ClassName= '" & strClassName & "'")
        intSeatCount = DCount("StudentID", "tbl2ClassTaken", "ClassID= " & strClassID)
        strSeatCount = CStr(intSeatCount)
        Me.txtUsedSeats = strSeatCount
            If IsNull(Reports!rptGrantStatusWIP!txtUsedSeats) Then
              Me.txtUsedSeats = 0
            End If
    Last edited by Voodeux2014; 01-13-2016 at 12:31 PM. Reason: new code

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    51,506
    I've never used OnPaint. I would use function.

    Or maybe put the DLookup and DCount() into a textboxes on report.

    No VBA code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. COUNT Group Count Records COUNT FUNCTION
    By PMCOFFEY in forum Access
    Replies: 9
    Last Post: 11-09-2012, 09:40 PM
  2. =Count in SQL Report
    By Brian62 in forum SQL Server
    Replies: 17
    Last Post: 06-19-2012, 05:15 PM
  3. Count or DCount Not Working Properly
    By Chris1112 in forum Forms
    Replies: 3
    Last Post: 05-09-2012, 02:51 PM
  4. count & sum in report
    By ali zaib in forum Access
    Replies: 1
    Last Post: 01-16-2012, 02:35 PM
  5. Count and Sum in Report
    By Brian62 in forum Reports
    Replies: 3
    Last Post: 02-19-2010, 04:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums