Berikut ini adalah tampilan , kira kira program yang akan kita buat :
(click gambar untuk memperbesar)
Program ini kita buat simple, database mysql dengan satu table.
Step Pertama adalah Menyiapkan Database :
1. Buat satu database dengan nama : “Market”
2. Buat tablenya sebagai berikut :
CREATE TABLE `marketdetail` (
`ID` varchar(255) character set utf8 default NULL,
`Comodity` varchar(255) character set utf8 default NULL,
`Quantity` float default NULL,
`Negara` varchar(255) character set utf8 default NULL,
`Dest` varchar(255) character set utf8 default NULL,
`Tanggal` varchar(255) character set utf8 default NULL,
`CW` float default NULL,
`BERAT` varchar(255) character set utf8 default NULL,
`Market_id` mediumint(9) NOT NULL auto_increment,
PRIMARY KEY (`Market_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=99 ;—-
Setelah database beserta tablenya dibuat, berikutnya kita siapkan modul entrynya menggunakan aplikasi VB. Berikut tampilan dan listing programnya
Lakukan langkah Berikut :
1. Buat satu Modul (namakan global.bas)
2. Isikan modul tersebut dengan Syntak2 Berikut :
Public objCnn As New ADODB.Connection
Public objcmd As New ADODB.Command
Public objRst As New ADODB.Recordset
Public USRText As String
Public PWDText As String
Public PortText As String
Public strconnect As String
Public ServerAddress As String
Public DatabaseName As String
Sub SQLConnection()
ServerAddress = “localhost”
USRText = “root”
PWDText = “”
DatabaseName = “market”
PortText = “”
Set objCnn = Nothing
strconnect = “Provider=MSDASQL.1;Password=;Persist Security Info=True;User ID=;Extended Properties=” & Chr$(34) & “DRIVER={MySQL ODBC 3.51 Driver};DESC=;DATABASE=” & DatabaseName & “;SERVER=” & ServerAddress & “;UID=” & USRText & “;PASSWORD=” & PWDText & “;PORT=” & PortText & “;OPTION=16387;STMT=;” & Chr$(34)
objCnn.CursorLocation = adUseClient
objCnn.ConnectionTimeout = 0
objCnn.Open strconnect
End Sub
Sub SQLDisconnection()
Set objRst = Nothing
Set objcmd = Nothing
objCnn.Close
End Sub
2. Buat Form seperti Dibawah ini
3. Pada tombol Save isikan syntak berikut (perhatikan pada penamaan objek Text Box dan Tanggal !!) :
Select Case MsgBox(”Do you want to save the record?”, vbQuestion + vbYesNoCancel, “Confirmation”)
Case vbYes
‘ If Not bValidate Then Exit Sub
Call SQLConnection
strSQL = “INSERT INTO marketdetail (ID, Comodity, Quantity, Negara, Dest, Tanggal, CW, Berat) ” & _
“VALUES (’” + Trim(Me.TxtName.Item(0).Text) + “‘ , ‘” + Trim(Me.TxtName.Item(1).Text) + “‘, ” & Me.TxtName.Item(2).Text & “,” & _
” ‘” + Trim(Me.TxtName.Item(3).Text) + “‘ , ‘” + Trim(Me.TxtName.Item(4).Text) + “‘, ‘” + Format(Me.TxtDate(0).Value, “mm/dd/yy”) + “‘, ‘” & Me.TxtName.Item(5).Text & “‘, ‘” & Me.TxtName.Item(6).Text & “‘)”
objCnn.Execute (strSQL)
Call SQLDisconnection
Case vbNo
msgbox “Gak Jadi !!”
Case vbCancel
Msgbox “Batal”
End Select
Selanjutnya, data akan kita tampilkan pada VSFlexgrid dan terakhir 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 :
dan seperti ini
Tidak ada komentar:
Posting Komentar