Filter grid1 with all last records

[wilder1926]

Hello all

I need you help on this code please.

What i need to do is to be able to filter my grid based on 2 criteria.

In column 4 of every rows, i have ID for stores(Ex: 00167D)
In column 5 or every rows, i have the date that the ID was entered in the list with a new cost in column 6.

I will have in this list many times the same ID but with different dates and i will found over 700 different ID also.

So yes, this is a huge data base,.


Now i need to click on a button an just keep the most recent date for every ID in column 4, and delete all the others.

Can you help me with that please?

This is what i have for now but I'm having a runtime error '381' subscript out of range:

strCol4 = MSHFlexGrid1.TextMatrix(r, 4)
strCol5 = MSHFlexGrid1.TextMatrix(r, 5)



Thanks again.

Private Sub FetchNoRowCol(ws As Excel.Worksheet, ByRef NoOfRows As Long, _
        ByRef NoOfColumns As Long)
   
    NoOfRows = ActiveSheet.Cells.SpecialCells(11).Row
NoOfColumns = ActiveSheet.Cells.SpecialCells(11).Column
               
End Sub

and

Dim xlObject     As Excel.Application
Dim xlWB         As Excel.Workbook
Dim NoOfRows     As Long
Dim NoOfColumns  As Long
Dim r As Long, NewRow As Long, RowToDelete As Long
Dim strCol4 As String, strCol5 As String
Dim strNewCol4 As String, strNewCol5 As String



For r = 0 To MSHFlexGrid1.Rows - 1
      If r <= MSHFlexGrid1.Rows - 1 Then
         strCol4 = MSHFlexGrid1.TextMatrix(r, 4)
         strCol5 = MSHFlexGrid1.TextMatrix(r, 5)
         If Len(strCol4) > 0 And Len(strCol5) > 0 Then
            strCol5 = CDate(MSHFlexGrid1.TextMatrix(r, 5))
            For NewRow = r - 1 To MSHFlexGrid1.Rows - 1
               RowToDelete = 0
               If NewRow <= MSHFlexGrid1.Rows - 1 Then
                  If MSHFlexGrid1.TextMatrix(NewRow, 4) = strCol4 Then
                     strNewCol5 = CDate(MSHFlexGrid1.TextMatrix(NewRow, 5))
                     If DateDiff("s", strCol5, strNewCol5) > 0 Then
                        RowToDelete = r
                     ElseIf DateDiff("s", strCol5, strNewCol5) < 0 Then
                        RowToDelete = NewRow
                     End If
                     If RowToDelete Then
                        MSHFlexGrid1.RemoveItem RowToDelete
                        r = r - 1
                     End If
                  End If
               End If
            Next NewRow
         End If
      End If
   Next r

[dglienna]

1) Use CODE TAGS!

' Like this

#2 Don't double-post.

#3 Why use Excel for a DB problem?

[wilder1926]

Hello all

Hummm sorry for the double post, i did not even no it.

Sorry for that

Code is now fixed also.

Now why excel, because it is a dbase extract from another department where i work. So from this, i have a VB project that convert there list in different ways.

[DataMiser]

An Access database would be a much better choice. Excel is not a database even if your data was extracted from a database you are still working with a spreadsheet and not a database.

[wilder1926]

Ok

So to populate my grid normally, i would use this:

Dim sSQL2 As String
    Dim oRST2 As ADODB.Recordset
  Set oRST2 = New ADODB.Recordset
  Dim oConnect2 As ADODB.Connection
  Set oConnect2 = New ADODB.Connection
'

     oConnect2.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & parameter.fuel_surcharge.Text

sSQL2 = "SELECT [CARRIER_ID],[ACCESS_ID],[DATE_ID],[CHARGE] FROM [fuel_surcharge_1_new] " '"

oRST2.Open sSQL2, oConnect2

If oRST2.BOF = False Then

Do Until oRST2.EOF
  With MSHFlexGrid1
        'do the this if u have multiple field to add on your flexgrid
        .AddItem oRST2("CARRIER_ID") & vbTab & oRST2("ACCESS_ID") & vbTab & oRST2("DATE_ID") & vbTab & oRST2("CHARGE") & vbTab
  oRST2.MoveNext
  End With
Loop
End If


 'Auto column fit

Dim c As Long
Dim z As Long
Dim cell_wid As Single
Dim col_wid As Single

    For c = 0 To MSHFlexGrid1.Cols - 1
        col_wid = 0
        For z = 0 To MSHFlexGrid1.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid1.TextMatrix(z, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next z
        MSHFlexGrid1.ColWidth(c) = col_wid + 120
    Next c

But how would i be able to filter the data before it enter in the grid?

[dglienna]

Thanks. This might help, using a MS-H-Flexgrid, which is not the standard MSFlexgrid.

Option Explicit


Private Sub Command1_Click()
    Dim cnLvConnection As ADODB.Connection
    Set cnLvConnection = New ADODB.Connection
    Dim rsLvRecordset As ADODB.Recordset
    Set rsLvRecordset = New ADODB.Recordset
    With cnLvConnection
        .Provider = "MSDataShape.1"
        .ConnectionString = "Data Source=" & App.Path & "\db1.mdb;" _
                          & "Data Provider=Microsoft.Jet.OLEDB.4.0;"
        .Open
        With rsLvRecordset
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
        End With
        Set rsLvRecordset = .Execute("SHAPE {SELECT c.CustomerName As Customer, c.CustomerID FROM Customers c ORDER BY c.CustomerName} As Customers" _
                                   & " APPEND ((SHAPE {SELECT oh.OrderNumber As [Order No], oh.CustomerID, oh.OrderHeaderID FROM OrderHeaders oh ORDER BY oh.OrderNumber} As OrderHeaders" _
                                   & "          APPEND ({SELECT od.OrderLine As [Line], od.OrderLineDescription As [Description], od.OrderLineQuantity As Quantity, od.OrderHeaderID FROM OrderDetails od ORDER BY od.OrderLine} As OrderDetails" _
                                   & "          RELATE OrderHeaderID TO OrderHeaderID))" _
                                   & " RELATE CustomerID TO CustomerID)")
    End With
    '   Setup Grid
    Set Me.MSHFlexGrid1.Recordset = rsLvRecordset
    Me.MSHFlexGrid1.ColWidth(1, 0) = 0 ' c.CustomerID
    Me.MSHFlexGrid1.ColWidth(1, 1) = 0 ' oh.CustomerID
    Me.MSHFlexGrid1.ColWidth(2, 1) = 0 ' oh.OrderHeaderID
    Me.MSHFlexGrid1.ColWidth(3, 2) = 0 ' od.OrderHeaderID
    '   Tidy up
    If Not rsLvRecordset Is Nothing Then
        If rsLvRecordset.State <> adStateClosed Then
            rsLvRecordset.Close
        End If
        Set rsLvRecordset = Nothing
    End If
    If Not cnLvConnection Is Nothing Then
        If cnLvConnection.State <> adStateClosed Then
            cnLvConnection.Close
        End If
        Set cnLvConnection = Nothing
    End If
End Sub

Private Sub Form_Load()
    Me.MSHFlexGrid1.FixedCols = 0
End Sub

'
'  Edit the cell, without the update

Private Sub MSHFlexGrid1_KeyPress(KeyAscii As Integer)
   If KeyAscii = vbKeyReturn Then   'enter key
    'move to next cell.
        With MSHFlexGrid1
            If .Col + 1 <= .Cols - 1 Then
                .Col = .Col + 1
            Else
                If .Row + 1 <= .Rows - 1 Then
                    .Row = .Row + 1
                    .Col = 0
                Else
                    .Row = 1
                    .Col = 0
                End If
            End If
        End With
    ElseIf KeyAscii = vbKeyBack Then   'back space key
        With MSHFlexGrid1
            'back space out the entered characters
            If Len(.Text) Then
                .Text = Left(.Text, Len(.Text) - 1)
            End If
        End With
    Else
        With MSHFlexGrid1
            .Text = .Text & Chr(KeyAscii)
        End With
    End If
End Sub

[wilder1926]

ok, this is based on multiple data base in access.

Let me see what i can do here since i need to filter the grid by date and ACCESS_ID to get the last effective of each.

[dglienna]

Search for EXCEL and SQL. You can connect to a WorkSheet just like it was a table, but there are certain 'rules' that you need to follow.

Pretty sure it uses [] for cells, and a different connection string.

[wilder1926]

Oh OK, i will search for it.

I was kind of struggling