Inti dari sebuah aplikasi
Absensi Karyawan
adalah bagaimana menyimpan data kehadiran/absensi semua karyawan secara
lengkap. Masing-masing karyawan tentunya memiliki sebuah identitas
unique/berbeda
dari yang lain misalnya NIP atau yang lain. Semua data terkait
kehadiran baik itu, jam masuk, jam keluar, hari masuk, durasi waktu
kerja, jumlah kehadiran dan yang lain merupakan data-data yang mengacu
pada masing-masing karyawan. Disinilah peran utama
database, sehingga setiap karyawan dapat diketahui masing-masing datanya.
Untuk membuat sebuah database kita harus membuat pengelompokan
data-data menjadi beberapa tabel yang memiliki kesamaan. Misalnya:
- tabel Karyawan: terdiri dari ID, Nama, Tanggal Lahir, Jabatan, Alamat
- tabel Jam Kerja: terdiri dari ID, bulan, Tahun, Jam, Sisa Jam Kerja
dan seterusnya.
Dari data-data di atas, kita bisa membuat database menggunakan MsAccess dengan desain seperti berikut:
Tabel Karyawan
Tabel Jam Kerja
untuk dapat menggunakan template yang sudah ada klik
disini.
Selanjutnya bagaimanakah untuk menghubungkan database yang sudah dibuat di atas dengan Visual Basic?
1. Buatlah sebuah project standard pada
Visual Basic 6.0
2. Kemudian masukkan komponen MSFlexGrid dengan menekan
Ctrl+T atau melalui menu Project–> Component, seperti ditunjukkan gambar berikut:
kemudian klik
Apply.
3. Selanjutnya buatlah tampilan berikut:
4. Kemudian masukkan komponen DtPicker dengan menekan Ctrl+T atau
melalui menu Project–> Component, seperti ditunjukkan gambar berikut:
5. Kemudian buat tampilan berikut, dengan menambahkan kotak isian masukan:
6. Dengan menambahkan beberapa tombol yang diperlukan, Car, Tambah, Edit, Hapus, dan Close tampilan akhir akan seperti berikut:
Untuk melanjutkan latihan dengan template yang sudah ada, silakan unduh file
disini.
Menambahkan Coding
1. Pada bagian paling atas sisi coding tambahkan deklarasi sebagai berikut:
Dim db As Connection
Dim rs As Recordset
Dim Saldo As Long, SaldoAwal As Long
Dim SQL As String
2. Kemudian buka menu Project –> Reference, centang pada pilihan berikut:
3. Isikan coding berikut:
Private Sub Form_Load()
Set db = New Connection
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\dbAbsensi.mdb;Persist Security Info=False"
db.CursorLocation = adUseClient
db.Open
dtTglLahir.Value = Format(Date, "dd/mm/yy")
End Sub
Lengkapilah coding menjadi berikut:
Public JumlahKaryawan As Integer
Dim i As Integer
Dim db As Connection
Dim rs As Recordset
Private Sub cmdBaca_Click()
If cmdBaca.Caption = "Baca" Then
tmrBarcode.Enabled = True
cmdBaca.Caption = "Stop"
'Call KunciBacaBarcode
txtCari.SetFocus
'cmdSearch.Caption = "Baca"
ElseIf cmdBaca.Caption = "Stop" Then
tmrBarcode.Enabled = True
cmdBaca.Caption = "Baca"
End If
End Sub
Private Sub cmdCari_Click()
CariKaryawan
End Sub
Private Sub cmdCetak_Click()
FormBarcode.Show
FormBarcode.Text1 = txtInfo(0).Text
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Function LihatNoKaryawanAkhir() As Integer
Dim NoAkhir As Integer
Set rs = New Recordset
Dim SQL As String
Dim gi As Integer
NoAkhir = 0
SQL = "Select * from tblKaryawan"
Set rs = New Recordset
rs.Open SQL, db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
If NoAkhir < rs.Fields(0) Then
NoAkhir = rs.Fields(0)
End If
gi = gi + 1
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
LihatNoKaryawanAkhir = NoAkhir
End Function
Sub Buka()
For i = 0 To 4
txtInfo(i).Enabled = True
Next
dtTglLahir.Enabled = True
End Sub
Sub Kunci()
For i = 0 To 4
txtInfo(i).Enabled = False
Next
dtTglLahir.Enabled = False
End Sub
Private Sub TampilKaryawan()
Dim gi As Integer
Dim SQL As String
Grid.Rows = 2
Grid.Width = 11535
Grid.ColWidth(0) = 500
Grid.ColWidth(1) = 1500
Grid.ColWidth(2) = 2000
Grid.ColWidth(3) = 2000
Grid.ColWidth(4) = 2000
Grid.ColWidth(5) = 1200
Grid.ColWidth(6) = 1200
Grid.ColWidth(6) = 2200
Grid.Clear
gi = 1
Grid.TextMatrix(0, 0) = "No"
Grid.TextMatrix(0, 1) = "Kode ID"
Grid.TextMatrix(0, 2) = "Nama"
Grid.TextMatrix(0, 3) = "Jabatan"
Grid.TextMatrix(0, 4) = "Tempat Lahir"
Grid.TextMatrix(0, 5) = "Tgl Lahir"
Grid.TextMatrix(0, 6) = "Alamat"
Grid.TextMatrix(0, 7) = "Jam Kerja"
SQL = "Select * from tblKaryawan"
Set rs = New Recordset
rs.Open SQL, db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
Grid.TextMatrix(gi, 0) = gi
Grid.TextMatrix(gi, 1) = rs.Fields(0)
Grid.TextMatrix(gi, 2) = rs.Fields(1)
Grid.TextMatrix(gi, 3) = rs.Fields(2)
Grid.TextMatrix(gi, 4) = rs.Fields(3)
Grid.TextMatrix(gi, 5) = rs.Fields(6)
Grid.TextMatrix(gi, 6) = rs.Fields(4)
gi = gi + 1
Grid.Rows = Grid.Rows + 1
rs.MoveNext
Loop
End If
JumlahKaryawan = gi - 1
rs.Close
Set rs = Nothing
End Sub
Sub Simpan()
Set rs = New Recordset
Dim SQL As String
SQL = "Select * from tblKaryawan where NoKartu='" & txtInfo(0).Text & "'"
rs.Open SQL, db, adOpenDynamic, adLockOptimistic
With rs
.AddNew
!NoKartu = txtInfo(0)
!Nama = txtInfo(1)
!Jabatan = txtInfo(2)
!TempatLahir = txtInfo(3)
!Alamat = txtInfo(6)
!TanggalLahir = dtTglLahir.Value
.UpDate
End With
rs.Close
Set rs = Nothing
MsgBox " Record order telah ditambahkan", vbInformation, "Sukses"
Call TampilKaryawan
End Sub
Sub UpDate()
Set rs = New Recordset
Dim SQL As String
'sql = "select * from tblCostumer"
SQL = "Select * from tblKaryawan where NoKartu='" & txtInfo(0).Text & "'"
rs.Open SQL, db, adOpenDynamic, adLockOptimistic
With rs
.UpDate
!NoKartu = txtInfo(0)
!Nama = txtInfo(1)
!Jabatan = txtInfo(2)
!TempatLahir = txtInfo(3)
!Alamat = txtInfo(4)
!TglLahir = dtTglLahir
.UpDate
End With
rs.Close
Set rs = Nothing
MsgBox "Record telah diEdit", vbInformation, "Sukses"
txtInfo(0) = ""
txtInfo(1) = ""
txtInfo(2) = ""
txtInfo(3) = ""
txtInfo(4) = ""
Call TampilKaryawan
End Sub
Private Sub cmdEdit_Click()
Set rs = New Recordset
Dim SQL As String
If txtInfo(0).Enabled = False Or txtInfo(1).Enabled = False Or txtInfo(2).Enabled = False Or txtInfo(3).Enabled = False Then
txtInfo(1).Enabled = True
txtInfo(2).Enabled = True
txtInfo(3).Enabled = True
txtInfo(4).Enabled = True
dtTglLahir.Enabled = True
End If
If cmdEdit.Caption = "&" + "Edit" Then
cmdEdit.Caption = "&" + "Update"
txtInfo(1).SetFocus
Exit Sub
ElseIf cmdEdit.Caption = "&" + "Update" Then
cmdEdit.Caption = "&" + "Edit"
Call UpDate
cmdTambah.Enabled = True
cmdEdit.Enabled = False
cmdHapus.Enabled = False
Call Kunci
End If
If cmdEdit.Caption = "&" + "Batal" Then
cmdEdit.Caption = "&" + "Edit"
cmdEdit.Enabled = False
cmdTambah.Enabled = True
cmdTambah.Caption = "&" + "Tambah"
For i = 1 To 3
txtInfo(i).Text = ""
Next
Call Kunci
Exit Sub
End If
End Sub
Sub Hapus()
Set rs = New Recordset
Dim SQL As String
SQL = "select * from tblKaryawan where NoKartu='" + txtInfo(0).Text + "'"
rs.Open SQL, db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
rs.Delete
MsgBox "Record karyawan(" & txtInfo(0).Text & ")", vbInformation, "Karyawan"
End If
rs.Close
Set rs = Nothing
End Sub
Private Sub cmdHapus_Click()
Dim NV As Integer
NV = MsgBox("Apakah record karyawan '" & txtInfo(0).Text & "' akan dihapus? ", vbYesNoCancel, "Konfirmasi")
If NV = vbYes Then
Call Hapus
End If
If NV = vbCancel Then
Exit Sub
End If
If NV = vbNo Then
Exit Sub
End If
txtInfo(0).Text = ""
txtInfo(1).Text = ""
txtInfo(2).Text = ""
txtInfo(3).Text = ""
txtInfo(4).Text = ""
dtTglLahir.Value = Format(Date, "dd/mm/yy")
Call TampilKaryawan
cmdHapus.Enabled = False
End Sub
Sub KunciBacaBarcode()
Call Kunci
txtInfo(0).Enabled = True
End Sub
Private Sub cmdTambah_Click()
Dim KodeTemp As String
Set rs = New Recordset
Dim SQL As String
If cmdTambah.Caption = "&" + "Batal" Then
cmdTambah.Caption = "&" + "Tambah"
cmdTambah.Enabled = True
cmdEdit.Enabled = False
cmdHapus.Enabled = False
For i = 1 To 4
txtInfo(i).Text = ""
Next
If cmdEdit.Caption = "&" + "Update" Then
cmdEdit.Caption = "&" + "Edit"
End If
dtTglLahir.Value = Format(Date, "dd/mm/yy")
Exit Sub
End If
If cmdTambah.Caption = "&" + "Tambah" Then
cmdTambah.Caption = "&" + "Simpan"
cmdEdit.Caption = "&" + "Batal"
cmdTambah.Enabled = False
cmdEdit.Enabled = True
If txtInfo(0).Enabled = False Then
txtInfo(0).Enabled = True
End If
txtInfo(0).SetFocus
Exit Sub
ElseIf cmdTambah.Caption = "&" + "Simpan" Then
If txtInfo(0).Text = "" Then
MsgBox "Isikan nomer ID Karyawan", vbInformation, "ID kurang"
If txtInfo(0).Enabled = False Then
txtInfo(0).Enabled = True
End If
txtInfo(0).SetFocus
Exit Sub
End If
If txtInfo(1).Text = "" Then
MsgBox "Isikan nama Karyawan", vbInformation, "nama kurang"
If txtInfo(1).Enabled = False Then
txtInfo(1).Enabled = True
End If
txtInfo(1).SetFocus
Exit Sub
End If
If txtInfo(2).Text = "" Then
MsgBox "Isikan jabatan", vbInformation, "Jabatan Kurang"
If txtInfo(2).Enabled = False Then
txtInfo(2).Enabled = True
End If
txtInfo(2).SetFocus
Exit Sub
End If
If txtInfo(3).Text = "" Then
MsgBox "Isikan tempat lahir", vbInformation, "Tempat lahir Kurang"
If txtInfo(3).Enabled = False Then
txtInfo(3).Enabled = True
End If
txtInfo(3).SetFocus
Exit Sub
End If
If txtInfo(4).Text = "" Then
MsgBox "Isikan Alamat", vbInformation, "Alamat Kurang"
If txtInfo(4).Enabled = False Then
txtInfo(4).Enabled = True
End If
txtInfo(4).SetFocus
Exit Sub
End If
End If
SQL = "Select * from tblKaryawan where NoKartu='" & txtInfo(0).Text & "'"
rs.Open SQL, db, adOpenDynamic, adLockOptimistic
'mencari kode SPBU
If rs.RecordCount > 0 Then
MsgBox "Record Kode telah ada", vbInformation, "Duplikasi"
If txtInfo(0).Enabled = False Then
txtInfo(0).Enabled = True
With txtInfo(0)
.Enabled = True
.SelStart = 0
.SelLength = Len(txtInfo(0))
.SetFocus
End With
End If
Exit Sub
End If
With rs
.AddNew
!NoKartu = txtInfo(0)
!Nama = txtInfo(1)
!Jabatan = txtInfo(2)
!TempatLahir = txtInfo(3)
!Alamat = txtInfo(4)
!JamKerja = 0
!TglLahir = dtTglLahir.Value
.UpDate
End With
rs.Close
Set rs = Nothing
MsgBox " Record karyawan telah ditambahkan", vbInformation, "Sukses"
For i = 0 To 4
txtInfo(i).Text = ""
Next
cmdTambah.Caption = "&" + "Tambah"
cmdEdit.Enabled = False
'cmdSearch.Enabled = False
Call TampilKaryawan
End Sub
Private Sub Form_Load()
Set db = New Connection
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\dbAbsensi.mdb;Persist Security Info=False"
db.CursorLocation = adUseClient
db.Open
TampilKaryawan
dtTglLahir.Value = Format(Date, "dd/mm/yy")
End Sub
Private Sub Grid_Click()
txtInfo(0).Text = ""
txtInfo(1).Text = ""
txtInfo(2).Text = ""
txtInfo(3).Text = ""
txtInfo(4).Text = ""
txtInfo(0).Text = Grid.TextMatrix(Grid.RowSel, 1)
If txtInfo(0).Text = "" Then
Exit Sub
Else
txtInfo(0).Text = Grid.TextMatrix(Grid.RowSel, 1)
txtInfo(1).Text = Grid.TextMatrix(Grid.RowSel, 2)
txtInfo(2).Text = Grid.TextMatrix(Grid.RowSel, 3)
txtInfo(3).Text = Grid.TextMatrix(Grid.RowSel, 4)
txtInfo(4).Text = Grid.TextMatrix(Grid.RowSel, 6)
dtTglLahir = Grid.TextMatrix(Grid.RowSel, 5)
cmdHapus.Enabled = True
cmdEdit.Enabled = True
cmdTambah.Enabled = False
End If
End Sub
Private Sub tmrBarcode_Timer()
End Sub
Private Sub Timer1_Timer()
With txtInfo(0)
.Enabled = True
.SelStart = 0
.SelLength = Len(txtInfo(0))
.SetFocus
End With
Timer1.Enabled = False
End Sub
Private Sub Timer2_Timer()
With txtCari
.Enabled = True
.SelStart = 0
.SelLength = Len(txtCari)
.SetFocus
End With
Timer2.Enabled = False
End Sub
Private Sub txtCari_Change()
If Len(txtCari) = 13 Then
Dim Temp As String
Temp = Fix(txtCari)
txtCari = Left(Temp, Len(Temp) - 1)
CariKaryawan
End If
End Sub
Private Sub txtCari_GotFocus()
txtCari.BackColor = &HC0FFFF
cmdCari.Enabled = True
End Sub
Private Sub CariKaryawan()
Dim gi As Integer
Dim SQL As String
'Grid.Clear
gi = 1
SQL = "Select * from tblKaryawan where NoKartu='" & txtCari.Text & "'"
Set rs = New Recordset
rs.Open SQL, db, adOpenDynamic, adLockOptimistic
If rs.RecordCount > 0 Then
Do While Not rs.EOF
txtInfo(0).Text = rs.Fields(0)
txtInfo(1).Text = rs.Fields(1)
dtTglLahir.Value = rs.Fields(6)
txtInfo(2).Text = rs.Fields(2)
txtInfo(3).Text = rs.Fields(3)
txtInfo(4).Text = rs.Fields(4)
gi = gi + 1
Exit Sub
rs.MoveNext
Loop
End If
JumlahOrder = gi - 1
rs.Close
Set rs = Nothing
End Sub
Private Sub txtCari_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
With txtCari
.Enabled = True
.SelStart = 0
.SelLength = Len(txtCari)
.SetFocus
End With
cmdCari_Click
End If
End Sub
Private Sub txtInfo_Change(Index As Integer)
'Timer1.Enabled = True
End Sub
Private Sub txtInfo_GotFocus(Index As Integer)
txtInfo(Index).BackColor = &HC0FFFF
If txtInfo(0).Text <> "" And txtInfo(1).Text <> "" And txtInfo(2).Text <> "" And txtInfo(3).Text <> "" And txtInfo(4).Text <> "" Then
If cmdTambah.Enabled = False Then
cmdTambah.Enabled = True
End If
End If
End Sub
Private Sub txtInfo_KeyPress(Index As Integer, KeyAscii As Integer)
If KeyAscii = 13 Then
If Index = 0 Then
With txtInfo(1)
.Enabled = True
.SelStart = 0
.SelLength = Len(txtInfo(1))
.SetFocus
End With
txtInfo(0).Enabled = False
End If
If Index = 1 Then
With txtInfo(2)
.Enabled = True
.SelStart = 0
.SelLength = Len(txtInfo(2))
.SetFocus
End With
txtInfo(1).Enabled = False
End If
If Index = 2 Then
With txtInfo(3)
.Enabled = True
.SelStart = 0
.SelLength = Len(txtInfo(3))
.SetFocus
End With
txtInfo(2).Enabled = False
End If
If Index = 3 Then
dtTglLahir.Enabled = True
With txtInfo(4)
.Enabled = True
.SelStart = 0
.SelLength = Len(txtInfo(4))
.SetFocus
End With
txtInfo(3).Enabled = False
End If
If Index = 4 Then
If cmdTambah.Enabled = False Then
cmdTambah.Enabled = True
cmdTambah.SetFocus
End If
txtInfo(4).Enabled = False
dtTglLahir.Enabled = False
End If
End If
End Sub
Private Sub txtInfo_LostFocus(Index As Integer)
txtInfo(Index).BackColor = &HE0E0E0
End Sub
Dan akhirnya silakan membandingkan dengan source code berikut.
Semoga bermanfaat.
