We help IT Professionals succeed at work.

VSFlexGrid

Aquarus
Aquarus asked
on
1,571 Views
Last Modified: 2012-05-08
I am using VSFlexGrid SubtotalPosition and Subtotal properity to show the subtotal after the grid is populated.
It works all the time besides when I filtering the grid.
When I run FilterIn the code goes over the same line where I am using the propery SubtotalPosition and Subtotal, but does not show subtotal.
I am suprised because the processes are seems similar to me - populate the grid - show subtotal, refresh the grid - show subtotal. Filter the grid by the value - show subtotal.
Where I don't undesrtand the property?

This is part of the code:
  Me.fgDetail.SubtotalPosition = flexSTBelow
        Me.fgDetail.Subtotal flexSTSum, 2, CD_AMOUNT_COL
Let me know if i need to supply more details.
Comment
Watch Question

Hi

Please send code that you use to populate, filter and subtotal grid.

Author

Commented:
northfields:
I have compiled code as you asked.  I included my comments over there that explain the logic of my compilation  If you search for Aquarus name - you'll find all my comments.
Thank you for stepping in.

I hope you will be able to copy it over in a more visible layout
Public Sub InitializeForm(ByVal strContractCode As String, ByVal lngUnitID As Long, _
                            ByVal strUnitCode As String, ByVal strPropertyName As String, _
                            Optional dtVoucherDate As Date, Optional lngVoucherID As Long)
 
 
100	InitUnitGrid - this is described below under 100*.(Aquarus)
 
 
 
170       Set objVoucher = CreateObject(BLLTN_VOM_VOUCHER)
180       mlngMonth = Me.txtFilterMonthBack
190       Set rsVoucher = objVoucher.ListUnitPayments2(mstrServerName, _
                        mstrContractCode, mlngUnitID, mlngMonth, mlngVoucherID, mdtVoucherDate)
 
200       AddRowsToGrid rsVoucher - this function populate the grid, also described below under 200*(Aquarus)
 
        
 
         Me.fgDetail.SubtotalPosition = flexSTBelow   ---This is where the Subtotal is shown the first time and it is working
         Me.fgDetail.Subtotal flexSTSum, 2, CD_AMOUNT_COL
------------------------------------
 
100* - grid initializing(Aquarus)
 
Private Sub InitUnitGrid()
          Dim intCol As Integer
          Dim intRow As Integer
          
10        On Error GoTo InitUnitGrid_ErrPara
 
20        intCol = 0
30        intRow = 0
        
              
40        With fgDetail
50            .Cols = CD_NUM_COLS
60            .Row = intRow
70            .FixedCols = 1
80            For intCol = 0 To .Cols - 1
90                .Col = intCol
100               Select Case intCol
                  Case CD_FIXED_COL
110                 .ColWidth(intCol) = 200
                    .ColHidden(intCol) = True
                  Case CD_VOUCHER_ID_COL
                    .ColWidth(intCol) = 200
                    .Text = "Voucher ID"
                    .ColHidden(intCol) = True
120               Case CD_CONTRACT_CODE_COL
130                   .ColAlignment(intCol) = flexAlignCenterBottom
140                   .FixedAlignment(intCol) = flexAlignCenterBottom
150                   .Text = "Contract Code"
160                   .ColWidth(intCol) = 1300
                      .ColHidden(intCol) = True
                  Case CD_VOUCHER_DATE_COL
1170                  .ColAlignment(intCol) = flexAlignRightBottom
1180                  .FixedAlignment(intCol) = flexAlignRightBottom
1190                  .Text = "          Voucher Date"
                      .WordWrap = True
                      .ColWidth(intCol) = 1500 '2000
1200                  .ColDataType(intCol) = flexDTDate
1210                  .ColFormat(intCol) = "mm/dd/yyyy"
                  Case CD_UNIT_ID_COL
                      .ColWidth(intCol) = 200
                      .Text = "Unit ID"
                      .ColHidden(intCol) = True
                  Case CD_UNIT_NUMBER_COL
230                   .ColAlignment(intCol) = flexAlignCenterBottom
240                   .FixedAlignment(intCol) = flexAlignCenterBottom
250                   .Text = "Unit Number"
260                   .ColWidth(intCol) = 1500
                  Case CD_BUILDING_ID_COL
                      .ColWidth(intCol) = 200
                      .Text = "Unit ID"
                      .ColHidden(intCol) = True
                    Case CD_TENANT_NAME_COL
261                  .ColAlignment(intCol) = flexAlignLeftBottom
262                   .FixedAlignment(intCol) = flexAlignLeftBottom
263                   .Text = "Tenant Name"
264                   .ColWidth(intCol) = 2000
                   Case CD_CERTCODE_COL
980                   .ColAlignment(intCol) = flexAlignLeftBottom
990                   .FixedAlignment(intCol) = flexAlignLeftBottom
1000                  .Text = "Cert.Code"
1010                  .ColWidth(intCol) = 1000
                   Case CD_AMOUNT_COL
570                   .ColAlignment(intCol) = flexAlignRightBottom
580                   .FixedAlignment(intCol) = flexAlignRightBottom
590                   .Text = "Amount"
600                   .ColWidth(intCol) = 1500
                      .ColFormat(intCol) = CURRENCY_DECIMAL_FORMAT
 
                   Case CD_VOUCHER_SECTION_COL
740                   .ColAlignment(intCol) = flexAlignLeftBottom
750                   .FixedAlignment(intCol) = flexAlignLeftBottom
760                   .Text = "Voucher Section"
765                   .ColWidth(intCol) = 2000 '3000
 
                   Case CD_CERT_EFF_DATE_COL
1171                  .ColAlignment(intCol) = flexAlignRightBottom
1181                  .FixedAlignment(intCol) = flexAlignRightBottom
1191                  .Text = "           Effective Date"
                      .ColWidth(intCol) = 1500 '2000
1201                 .ColDataType(intCol) = flexDTDate
1211                  .ColFormat(intCol) = "mm/dd/yyyy"
 
                    Case CD_ADJ_START_DATE_COL
1173                  .ColAlignment(intCol) = flexAlignRightBottom
1183                  .FixedAlignment(intCol) = flexAlignRightBottom
1193                  .Text = "  Adjustment Begining Date"
                      .ColWidth(intCol) = 1500 '2000
1203                 .ColDataType(intCol) = flexDTDate
1213                  .ColFormat(intCol) = "mm/dd/yyyy"
 
                    Case CD_ADJ_END_DATE_COL
1172                  .ColAlignment(intCol) = flexAlignRightBottom
1182                  .FixedAlignment(intCol) = flexAlignRightBottom
1192                  .Text = "  Adjustment Ending Date"
                      .ColWidth(intCol) = 1500 '2000
1202                 '.ColDataType(intCol) = flexDTDate
                      .ColDataType(intCol) = flexDTString
1212                 .ColFormat(intCol) = "mm/dd/yyyy"
                    Case FG_STATUS_COL
                        .ColHidden(intCol) = True
 
1420              End Select
1430          Next intCol
1440      End With
1450      fgDetail.Rows = 1
          
1460     
         
1470      Exit Sub
 
InitUnitGrid_ErrPara:
 
 
End Sub
 
-----------------------------------------------------
200* (Populating the grid the first time and every refresh - Aquarus)
 
Private Sub AddRowsToGrid(ByVal irsRows As ADODB.Recordset)
          Dim intAnswer           As Integer
          Dim strPrevUnitNumber   As String
          Dim strPrevUnitID       As String
          Dim strCertTypeCode     As String
          Dim strCurUnitNumber    As String
          Dim strDirection        As String
          Dim strCertTypeInfo     As String
          Dim strUnitTrafer       As String
'          Dim lTotalPaid          As Long
          
10        On Error GoTo AddRowsToGrid_ErrPara
 
20        While fgDetail.Rows + irsRows.RecordCount > MAX_GRID_ROWS
30            intAnswer = ShowMessageBox("Adding specified rows will cause grid to exceed the maximum number of rows allowed. Do you wish to clear grid and add selected rows?", cometsYesNo, cometsInformation, "Exceeds Max Rows")
40            If intAnswer = CMD_YES Then
50                fgDetail.Rows = 1
60            Else
 
70                Exit Sub
80            End If
90        Wend
          
'        lTotalPaid = 0
        
100       While Not irsRows.EOF
 
 
''370           If Not InGrid(Trim$(irsRows(COL_CERT_ID).Value), strCertTypeInfo) Then
380               With fgDetail
390                 .Rows = fgDetail.Rows + 1
                    .TextMatrix(.Rows - 1, CD_VOUCHER_DATE_COL) = Trim$("" & irsRows(COL_VOUCHER_MONTH).Value)
                    .TextMatrix(.Rows - 1, CD_UNIT_NUMBER_COL) = Trim$("" & irsRows(COL_UNIT_NUMBER).Value)
                    .TextMatrix(.Rows - 1, CD_TENANT_NAME_COL) = Trim$("" & irsRows(COL_TENANT_NAME).Value)
                    .TextMatrix(.Rows - 1, CD_AMOUNT_COL) = Trim$("" & "$" & irsRows(COL_PAYMENT_CA).Value)
                    .TextMatrix(.Rows - 1, CD_VOUCHER_SECTION_COL) = Trim$("" & irsRows(COL_VOUCHER_SECTION).Value)
                    .TextMatrix(.Rows - 1, CD_CERTCODE_COL) = Trim$("" & irsRows(COL_CERT_TYPE_CODE).Value)
                    .TextMatrix(.Rows - 1, CD_CONTRACT_CODE_COL) = Trim$("" & irsRows(COL_CONTRACT_CODE).Value)
                    .TextMatrix(.Rows - 1, CD_CERT_EFF_DATE_COL) = Trim$("" & irsRows(COL_CERT_EFF_DATE).Value)
''                    .TextMatrix(.Rows - 1, CD_ADJ_START_DATE_COL) = Trim$("" & irsRows(COL_ADJ_START_DATE).Value)
''                    .TextMatrix(.Rows - 1, CD_ADJ_END_DATE_COL) = Trim$("" & irsRows(COL_ADJ_END_DATE).Value)
                    .TextMatrix(.Rows - 1, CD_ADJ_START_DATE_COL) = Trim$("" & IIf(irsRows(COL_ADJ_START_DATE).Value = "1/1/1900", "", irsRows(COL_ADJ_START_DATE).Value))
                    .TextMatrix(.Rows - 1, CD_ADJ_END_DATE_COL) = Trim$("" & IIf(irsRows(COL_ADJ_END_DATE).Value = "1/1/1900", "", irsRows(COL_ADJ_END_DATE).Value))
                    .TextMatrix(.Rows - 1, FG_STATUS_COL) = ROW_ADDED
                  
                  
                
660             End With
''670           End If
680           lblCaptionBar.Caption = GridRowCount(fgDetail) & " item(s) listed."
              
                
681           'lTotalPaid = lTotalPaid + Trim$("" & "$" & irsRows(COL_PAYMENT_CA).Value)
              
690           irsRows.MoveNext
700       Wend
 
            'Me.txtTotalPaid.Text = FormatCurrency(lTotalPaid, 2, vbTrue, vbTrue, vbTrue)
            
710       FormatGrid_V2 fgDetail, 480
720       If fgDetail.Rows > 1 Then
730           fgDetail.Select 1, 1
740           fgDetail.ShowCell 1, 1
750       End If
          
760       Exit Sub
 
AddRowsToGrid_ErrPara:
770       Screen.MousePointer = vbDefault
 
780       If Left(Err.Source, DALTN_SIZE) <> DAL And _
             Left(Err.Source, BLLTN_SIZE) <> BLL And _
             Left(Err.Source, UI_SIZE) <> UI Then
790           Err.Source = "Line Number:" & Erl & " " & ERROR_SOURCE & ".AddRowsToGrid"
800       End If
          
810       Select Case RaiseError(Err.Number, Err.Source)
              Case ERR_RESUME
820               Resume
830           Case ERR_RESUME_NEXT
840               Resume Next
850           Case Else
860               Exit Sub
870       End Select
 
End Sub
 
UP TO THIS POINT THE CODE AND SUBTOTAL PROPERTY OF VSFLEXGRID WORKS - Aquarus
======================================================================
DOWN BELOW WHERE IT DOES NOT: (Aquarus)
 
Private Sub cmdFilterIn_Click() - this is the button(Aquarus)
10        On Error GoTo cmdFilterIn_Click_ErrPara
 
          Dim iRow As Integer
 
20        Screen.MousePointer = vbHourglass
30        FilterClick fgDetail   -  Aquarus (this is the function that actually filtering) It will be 
          shown down below under 30* (Aquarus)
 
          fgDetail.SubtotalPosition = flexSTBelow  -this is where the property does not seem to work(Aquarus)
          fgDetail.Subtotal flexSTSum, 2, CD_AMOUNT_COL
          
          lblCaptionBar.Caption = "FILTER ON " & GridRowCount(fgDetail) & " of " & fgDetail.Rows - 1 & " item(s) listed."
 
          If GridRowCount(fgDetail) <> fgDetail.Rows - 1 Then
              lblCaptionBar.Caption = "FILTER ON " & GridRowCount(fgDetail) & " of " & fgDetail.Rows - 1 & " item(s) listed."
          Else
              lblCaptionBar.Caption = GridRowCount(fgDetail) & " item(s) listed."
          End If
 
       
50        Screen.MousePointer = vbDefault
 
60        Exit Sub
 
cmdFilterIn_Click_ErrPara:
70        Screen.MousePointer = vbDefault
 
End Sub
 
-----------------------------------------------------
30*
Public Sub FilterClick(ByVal ifgGrid As VSFlexGrid)
10        On Error GoTo FilterClick_ErrPara
 
20        With ifgGrid
30            If .Row >= .FixedRows Then
                  'Check if Filter already Exists
40                If Not FilterAlreadyExists(.Col, .Tag) Then
50                    .Tag = .Tag & .TextMatrix(.Row, .Col) & DELIMITER_PLUS & .Col & DELIMITER_PIPE
60                    FilterGrid ifgGrid, .Tag - (Aquarus) - this function is shown down below under 60* 
70                End If
80            End If
90        End With
 
100       Exit Sub
 
-------------------------------------------------------
60*
Public Function FilterGrid(ByRef GrdName As VSFlexGrid, _
                            ByVal istrFilterValue As String)
          ' This purpose of this function is to accept a filter data
          ' records being displayed in a grid
          
          Dim iRow As Integer
          Dim intStart As Integer
          Dim intEnd As Integer
              
          Dim Tintstart As Integer
          Dim Tintend As Integer
          Dim strTemp As String
          Dim strTempValue As String
          Dim strTempCol As String
          
          'If there is no filter to be applied
10        On Error GoTo FilterGrid_ErrPara
 
20        If istrFilterValue = ZERO_LENGTH_STRING Then
30            For iRow = 1 To GrdName.Rows - 1
                  'loop to get all the values
40                GrdName.RowHidden(iRow) = False
50            Next iRow
60            Exit Function
70        End If
          
          'Display rows that satisfy filter criteria
80        For iRow = 1 To GrdName.Rows - 1
              'loop to get all the values
90            GrdName.RowHidden(iRow) = False
              
100           intStart = 1
              
110           Do While True
                  
120               intEnd = InStr(intStart, istrFilterValue, DELIMITER_PIPE)
130               If intEnd = 0 Then
140                   Exit Do
150               End If
160               strTemp = Mid(istrFilterValue, intStart, (intEnd - intStart))
                  
                  'Extract the Value and column number
                  
170               Tintstart = 1
180               Tintend = InStr(Tintstart, strTemp, DELIMITER_PLUS)
190               strTempValue = Mid(strTemp, Tintstart, (Tintend - Tintstart))
200               strTempCol = Mid(strTemp, Tintend + 2, Len(strTemp))
                  
                  'Code to extract the Task Code from the Key
210               intStart = intEnd + 2
                              
220               If UCase(Trim(GrdName.TextMatrix(iRow, strTempCol))) <> UCase(Trim(strTempValue)) _
                      Then
230                   If iRow >= GrdName.FixedRows Then
240                       GrdName.RowHidden(iRow) = True
250                   End If
260               End If
270           Loop
280       Next iRow
          
290       GrdName.Refresh
 
300       Exit Function

Open in new window

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.