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. . .
