- Step 1: Create database and tabel in MS Access 2003
- Step 2 : Design form in VB 6.0
#Step 1 :
Create Folder "AddEditDeleteVB6"
Create Database use MS Access 2003 inside Folder "AddEditDeleteVB6"
Database name : db.mdb
Table name : tbl_employee
design table like below picture :
#Step 2 :
Open Visual Basic 6.0
Standard EXE - Open
Add component
Design Form1 like below picture
save your project inside Folder in same database db.mdb
and then place code below in form1 :
Dim Conn As New ADODB.Connectionand then, please run your Project, click here to download
Dim RSEmployee As ADODB.Recordset
Sub OpenDB()
Set Conn = New ADODB.Connection
Set RSEmployee = New ADODB.Recordset
Conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DB.mdb"
End Sub
Private Sub Command1_Click()
If Command1.Caption = "&Add" Then
Command1.Caption = "&Save"
Command2.Enabled = False
Command3.Enabled = False
Command4.Caption = "&Discard"
ReadyFill
Empty_form
Call AutoID
Text1.Enabled = False
Text2.SetFocus
Else
If Text1 = "" Or Text2 = "" Or Text3 = "" Or Text4 = "" Then
MsgBox "Please fill all form", vbInformation, "www.belajarvb.com"
Text2.SetFocus
Else
Dim SQLTambah As String
SQLAdd = "Insert Into TBL_Employee values ('" & Text1 & "','" & Text2 & "','" & Text3 & "','" & Text4 & "')"
Conn.Execute SQLAdd
MsgBox "Add data success", vbInformation, "www.belajarvb.com"
Form_Activate
Call Default_Form
End If
End If
End Sub
Private Sub Command2_Click()
If Command2.Caption = "&Edit" Then
Command1.Enabled = False
Command2.Caption = "&Save"
Command3.Enabled = False
Command4.Caption = "&Discard"
ReadyFill
Text1.SetFocus
Else
'Call SearchData
If RSEmployee.EOF Then
MsgBox "Kode Employee Salah..!!!" & vbNewLine & "Masukan Kode Employee kemudian tekan [ENTER]", vbInformation, "www.belajarvb.com"
Text1 = ""
Text1.SetFocus
ElseIf Text2 = "" Or Text3 = "" Or Text4 = "" Then
MsgBox "make sure all the forms filled", vbInformation, "Information"
Text2.SetFocus
Else
'End If
OpenDB
Dim SQLEdit As String
SQLEdit = "Update TBL_Employee Set Name= '" & Text2 & "', Address='" & Text3 & "', Phone='" & Text4 & "' where Employee_Id='" & Text1 & "'"
Conn.Execute SQLEdit
MsgBox "Edit data success", vbInformation, "www.belajarvb.com"
Form_Activate
End If
End If
End Sub
Private Sub Command3_Click()
If Command3.Caption = "&Delete" Then
Command1.Enabled = False
Command2.Enabled = False
Command4.Caption = "&Discard"
Empty_form
ReadyFill
Text1.SetFocus
End If
End Sub
Private Sub Command4_Click()
Select Case Command4.Caption
Case "&Close"
Unload Me
Case "&Discard"
NoTReadyFill
Default_Form
End Select
End Sub
Private Sub Form_Activate()
Call NoTReadyFill
Call Empty_form
Call Default_Form
Call OpenDB
Adodc1.ConnectionString = Conn
Adodc1.RecordSource = "tbl_Employee"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
End Sub
Private Sub Form_Load()
Call OpenDB
Adodc1.ConnectionString = Conn
Adodc1.RecordSource = "tbl_Employee"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
Text1.MaxLength = 6
End Sub
Private Sub AutoID()
Call OpenDB
RSEmployee.Open ("select * from Tbl_Employee Where Employee_id In(Select Max(Employee_id)From Tbl_Employee)Order By Employee_id Desc"), Conn
RSEmployee.Requery
Dim TBLSequence As String * 6
Dim TBLCount As Long
With RSEmployee
If .EOF Then
TBLSequence = "EM" + "0001"
Text1 = TBLSequence
Else
TBLCount = Right(!Employee_id, 4) + 1
TBLSequence = "EM" + Right("0000" & TBLCount, 4)
End If
Text1 = TBLSequence
End With
End Sub
Sub Empty_form()
Text1 = ""
Text2 = ""
Text3 = ""
Text4 = ""
End Sub
Private Sub ReadyFill()
Text1.Enabled = True
Text2.Enabled = True
Text3.Enabled = True
Text4.Enabled = True
End Sub
Private Sub NoTReadyFill()
Text1.Enabled = False
Text2.Enabled = False
Text3.Enabled = False
Text4.Enabled = False
End Sub
Private Sub ShowData()
With RSEmployee
If Not RSEmployee.EOF Then
Text2 = !Name
Text3 = !Address
Text4 = !Phone
End If
End With
End Sub
Private Sub Default_Form()
NoTReadyFill
Empty_form
Command1.Caption = "&Add"
Command2.Caption = "&Edit"
Command3.Caption = "&Delete"
Command4.Caption = "&Close"
Command1.Enabled = True
Command2.Enabled = True
Command3.Enabled = True
End Sub
Private Sub Text1_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
If KeyAscii = 13 Then
If Len(Text1) < 6 Then
MsgBox "Employee id must 6 Digit", vbInformation, "www.belajarvb.com"
Text1.SetFocus
Exit Sub
Else
Text2.SetFocus
End If
If Command1.Caption = "&Save" Then
Call SearchData
If Not RSEmployee.EOF Then
ShowData
MsgBox "Employee id already inputed", vbInformation, "www.belajarvb.com"
Empty_form
Text1.SetFocus
Else
Text2.SetFocus
End If
End If
If Command2.Caption = "&Save" Then
Call SearchData
If Not RSEmployee.EOF Then
ShowData
Text1.Enabled = False
Text2.SetFocus
Else
MsgBox "Employee Id not found!", vbInformation, "www.belajarvb.com"
Text1 = ""
Text1.SetFocus
End If
End If
If Command3.Enabled = True Then
Call SearchData
If Not RSEmployee.EOF Then
ShowData
Pesan = MsgBox("Are you sure want to delete ?", vbYesNo)
If Pesan = vbYes Then
Dim SQLHapus As String
SQLDelete = "Delete From TBL_Employee where Employee_Id= '" & Text1 & "'"
Conn.Execute SQLDelete
MsgBox "Deleted data success", vbInformation, "www.belajarvb.com"
Default_Form
Form_Activate
Else
Default_Form
Command3.SetFocus
End If
Else
MsgBox "Data not found!", vbInformation, "www.belajarvb.com"
Text1.SetFocus
End If
End If
End If
End Sub
Function SearchData()
Call OpenDB
RSEmployee.Open "Select * From TBL_Employee where Employee_Id='" & Text1 & "'", Conn
End Function
Private Sub Text2_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
If KeyAscii = 13 Then Text3.SetFocus
End Sub
Private Sub Text3_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
If KeyAscii = 13 Then Text4.SetFocus
End Sub
Private Sub Text4_KeyPress(KeyAscii As Integer)
KeyAscii = Asc(UCase(Chr(KeyAscii)))
If KeyAscii = 13 Then
If Command1.Enabled = True Then
Command1.SetFocus
ElseIf Command2.Enabled = True Then
Command2.SetFocus
End If
End If
End Sub
