Lagi enak2 senderan bis makan siang, tau2 pundak ditepuk ma Utut anak Tax.
“Gw mo TA nih…, mo ga bikinin Aplikasi PPh Pasal 21?”
“Berapa duit?”
“150 rb!, tp seminggu ya…, trus pake VB6″
“Ok Deal!”
Mendesign Struktur Database
Tentukan dahulu struktur Databasenya, saya tulis dalam vbModul seperti berikut:
mdlUtama.bas
Option Explicit
‘ Koneksi DataBase menggunakan:
‘ - Reference Microsoft ActiveX Data Object 2.x Library
‘ - Reference ADO Ext. 2.x For DDL and Security
Public ConnMDB As New ADODB.Connection
Public CatMDB As New ADOX.Catalog
Public CatRpt As New ADOX.Catalog
Public TblMDB As ADOX.Table
Public TblRpt As ADOX.Table
Public strFile As String
Public SQL As String
Public i As Integer
Public strUser As String
Public strPlu As String
Public Sub Main()
‘ Buka Koneksi DataBase Cukup Sekali saja
‘ Panggil Sub Koneksi Database
Call KoneksiData
MDIfrmUtama.Show
End Sub
Public Sub KoneksiData()
‘Letak Database sama dengan Path Aplikasi
strFile = App.Path & “\Utut.Mdb”
‘Cek keberadaan data
If Dir(strFile) = “” Then
‘Jika tidak ada buat Database baru
CatMDB.Create “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFile & _
“;Jet OLEDB:Database Password=;”
‘Buat Tabel Karyawan
Set TblMDB = New ADOX.Table
With TblMDB
.Name = “Karyawan”
.Columns.Append “NPWP”, adVarWChar, 50
.Columns.Append “Nama WP”, adVarWChar, 100
.Columns.Append “Alamat”, adVarWChar, 100
.Columns.Append “Status”, adVarWChar, 20
.Columns.Append “Gaji Pokok”, adDouble
.Columns.Append “Tunjangan”, adDouble
.Columns.Append “Lembur”, adDouble
.Columns.Append “Jumlah Penghasilan Tetap”, adDouble
.Columns.Append “Biaya Jabatan”, adDouble
.Columns.Append “Jumlah Penghasilan Netto”, adDouble
.Columns.Append “Penghasilan Tidak Tetap”, adDouble
.Columns.Append “Total Penghasilan”, adDouble
.Columns.Append “PTKP”, adDouble
.Columns.Append “PKP”, adDouble
.Columns.Append “PPh Pasal 21″, adDouble
End With
‘Tambah Primari Key dan Index Key
TblMDB.Keys.Append “PrimeKey”, adKeyPrimary, “NPWP”
TblMDB.Indexes.Append “IndexKey”, “NPWP”
CatMDB.Tables.Append TblMDB
Set TblMDB = Nothing
‘Buat Tabel Tenaga Ahli
Set TblMDB = New ADOX.Table
With TblMDB
.Name = “Tenaga Ahli”
.Columns.Append “NPWP”, adVarWChar, 50
.Columns.Append “Nama WP”, adVarWChar, 100
.Columns.Append “Alamat”, adVarWChar, 100
.Columns.Append “Telepon”, adVarWChar, 30
.Columns.Append “Penghasilan Bruto”, adDouble
.Columns.Append “Keahlian”, adVarWChar, 100
.Columns.Append “PPh Dipotong”, adDouble
End With
‘Tambah Primari Key dan Index Key
TblMDB.Keys.Append “PrimeKey”, adKeyPrimary, “NPWP”
TblMDB.Indexes.Append “IndexKey”, “NPWP”
CatMDB.Tables.Append TblMDB
Set TblMDB = Nothing
ConnMDB.Open “Provider=Microsoft.Jet.OLEDB.4.0;” _
& “Data Source=” & strFile & “;” & _
“Jet OLEDB:Database Password=;”
Else
‘Buka Database yang Exist
ConnMDB.Open “Provider=Microsoft.Jet.OLEDB.4.0;” _
& “Data Source=” & strFile & “;” & _
“Jet OLEDB:Database Password=;”
‘Deklarasi Katalog ke Koneksi yang aktif
CatMDB.ActiveConnection = ConnMDB
End If
End Sub
Selanjutnya tambah MDIForm untuk menu
MDIfrmUtama.frm
Option Explicit
Private Sub MDIForm_Load()
Me.Caption = “Aplikasi PPh Pasal 21″ & ” versi ” & App.Major & “.” & App.Minor
End Sub
Private Sub mnKaryawan_Click()
frmKaryawan.Show
End Sub
Private Sub mnRepKaryawan_Click()
Dim strFileReport As String
Dim strTableReport As String
strFileReport = “C:\Windows\Temp\UtutReport.Mdb”
If Dir(strFileReport) = “” Then
CatRpt.Create “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFileReport & _
“;Jet OLEDB:Database Password=;”
Else
CatRpt.ActiveConnection = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFileReport & _
“;Jet OLEDB:Database Password=;”
End If
For Each TblRpt In CatRpt.Tables
‘jika ada tabel ..
If TblRpt.Name = “Karyawan” Then CatRpt.Tables.Delete TblRpt.Name
Next
strTableReport = Left(strFileReport, Len(strFileReport) - 3) & “Karyawan”
SQL = “SELECT * INTO ” & strTableReport & ” FROM karyawan”
ConnMDB.Execute SQL
With Crt
.ReportFileName = App.Path & “\ReportKaryawan.rpt”
.RetrieveDataFiles
.Action = 1
End With
End Sub
Private Sub mnRepTenagaAhli_Click()
Dim strFileReport As String
Dim strTableReport As String
strFileReport = “C:\Windows\Temp\UtutReport.Mdb”
If Dir(strFileReport) = “” Then
CatRpt.Create “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFileReport & _
“;Jet OLEDB:Database Password=;”
Else
CatRpt.ActiveConnection = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFileReport & _
“;Jet OLEDB:Database Password=;”
End If
For Each TblRpt In CatRpt.Tables
‘jika ada tabel ..
If TblRpt.Name = “Tenaga Ahli” Then CatRpt.Tables.Delete TblRpt.Name
Next
strTableReport = Left(strFileReport, Len(strFileReport) - 3) & “[Tenaga Ahli]”
SQL = “SELECT * INTO ” & strTableReport & ” FROM [Tenaga Ahli]”
ConnMDB.Execute SQL
With Crt
.ReportFileName = App.Path & “\ReportAhli.rpt”
.RetrieveDataFiles
.Action = 1
End With
End Sub
Private Sub mnTenagaAhli_Click()
frmTenagaAhli.Show
End Sub
Design tiap-tiap Laporan dengan Crystal Report 8.5, ambil data dari tabel Karyawan dan Tenaga Ahli.
Form Inputan akan dibahas di bagian 2 nanti.
Untuk sesion ini apa ada pertanyaan?
