Hello to all, can anyone help me on how to use the "sort" and "refresh" command using MSFlexGrid?
Here is the code:
|-----------------------------------------------------------------------------------------------

Dim WithEvents Con As ADODB.Connection
Dim WithEvents rst As ADODB.Recordset
Dim cmd As ADODB.Command

[B]Private Sub cmdAdd_Click()[/B]
chec:

Set rst = New ADODB.Recordset 'specifying attributes to this recordset

With rst

    .ActiveConnection = Con
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic

.Open "profile2"

End With

'adding records from textbox to recordset

With rst

    .AddNew
    
    .Fields!Name = StrConv(fldName, vbProperCase)
    .Fields!age = StrConv(fldAge, vbProperCase)
    .Fields!sex = StrConv(fldSex, vbProperCase)
    .Fields!tel = StrConv(fldTelNo, vbProperCase)
    .Fields!address = StrConv(fldAddress, vbProperCase)
    .Fields!occupation = StrConv(fldOccupation, vbProperCase)
    .Fields!diagnosis = StrConv(fldDiagnosis, vbProperCase)
    .Fields!treatment = StrConv(fldTreatment, vbProperCase)
    .Fields!remarks = StrConv(fldRemarks, vbProperCase)
    .Fields!Date = StrConv(lblDate2, vbProperCase)
    .Update
    
End With

' clearing the text boxes

fldName = ""
fldAge = ""
fldSex = ""
fldTelNo = ""
fldAddress = ""
fldOccupation = ""
fldDiagnosis = ""
fldTreatment = ""
fldRemarks = ""

' closing the recordset
rst.Close
Set rst = Nothing

Call dload ' calling private procedure to fill the flexgrid

                    'in case of error, informing the user



fldName.Enabled = False
fldAge.Enabled = False
fldSex.Enabled = False
fldTelNo.Enabled = False
fldAddress.Enabled = False
fldOccupation.Enabled = False
fldDiagnosis.Enabled = False
fldTreatment.Enabled = False
fldRemarks.Enabled = False
cmdAdd.Enabled = False

End Sub

[B]Private Sub cmdDelete_Click()[/B]

Set cmd = New ADODB.Command ' using command object to execute sql commands

With cmd

    .ActiveConnection = Con
    .CommandType = adCmdText
    .CommandText = "delete from profile2 where name = '" & fldName & "'"
    .Execute

End With

Set cmd = Nothing

' clearing all the text boxes

fldName = ""
fldAge = ""
fldSex = ""
fldTelNo = ""
fldAddress = ""
fldOccupation = ""
fldDiagnosis = ""
fldTreatment = ""
fldRemarks = ""
fldDate = ""

Call dload ' calling procedure to fill flexgrid


End Sub

[B]Private Sub cmdupdate_Click()[/B]

On Error GoTo errhan

Set rst = New ADODB.Recordset

With rst

    .CursorLocation = adUseClient
    .ActiveConnection = Con
    .CursorType = adOpenDynamic
    .LockType = adLockPessimistic
    
    .Open "select * from profile2 where name='" & fldName.Text & "'" 'opening the recordset
    
    .Fields!Name = StrConv(fldName, vbProperCase)
    .Fields!age = StrConv(fldAge, vbProperCase)
    .Fields!sex = StrConv(fldSex, vbProperCase)
    .Fields!tel = StrConv(fldTelNo, vbProperCase)
    .Fields!address = StrConv(fldAddress, vbProperCase)
    .Fields!occupation = StrConv(fldOccupation, vbProperCase)
    .Fields!diagnosis = StrConv(fldDiagnosis, vbProperCase)
    .Fields!treatment = StrConv(fldTreatment, vbProperCase)
    .Fields!remarks = StrConv(fldRemarks, vbProperCase)
    .Fields!Date = StrConv(fldDate, vbProperCase)
    
    .Update ' updating the recordset

End With

Set rst = Nothing

Call dload

fldName = ""
fldAge = ""
fldSex = ""
fldTelNo = ""
fldAddress = ""
fldOccupation = ""
fldDiagnosis = ""
fldTreatment = ""
fldRemarks = ""
fldDate = ""

errhan:

If Err.Description <> vbNullString Then
    MsgBox Err.Description
End If

End Sub
[B]Public Sub connect()[/B]

Set Con = New ADODB.Connection

Con.CursorLocation = adUseClient

' use this code to connect to the database using universal data link

'Con.Open "File Name=" & App.Path & "\test.udl"

Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path & "\db2.mdb"

If Con.Provider = "SQLOLEDB.1" Then
    
    DataEnvironment1.Connections(2).Open Con

Else

    DataEnvironment1.Connections(1).Open Con
    
End If

Call dload

End Sub
[B]Private Sub dload()[/B]

MSFlexGrid1.Rows = 1

Set rst = New ADODB.Recordset

    rst.ActiveConnection = Con
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.Source = "profile2"
    rst.Open
    
While Not rst.EOF() ' checking end of file

    MSFlexGrid1.AddItem rst!Name & Chr(9) & rst!age & Chr(9) & rst!sex & Chr(9) & rst!tel & Chr(9) & rst!address & Chr(9) & rst!occupation & Chr(9) & rst!diagnosis & Chr(9) & rst!treatment & Chr(9) & rst!remarks
    
    rst.MoveNext

Wend

Set rst = Nothing

End Sub

[B]Private Sub cmdNew_Click()[/B]
fldName = ""
fldAge = ""
fldSex = ""
fldTelNo = ""
fldAddress = ""
fldOccupation = ""
fldDiagnosis = ""
fldTreatment = ""
fldRemarks = ""
fldDate = ""

fldName.Enabled = True
fldAge.Enabled = True
fldSex.Enabled = True
fldTelNo.Enabled = True
fldAddress.Enabled = True
fldOccupation.Enabled = True
fldDiagnosis.Enabled = True
fldTreatment.Enabled = True
fldRemarks.Enabled = True
cmdAdd.Enabled = True

End Sub

[B]Private Sub cmdPrint_Click()[/B]

With DataEnvironment1

    If Con.Provider = "SQLOLEDB.1" Then
            
            
            .Commands(2).CommandType = adCmdText
            .Commands(2).CommandText = "SELECT * FROM profile2 where Name = '" & fldName.Text & "'"
            .Commands(2).Execute
            
        DataReport2.Show
            
            
          If .rsCommand2.State = 1 Then
          
            .rsCommand2.Close
          
          End If
        
     
    Else
    
     
            .Commands(1).CommandType = adCmdText
            .Commands(1).CommandText = "SELECT * FROM profile2 where Name = '" & fldName.Text & "'"
            .Commands(1).Execute
        
        DataReport1.Show
        
        If .rsCommand1.State = 1 Then
          
            .rsCommand1.Close
          
        End If
     
    
    End If

End With

End Sub
*****************************************
Private Sub cmdRefresh_Click()

End Sub
*****************************************
Private Sub cmdRefresh_Click()

End Sub
*****************************************
Private Sub cmdSearch_Click()
Form5.Show
End Sub

Private Sub Form_Load()
Call connect
End Sub

Private Sub Form_Unload(Cancel As Integer)

Con.Close
Set Con = Nothing

End Sub

Private Sub mnuExit_Click()
End
End Sub

Private Sub MSFlexGrid1_Click()

With MSFlexGrid1 ' populating the text boxes when user clicks the flexgrid

    .Col = 0
        fldName.Text = .Text
    .Col = 1
        fldAge.Text = .Text
    .Col = 2
        fldSex.Text = .Text
    .Col = 3
        fldTelNo.Text = .Text
    .Col = 4
        fldAddress.Text = .Text
    .Col = 5
        fldOccupation.Text = .Text
    .Col = 6
        fldDiagnosis.Text = .Text
    .Col = 7
        fldTreatment.Text = .Text
    .Col = 8
        fldRemarks.Text = .Text
        
End With

End Sub

Private Sub Timer1_Timer()
lblDate2.Caption = Date
lblTime.Caption = Time
End Sub

|-------------------------------------------------------------------------------------------

this a program than can store data but the problem is i don't know what is the code so i can refresh and sort data inside the MSFlexGrid. . .can anyone help me pls..
see the code, the one that is in red color sub is the problem. . .

email me at: crumple_05@yahoo.com
thank you for the help. . .

The MsFlexGrid.Sort property sorts entire rows. I think it may not useful for particular column.
To Sort
Make the SQL with ORDER BY Clause. This will sort your Records.

To refresh
Also in FlexGrid you are loading the records so you have to Reload everything in Refresh Action.

Hi,

Write this code in Grid's Click event:

If Grd.Row = 1 And Grd.Col > 0 Then
  Grd.Sort = 1
End If

Click on any of the Fixed Column (at top) This allows you to Sort the selected Column.

Regards
veena

Thank you so much. . your code is a big help to me. . .thank you, it works. . =)

Another suggestion, use MSHFlexGrid (Heirarchial FlexGrid to populate the records of the recordset. U need not use the loop to populate the records. just use

Open the recordset as KeySet

rst.Open QryString, con, adOpenKeyset
Set MSHFlexGrid1.Recordset = rst

Why don't u use ORDER BY (fieldname) is ur sql query it will definately solve ur sorting and refreshing issue.


Regards
DSP

This article has been dead for over six months. Start a new discussion instead.