Database Karyawan Dengan VB

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.