Updating of Group Header

Posted by: pete-newman on 4 August 2017, 2:47 pm EST

  • Posted 4 August 2017, 2:47 pm EST

    Im proberly trying to go about the the completly wrong way. I am running a complex SQL query through an ADO datacontrol.


    What im trying to do is count the 3 different values that may appear in field 128 and then update the fields in the groupheader2. Im tryingb the code below but it is not updating the report. Due to the complext nature of the query i can not do an add from within the query to get the results .....  any suggestions ?


     


    Dim  RTNCount as integer
    Dim   AMDCount as integer
    Dim  REJCount as integer


    Private Sub Detail_Format()
      If Field128.Text = "RETURNED RECORD" Then
         RTNCount = RTNCount + 1
      End If
      If Field128.Text = "AMENDED RECORD" Then
         AMDCount = AMDCount + 1
      End If
      If Field128.Text = "REJECTED RECORDS" Then
         REJCount = REJCount + 1
      End If
     
    End Sub


    Private Sub GroupHeader2_Format()
         Field97.Text = RTNCount
         Field96.Text = AMDCount
         Field98.Text = REJCount
    End Sub

  • Replied 4 August 2017, 2:47 pm EST

    You could add three fields to your fields collection in your DataInitialize to be pseudo counters. Then in your FetchData event do similar checking to your example Detail Format and set the appropriate counter to 1 and the others to 0. Setup your counter display textboxes in your groupheader with a summary function to sum with the pseudo counter fields as DataFields and the appropriate settings to limit per group or whatever you need.
  • Replied 4 August 2017, 2:47 pm EST

    A couple of things...


    1) You should never access controls your FetchData event, the Fields collection and/or variables only. So please replace Field128.Text with the appropriate recordset field and syntax...Fields("datafield").Value


    2) The summary function should take care of all the adding. All you need to do is put a 1 in the correction field (0s in the other two), no mannual counting should be neccessary.


    RTNCount = 0
    AMDCount = 0
    REJCount = 0


    Select Case Fields("datafield").Value
       Case "RETURNED RECORD"
           RTNCount = 1
       Case "AMENDED RECORD"
           AMDCount = 1
       Case "REJECTED RECORDS"
           REJCount = 1
    End Select


     

  • Replied 4 August 2017, 2:47 pm EST

    ok im deffo lost here now.  I have to do a manual count on Fields("datafield").value as it may be more than 1 item ie here may be 3 returned records.


     


    I have the following as per your guidiance


    Private Sub ActiveReport_Initialize()
     Fields.Add "COUNTRETURNED"
     Fields.Add "COUNTAMENDED"
     Fields.Add "COUNTREJECTED"
    End Sub


    Private Sub ActiveReport_FetchData(EOF As Boolean)
     
        RTNCount = 0
        AMDCount = 0
        REJCount = 0
       
      Select Case Fields("AMENDErrorItemErrorItemType").Value
       Case "RETURNED RECORD"
        RTNCount = RTNCount + 1
       Case "AMENDED RECORD"
        AMDCount = AMDCount + 1
       Case "REJECTED RECORDS"
        REJCount = REJCount + 1
      End Select
      
    End Sub


    ok that bit i follow easily enough,  but what i dont get is how i get the info from


        RTNCount 
        AMDCount
        REJCount 


     


    into  Field1, Field2 and Field3 in the group2header ?


     


     

  • Replied 4 August 2017, 2:47 pm EST

    3) What happened to Field96, Field97, and Field98? Whatever your summary fields are named in your group header, they should have their DataField properties set to those three additions to your Field collection created in your DataInitialize with the appropriate summary functions. What are your SummaryGroup, SummaryRunning, and SummaryType settings for the three textbox controls?


    4) To get the data from you count values you still need the following code in your FetchData, I only posted what I believed need to be changed or added.


    Fields("COUNTRETURNED").Value = RTNCount
    Fields("COUNTAMENDED").Value = AMDCount
    Fields("COUNTREJECTED").Value = REJCount


    5) You do not need to do a manual addition on your count variables as that is what the summary function is for...assume we have six records, 2 of each of your category.


    Using summary function with only placing a bit, 1 or 0 in your counters for each record.
    RTN AMD REJ
    -----------------
    1       0       0
    0       0       1
    1       0       0
    0       0       1
    0       1       0
    0       1       0
    -----------------
    2       2       2


    Using summary function in addition to manually counting the fields will result in this, which is basically compounding.
    RTN AMD REJ
    -----------------
    1       0       0
    1       0       1
    2       0       1
    2       0       2
    2       1       2
    2       2       2
    -----------------
    10     3       8

  • Replied 4 August 2017, 2:47 pm EST

    thanks.   its all clear now ....


     


    boy do i feel foolish

  • Replied 4 August 2017, 2:47 pm EST

    Your welcome.


    More wisdom for next time you have, yes.

  • Replied 4 August 2017, 2:47 pm EST

    I tried your suggestion and must be doing something wrong because the group header is still not getting updated.


    I have included the code in the Fetch data and the subsequent debug info


     


    Private Sub ActiveReport_FetchData(EOF As Boolean)
      Select Case Field128.Text
       Case "RETURNED RECORD"
        RTNCount = RTNCount + 1
       Case "AMENDED RECORD"
        AMDCount = AMDCount + 1
       Case "REJECTED RECORDS"
        REJCount = REJCount + 1
      End Select
     
     Fields("COUNTRETURNED").Value = RTNCount
     Fields("COUNTAMENDED").Value = AMDCount
     Fields("COUNTREJECTED").Value = REJCount
      
     Debug.Print "RTNCount = " & RTNCount
     Debug.Print "AMDCount = " & AMDCount
     Debug.Print "REJCount = " & REJCount
     
    End Sub


    Debug Info


    RTNCount = 0
    AMDCount = 0
    REJCount = 0
    RTNCount = 1
    AMDCount = 0
    REJCount = 0


    In the Group header 2 i have three text boxes,  one ( Field97 ) datafield is set to   COUNTRETURNED and its Summary Func is set to 0-ddSFSum  yet it still show 0, where i would expct it to be set to 1 in this case. Any ideas


     

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels