VB 6.0 - Simple Add, Edit & Delete Access 2003

Here we will share about Simple Add, Edit & Delete Access 2003 in VB 6.0
  • 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 :
fill sample Employee

#Step 2 :
Open Visual Basic 6.0
Standard EXE - Open
Add component
Click Apply - OK
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.Connection
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
and then, please run your Project, click here to download

Subscribe to receive free email updates:

0 Response to "VB 6.0 - Simple Add, Edit & Delete Access 2003"

Post a Comment