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 Karyawan

Tabel Jam Kerja

Tabel Jam

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:

MSFlexGrid

kemudian klik Apply.

3.  Selanjutnya buatlah tampilan berikut:

Form Absensi 1

4. Kemudian masukkan komponen DtPicker dengan menekan Ctrl+T atau melalui menu Project–> Component, seperti ditunjukkan gambar berikut:

DtPicker

5. Kemudian buat tampilan berikut, dengan menambahkan kotak isian masukan:

form input data

6. Dengan menambahkan beberapa tombol yang diperlukan, Car, Tambah, Edit, Hapus, dan Close tampilan akhir akan seperti berikut:

form database karyawan

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:

1 Dim db As Connection
2  Dim rs As Recordset
3  Dim Saldo As Long, SaldoAwal As Long
4  Dim <a title="SQL" href="http://www.iso.org/iso/catalogue_detail.htm?csnumber=45498" target="_blank" rel="homepage">SQL</a> As String

2. Kemudian buka menu Project –> Reference, centang pada pilihan berikut:

Setting Reference

3.  Isikan coding berikut:

01 Private Sub Form_Load()
02  Set db = New Connection
03
04 db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\dbAbsensi.mdb;Persist Security Info=False"
05
06 db.CursorLocation = adUseClient
07  db.Open
08
09 dtTglLahir.Value = Format(Date, "dd/mm/yy")
10  End Sub

Lengkapilah coding menjadi berikut:

001 Public JumlahKaryawan As Integer
002 Dim i As Integer
003 Dim db As Connection
004 Dim rs As Recordset
005
006 Private Sub cmdBaca_Click()
007 If cmdBaca.Caption = "Baca" Then
008 tmrBarcode.Enabled = True
009 cmdBaca.Caption = "Stop"
010 'Call KunciBacaBarcode
011 txtCari.SetFocus
012 'cmdSearch.Caption = "Baca"
013 ElseIf cmdBaca.Caption = "Stop" Then
014 tmrBarcode.Enabled = True
015 cmdBaca.Caption = "Baca"
016 End If
017 End Sub
018
019 Private Sub cmdCari_Click()
020 CariKaryawan
021 End Sub
022
023 Private Sub cmdCetak_Click()
024 FormBarcode.Show
025 FormBarcode.Text1 = txtInfo(0).Text
026
027 End Sub
028
029 Private Sub cmdClose_Click()
030 Unload Me
031 End Sub
032 Function LihatNoKaryawanAkhir() As Integer
033 Dim NoAkhir As Integer
034 Set rs = New Recordset
035 Dim SQL As String
036 Dim gi As Integer
037 NoAkhir = 0
038
039 SQL = "Select * from tblKaryawan"
040 Set rs = New Recordset
041 rs.Open SQL, db, adOpenDynamic, adLockOptimistic
042 If rs.RecordCount > 0 Then
043 Do While Not rs.EOF
044 If NoAkhir < rs.Fields(0) Then
045 NoAkhir = rs.Fields(0)
046 End If
047 gi = gi + 1
048 rs.MoveNext
049 Loop
050 End If
051 rs.Close
052 Set rs = Nothing
053 LihatNoKaryawanAkhir = NoAkhir
054
055 End Function
056 Sub Buka()
057 For i = 0 To 4
058 txtInfo(i).Enabled = True
059 Next
060 dtTglLahir.Enabled = True
061 End Sub
062 Sub Kunci()
063 For i = 0 To 4
064 txtInfo(i).Enabled = False
065 Next
066 dtTglLahir.Enabled = False
067 End Sub
068 Private Sub TampilKaryawan()
069 Dim gi As Integer
070 Dim SQL As String
071
072 Grid.Rows = 2
073 Grid.Width = 11535
074 Grid.ColWidth(0) = 500
075 Grid.ColWidth(1) = 1500
076 Grid.ColWidth(2) = 2000
077 Grid.ColWidth(3) = 2000
078 Grid.ColWidth(4) = 2000
079 Grid.ColWidth(5) = 1200
080 Grid.ColWidth(6) = 1200
081 Grid.ColWidth(6) = 2200
082 Grid.Clear
083 gi = 1
084 Grid.TextMatrix(0, 0) = "No"
085 Grid.TextMatrix(0, 1) = "Kode ID"
086 Grid.TextMatrix(0, 2) = "Nama"
087 Grid.TextMatrix(0, 3) = "Jabatan"
088 Grid.TextMatrix(0, 4) = "Tempat Lahir"
089 Grid.TextMatrix(0, 5) = "Tgl Lahir"
090 Grid.TextMatrix(0, 6) = "Alamat"
091 Grid.TextMatrix(0, 7) = "Jam Kerja"
092
093 SQL = "Select * from tblKaryawan"
094 Set rs = New Recordset
095 rs.Open SQL, db, adOpenDynamic, adLockOptimistic
096 If rs.RecordCount > 0 Then
097 Do While Not rs.EOF
098 Grid.TextMatrix(gi, 0) = gi
099 Grid.TextMatrix(gi, 1) = rs.Fields(0)
100 Grid.TextMatrix(gi, 2) = rs.Fields(1)
101 Grid.TextMatrix(gi, 3) = rs.Fields(2)
102 Grid.TextMatrix(gi, 4) = rs.Fields(3)
103 Grid.TextMatrix(gi, 5) = rs.Fields(6)
104 Grid.TextMatrix(gi, 6) = rs.Fields(4)
105 gi = gi + 1
106 Grid.Rows = Grid.Rows + 1
107 rs.MoveNext
108 Loop
109 End If
110 JumlahKaryawan = gi - 1
111 rs.Close
112 Set rs = Nothing
113
114 End Sub
115 Sub Simpan()
116 Set rs = New Recordset
117 Dim SQL As String
118 SQL = "Select * from  tblKaryawan where NoKartu='" & txtInfo(0).Text & "'"
119 rs.Open SQL, db, adOpenDynamic, adLockOptimistic
120
121 With rs
122 .AddNew
123 !NoKartu = txtInfo(0)
124 !Nama = txtInfo(1)
125 !Jabatan = txtInfo(2)
126 !TempatLahir = txtInfo(3)
127 !Alamat = txtInfo(6)
128 !TanggalLahir = dtTglLahir.Value
129 .UpDate
130 End With
131 rs.Close
132 Set rs = Nothing
133 MsgBox " Record order telah ditambahkan", vbInformation, "Sukses"
134
135 Call TampilKaryawan
136 End Sub
137
138 Sub UpDate()
139 Set rs = New Recordset
140 Dim SQL As String
141 'sql = "select * from tblCostumer"
142 SQL = "Select * from  tblKaryawan where NoKartu='" & txtInfo(0).Text & "'"
143 rs.Open SQL, db, adOpenDynamic, adLockOptimistic
144 With rs
145 .UpDate
146 !NoKartu = txtInfo(0)
147 !Nama = txtInfo(1)
148 !Jabatan = txtInfo(2)
149 !TempatLahir = txtInfo(3)
150 !Alamat = txtInfo(4)
151 !TglLahir = dtTglLahir
152 .UpDate
153 End With
154 rs.Close
155 Set rs = Nothing
156 MsgBox "Record telah diEdit", vbInformation, "Sukses"
157 txtInfo(0) = ""
158 txtInfo(1) = ""
159 txtInfo(2) = ""
160 txtInfo(3) = ""
161 txtInfo(4) = ""
162 Call TampilKaryawan
163 End Sub
164 Private Sub cmdEdit_Click()
165 Set rs = New Recordset
166 Dim SQL As String
167
168 If txtInfo(0).Enabled = False Or txtInfo(1).Enabled = False Or txtInfo(2).Enabled = False Or txtInfo(3).Enabled = False Then
169
170 txtInfo(1).Enabled = True
171 txtInfo(2).Enabled = True
172 txtInfo(3).Enabled = True
173 txtInfo(4).Enabled = True
174 dtTglLahir.Enabled = True
175 End If
176 If cmdEdit.Caption = "&" + "Edit" Then
177 cmdEdit.Caption = "&" + "Update"
178 txtInfo(1).SetFocus
179 Exit Sub
180 ElseIf cmdEdit.Caption = "&" + "Update" Then
181 cmdEdit.Caption = "&" + "Edit"
182 Call UpDate
183 cmdTambah.Enabled = True
184 cmdEdit.Enabled = False
185 cmdHapus.Enabled = False
186 Call Kunci
187 End If
188
189 If cmdEdit.Caption = "&" + "Batal" Then
190 cmdEdit.Caption = "&" + "Edit"
191 cmdEdit.Enabled = False
192 cmdTambah.Enabled = True
193 cmdTambah.Caption = "&" + "Tambah"
194 For i = 1 To 3
195 txtInfo(i).Text = ""
196 Next
197 Call Kunci
198 Exit Sub
199 End If
200 End Sub
201 Sub Hapus()
202 Set rs = New Recordset
203 Dim SQL As String
204 SQL = "select * from tblKaryawan where NoKartu='" + txtInfo(0).Text + "'"
205 rs.Open SQL, db, adOpenDynamic, adLockOptimistic
206 If rs.RecordCount > 0 Then
207 rs.Delete
208 MsgBox "Record karyawan(" & txtInfo(0).Text & ")", vbInformation, "Karyawan"
209 End If
210 rs.Close
211 Set rs = Nothing
212 End Sub
213 Private Sub cmdHapus_Click()
214
215 Dim NV As Integer
216
217 NV = MsgBox("Apakah record karyawan '" & txtInfo(0).Text & "' akan dihapus? ", vbYesNoCancel, "Konfirmasi")
218 If NV = vbYes Then
219 Call Hapus
220 End If
221 If NV = vbCancel Then
222 Exit Sub
223 End If
224 If NV = vbNo Then
225 Exit Sub
226 End If
227 txtInfo(0).Text = ""
228 txtInfo(1).Text = ""
229 txtInfo(2).Text = ""
230 txtInfo(3).Text = ""
231 txtInfo(4).Text = ""
232 dtTglLahir.Value = Format(Date, "dd/mm/yy")
233 Call TampilKaryawan
234 cmdHapus.Enabled = False
235 End Sub
236
237 Sub KunciBacaBarcode()
238 Call Kunci
239 txtInfo(0).Enabled = True
240 End Sub
241 Private Sub cmdTambah_Click()
242 Dim KodeTemp As String
243 Set rs = New Recordset
244 Dim SQL As String
245
246 If cmdTambah.Caption = "&" + "Batal" Then
247 cmdTambah.Caption = "&" + "Tambah"
248 cmdTambah.Enabled = True
249 cmdEdit.Enabled = False
250 cmdHapus.Enabled = False
251 For i = 1 To 4
252 txtInfo(i).Text = ""
253 Next
254 If cmdEdit.Caption = "&" + "Update" Then
255 cmdEdit.Caption = "&" + "Edit"
256 End If
257 dtTglLahir.Value = Format(Date, "dd/mm/yy")
258 Exit Sub
259 End If
260
261 If cmdTambah.Caption = "&" + "Tambah" Then
262 cmdTambah.Caption = "&" + "Simpan"
263 cmdEdit.Caption = "&" + "Batal"
264 cmdTambah.Enabled = False
265 cmdEdit.Enabled = True
266 If txtInfo(0).Enabled = False Then
267 txtInfo(0).Enabled = True
268 End If
269 txtInfo(0).SetFocus
270 Exit Sub
271 ElseIf cmdTambah.Caption = "&" + "Simpan" Then
272 If txtInfo(0).Text = "" Then
273 MsgBox "Isikan nomer ID Karyawan", vbInformation, "ID kurang"
274 If txtInfo(0).Enabled = False Then
275 txtInfo(0).Enabled = True
276 End If
277 txtInfo(0).SetFocus
278 Exit Sub
279 End If
280 If txtInfo(1).Text = "" Then
281 MsgBox "Isikan nama Karyawan", vbInformation, "nama kurang"
282 If txtInfo(1).Enabled = False Then
283 txtInfo(1).Enabled = True
284 End If
285 txtInfo(1).SetFocus
286 Exit Sub
287 End If
288 If txtInfo(2).Text = "" Then
289 MsgBox "Isikan jabatan", vbInformation, "Jabatan Kurang"
290 If txtInfo(2).Enabled = False Then
291 txtInfo(2).Enabled = True
292 End If
293 txtInfo(2).SetFocus
294 Exit Sub
295 End If
296 If txtInfo(3).Text = "" Then
297 MsgBox "Isikan tempat lahir", vbInformation, "Tempat lahir Kurang"
298 If txtInfo(3).Enabled = False Then
299 txtInfo(3).Enabled = True
300 End If
301 txtInfo(3).SetFocus
302 Exit Sub
303 End If
304 If txtInfo(4).Text = "" Then
305 MsgBox "Isikan Alamat", vbInformation, "Alamat Kurang"
306 If txtInfo(4).Enabled = False Then
307 txtInfo(4).Enabled = True
308 End If
309 txtInfo(4).SetFocus
310 Exit Sub
311 End If
312 End If
313
314 SQL = "Select * from  tblKaryawan where NoKartu='" & txtInfo(0).Text & "'"
315 rs.Open SQL, db, adOpenDynamic, adLockOptimistic
316
317 'mencari kode SPBU
318 If rs.RecordCount > 0 Then
319 MsgBox "Record Kode telah ada", vbInformation, "Duplikasi"
320 If txtInfo(0).Enabled = False Then
321 txtInfo(0).Enabled = True
322 With txtInfo(0)
323 .Enabled = True
324 .SelStart = 0
325 .SelLength = Len(txtInfo(0))
326 .SetFocus
327 End With
328 End If
329 Exit Sub
330 End If
331
332 With rs
333 .AddNew
334 !NoKartu = txtInfo(0)
335 !Nama = txtInfo(1)
336 !Jabatan = txtInfo(2)
337 !TempatLahir = txtInfo(3)
338 !Alamat = txtInfo(4)
339 !JamKerja = 0
340 !TglLahir = dtTglLahir.Value
341 .UpDate
342 End With
343 rs.Close
344 Set rs = Nothing
345 MsgBox " Record karyawan telah ditambahkan", vbInformation, "Sukses"
346 For i = 0 To 4
347 txtInfo(i).Text = ""
348 Next
349 cmdTambah.Caption = "&" + "Tambah"
350 cmdEdit.Enabled = False
351 'cmdSearch.Enabled = False
352 Call TampilKaryawan
353 End Sub
354
355 Private Sub Form_Load()
356 Set db = New Connection
357
358 db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "\dbAbsensi.mdb;Persist Security Info=False"
359
360 db.CursorLocation = adUseClient
361 db.Open
362 TampilKaryawan
363 dtTglLahir.Value = Format(Date, "dd/mm/yy")
364 End Sub
365
366 Private Sub Grid_Click()
367 txtInfo(0).Text = ""
368 txtInfo(1).Text = ""
369 txtInfo(2).Text = ""
370 txtInfo(3).Text = ""
371 txtInfo(4).Text = ""
372 txtInfo(0).Text = Grid.TextMatrix(Grid.RowSel, 1)
373 If txtInfo(0).Text = "" Then
374 Exit Sub
375 Else
376 txtInfo(0).Text = Grid.TextMatrix(Grid.RowSel, 1)
377 txtInfo(1).Text = Grid.TextMatrix(Grid.RowSel, 2)
378 txtInfo(2).Text = Grid.TextMatrix(Grid.RowSel, 3)
379 txtInfo(3).Text = Grid.TextMatrix(Grid.RowSel, 4)
380 txtInfo(4).Text = Grid.TextMatrix(Grid.RowSel, 6)
381 dtTglLahir = Grid.TextMatrix(Grid.RowSel, 5)
382 cmdHapus.Enabled = True
383 cmdEdit.Enabled = True
384 cmdTambah.Enabled = False
385 End If
386 End Sub
387
388 Private Sub tmrBarcode_Timer()
389
390 End Sub
391
392 Private Sub Timer1_Timer()
393 With txtInfo(0)
394 .Enabled = True
395 .SelStart = 0
396 .SelLength = Len(txtInfo(0))
397 .SetFocus
398 End With
399 Timer1.Enabled = False
400 End Sub
401
402 Private Sub Timer2_Timer()
403 With txtCari
404 .Enabled = True
405 .SelStart = 0
406 .SelLength = Len(txtCari)
407 .SetFocus
408 End With
409 Timer2.Enabled = False
410 End Sub
411
412 Private Sub txtCari_Change()
413
414 If Len(txtCari) = 13 Then
415 Dim Temp As String
416 Temp = Fix(txtCari)
417 txtCari = Left(Temp, Len(Temp) - 1)
418 CariKaryawan
419 End If
420 End Sub
421
422 Private Sub txtCari_GotFocus()
423 txtCari.BackColor = &HC0FFFF
424 cmdCari.Enabled = True
425 End Sub
426 Private Sub CariKaryawan()
427 Dim gi As Integer
428 Dim SQL As String
429
430 'Grid.Clear
431 gi = 1
432 SQL = "Select * from  tblKaryawan where NoKartu='" & txtCari.Text & "'"
433 Set rs = New Recordset
434 rs.Open SQL, db, adOpenDynamic, adLockOptimistic
435 If rs.RecordCount > 0 Then
436 Do While Not rs.EOF
437 txtInfo(0).Text = rs.Fields(0)
438 txtInfo(1).Text = rs.Fields(1)
439 dtTglLahir.Value = rs.Fields(6)
440 txtInfo(2).Text = rs.Fields(2)
441 txtInfo(3).Text = rs.Fields(3)
442 txtInfo(4).Text = rs.Fields(4)
443 gi = gi + 1
444 Exit Sub
445 rs.MoveNext
446 Loop
447 End If
448
449 JumlahOrder = gi - 1
450 rs.Close
451 Set rs = Nothing
452 End Sub
453
454 Private Sub txtCari_KeyPress(KeyAscii As Integer)
455 If KeyAscii = 13 Then
456 With txtCari
457 .Enabled = True
458 .SelStart = 0
459 .SelLength = Len(txtCari)
460 .SetFocus
461 End With
462 cmdCari_Click
463 End If
464 End Sub
465
466 Private Sub txtInfo_Change(Index As Integer)
467 'Timer1.Enabled = True
468 End Sub
469
470 Private Sub txtInfo_GotFocus(Index As Integer)
471 txtInfo(Index).BackColor = &HC0FFFF
472 If txtInfo(0).Text <> "" And txtInfo(1).Text <> "" And txtInfo(2).Text <> "" And txtInfo(3).Text <> "" And txtInfo(4).Text <> "" Then
473 If cmdTambah.Enabled = False Then
474 cmdTambah.Enabled = True
475 End If
476 End If
477
478 End Sub
479
480 Private Sub txtInfo_KeyPress(Index As Integer, KeyAscii As Integer)
481 If KeyAscii = 13 Then
482 If Index = 0 Then
483 With txtInfo(1)
484 .Enabled = True
485 .SelStart = 0
486 .SelLength = Len(txtInfo(1))
487 .SetFocus
488 End With
489 txtInfo(0).Enabled = False
490 End If
491 If Index = 1 Then
492 With txtInfo(2)
493 .Enabled = True
494 .SelStart = 0
495 .SelLength = Len(txtInfo(2))
496 .SetFocus
497 End With
498 txtInfo(1).Enabled = False
499 End If
500 If Index = 2 Then
501 With txtInfo(3)
502 .Enabled = True
503 .SelStart = 0
504 .SelLength = Len(txtInfo(3))
505 .SetFocus
506 End With
507 txtInfo(2).Enabled = False
508 End If
509 If Index = 3 Then
510 dtTglLahir.Enabled = True
511 With txtInfo(4)
512 .Enabled = True
513 .SelStart = 0
514 .SelLength = Len(txtInfo(4))
515 .SetFocus
516 End With
517 txtInfo(3).Enabled = False
518 End If
519 If Index = 4 Then
520 If cmdTambah.Enabled = False Then
521 cmdTambah.Enabled = True
522 cmdTambah.SetFocus
523 End If
524 txtInfo(4).Enabled = False
525 dtTglLahir.Enabled = False
526 End If
527 End If
528 End Sub
529
530 Private Sub txtInfo_LostFocus(Index As Integer)
531 txtInfo(Index).BackColor = &HE0E0E0
532 End Sub

Dan akhirnya silakan membandingkan dengan source code berikut.

Semoga bermanfaat.

Comments

comments