Visual Basic - MySQL - VSFlexGrid - Crystal Report

I. Buat tampilan seperti dibawah ini ,

dgn komposisi sebagai berikut :
1. satu buah combo box dengan nama CboFields
2. satu buah vsFlexgrid dengan nama VSFlexGrid1
3. dua buah command button dengan nama :
cmdprocess dan cmdprint

II. Berikut syntax2-nya :

1. Form load :Private Sub Form_Load()
Me.Left = 0
Me.Top = 0
Me.VSFlexGrid1.Rows = 1
Call SQLConnection
Me.CboFields.Clear

’syntax dibawah ini digunakan untuk mengisi combo box dengan
’nama2 filed dari table marketdetail

With objcmd
.ActiveConnection = objCnn
.CommandText = “show columns from marketdetail”
.CommandType = adCmdText
End With
Set objRst = objcmd.Execute
Do While objRst.EOF = False
Me.CboFields.AddItem objRst.Fields(0)
objRst.MoveNext
DoEvents
Loop
Call SQLDisconnection
End Sub

2. tombol Process (CmdProcess)

Private Sub CmdProcess_Click()
On Error GoTo erhan
Call SQLConnection
Dim i As Integer
Dim y As Integer
Dim cnt1 As Integer
i = 0
y = 0
If Len(Trim(Me.CboFields.Text)) = 0 Then
MsgBox “Please select Order By Selection !”, vbOKOnly + vbInformation
Exit Sub
End If
With objcmd
.ActiveConnection = objCnn
.CommandText = “Select * From marketdetail order by ” + Me.CboFields.Text + ” asc”
.CommandType = adCmdText
End With
Set objRst = objcmd.Execute
If objRst.EOF = False Then
cnt1 = objRst.RecordCount
For i = 0 To objRst.Fields.Count - 1
Me.VSFlexGrid1.Rows = 1
Me.VSFlexGrid1.Cols = 11
Me.VSFlexGrid1.Row = 0
Me.VSFlexGrid1.Col = i + 1
Me.VSFlexGrid1.Text = objRst.Fields(i).Name
Me.VSFlexGrid1.CellFontBold = True
Next
i = 0
y = 1
Do While objRst.EOF = False
Me.VSFlexGrid1.Rows = Me.VSFlexGrid1.Rows + 1
For i = 0 To objRst.Fields.Count - 1
Me.VSFlexGrid1.TextMatrix(y, i + 1) = Trim(objRst.Fields(i))
DoEvents
Next i
y = y + 1
DoEvents
objRst.MoveNext
DoEvents
Loop
Else
Exit Sub
End If
Call SQLDisconnection

DoEvents
Me.VSFlexGrid1.AllowUserResizing = flexResizeBoth
i = 0
For i = 1 To 10
Me.VSFlexGrid1.MergeCol(i) = True
Next i
SortAndShow

VSFlexGrid1.MergeCells = 1
VSFlexGrid1.MergeCells = 2
‘
VSFlexGrid1.Subtotal flexSTSum, -1, 3, m_Fmt, , RGB(255, 0, 0), True
VSFlexGrid1.Subtotal flexSTSum, -1, 7, m_Fmt, , RGB(255, 0, 0), True

VSFlexGrid1.Subtotal flexSTSum, 1, 3, m_Fmt, , RGB(0, 0, 255), True
VSFlexGrid1.Subtotal flexSTSum, 1, 7, m_Fmt, , RGB(255, 0, 0), True
Exit Sub
erhan:
MsgBox Err.Description
Call SQLDisconnection
End Sub
”—– additional procedure

Sub SortAndShow()
Dim r%, c%
On Error GoTo erhan
VSFlexGrid1.Redraw = False

r = VSFlexGrid1.Row
c = VSFlexGrid1.Col

VSFlexGrid1.Col = 0
VSFlexGrid1.ColSel = VSFlexGrid1.Cols - 1
VSFlexGrid1.Row = r
VSFlexGrid1.Col = c
VSFlexGrid1.Redraw = True
Exit Sub
erhan:
MsgBox Err.Description
Resume Next
End Sub

3. tombol Print (cmdprint)

untuk tombol print ini pastikan reportnya sudah dibuat terlebih dahulu, dengan nama file reportnya adalah “mymarket.rpt”

Private Sub CmdPrint_Click()
On Error GoTo erhan
LocText$ = LCase(App.path)
If Right$(App.path, 1) <> “\” Then LocText$ = LocText$ + “\”
CrystalReport1.Connect = strconnect
CrystalReport1.ReportFileName = LocText$ + “mymarket.rpt”

On Error GoTo erhan
CrystalReport1.Action = 0
Exit Sub
erhan:
MsgBox Err.Description
End Sub

jadi hasil akhirnya harusnya seperti ini :