How to INSERT UPDATE and DELETE Records From a Table (Executing SQL Statements in Access VBA)

How to INSERT UPDATE and DELETE Records From a Table (Executing SQL Statements in Access VBA Code).

🎓 Master Visual Basic .NET and Access Database By Building the Point Of Sale System (POS).

📲 Enroll Now: https://bit.ly/2WcbRhX

[Screenshot]

Part 1 Form Design

Part 2 - Adding VBA Code

Special Part: CRUD Operations

[Source Code]

Part 2 VBA Code:

Option Compare Database
Option Explicit

Private Sub cboAccSQL_AfterUpdate()

    Call EnableTextboxes

    cmdExecute.Enabled = True
    cmdClear.Enabled = True

    Me.Refresh
    Me.Requery '---DoCmd.Requery

    Dim strSQL As String
    strSQL = "SELECT * FROM Explanations " & _
                     "WHERE [SQL Statement]='" & Me.cboAccSQL.Value & "';"
    Me.RecordSource = strSQL
    Forms![Command Form]!txtExplanation.ControlSource = "Explanation"

        If cboAccSQL = "INSERT" Then
        cmdExecute.Caption = "SQL INSERT"
        chkDelete.Value = 0
        chkDelete.Enabled = False
            txtID.Value = "(Auto Number)"
            txtID2.Visible = False
            txtID.Enabled = False
                txtProduct.Enabled = True
                txtQuantity.Enabled = True
                txtProduct.SetFocus
                    Label1.Visible = False
                    Label2.Visible = False
        ElseIf cboAccSQL = "DELETE" Then
        cmdExecute.Caption = "SQL DELETE"
        chkDelete.Enabled = True
        chkDelete.Value = 0
            txtID.Enabled = True
            txtID.SetFocus
                txtProduct.Value = "Disabled"
                txtQuantity.Value = "Disabled"
                txtProduct.Enabled = False
                txtQuantity.Enabled = False
        Else
        cmdExecute.Caption = "SQL UPDATE"
        chkDelete.Enabled = False
        chkDelete.Value = 0
            txtID.Enabled = True
            txtID2.Visible = False
                txtProduct.Enabled = True
                txtQuantity.Enabled = True
                txtID.SetFocus
                    Label1.Visible = False
                    Label2.Visible = False

        End If

End Sub

Private Sub chkDelete_AfterUpdate()
txtID.SetFocus
End Sub

Private Sub chkDelete_Click()

If Me.chkDelete.Value = -1 Then 'Checked

    Label1.Visible = True
    Label2.Visible = True
    txtID2.Visible = True

Else

    Label1.Visible = False
    Label2.Visible = False
    txtID2.Visible = False

End If

End Sub

Private Sub cmdClear_Click()

If IsNull(cboAccSQL) Or Len(cboAccSQL & vbNullString) = 0 Then
    cboAccSQL.SetFocus
    Exit Sub

Else

    Call Form_Load
    Call EnableTextboxes
    Me.cboAccSQL.Value = ""
    cboAccSQL.SetFocus
    Label1.Visible = False
    Label2.Visible = False
    txtID2.Visible = False
    Me.Refresh
    Me.Requery '---DoCmd.Requery

End If

End Sub

Private Sub cmdExecute_Click()

On Error GoTo ExecuteErr

If IsNull(cboAccSQL) Or Len(cboAccSQL & vbNullString) = 0 Then

    MsgBox "No SQL Statement selected, please select from the list", vbInformation, "Select SQL Command [Combo Box]"
    Call DisableTextBoxes
    cboAccSQL.SetFocus
    cmdExecute.Enabled = False
    cmdClear.Enabled = False
    Exit Sub

Else

    Dim strSQL As String
    strSQL = cboAccSQL

        Select Case strSQL
            Case Is = "INSERT"
                Call sqlInsert
            Case Is = "UPDATE"
                Call sqlUpdate
            Case Is = "DELETE"
                Call sqlDelete
            End Select

End If

ErrorExit:
    Exit Sub

ExecuteErr:

    If Err.Number <> 0 Then
        MsgBox "Error Number : " & Err.Number & vbCrLf & vbCrLf & _
                        "Description : " & Err.Description, vbCritical, "Execute SQL Statement Error!"

        Resume ErrorExit

    End If

End Sub

Private Sub Form_Load()

    Me.RecordSource = ""
    Forms![Command Form]!txtExplanation.ControlSource = ""
    chkDelete.Value = 0
    chkDelete.Enabled = False
    cmdExecute.Caption = "Execute Me!" '---Sorry sir!

End Sub

Private Sub DisableTextBoxes()
Dim con As Control
Dim text As TextBox
    For Each con In Me.Controls
        If TypeOf con Is TextBox Then
            Set text = con
                If Left(text.Name, 4) <> "txtE" Then
                    With text
                        .Value = ""
                        .Enabled = False
                    End With
                End If
        End If
    Next con
End Sub
Private Sub EnableTextboxes()
Dim con As Control
Dim text As TextBox
    For Each con In Me.Controls
        If TypeOf con Is TextBox Then
            Set text = con
                If Left(text.Name, 4) <> "txtE" Then
                    With text
                        .Enabled = True
                        .Value = ""
                    End With
                End If
        End If
    Next con

End Sub

'----------------------------------End Part 2

Part 3 - SQL INSERT INTO Statement

Part 3 - VBA Code

Private Sub sqlInsert()

If IsNull(txtProduct) Or Len(txtProduct & vbNullString) = 0 Or _

IsNull(txtQuantity) Or Len(txtQuantity & vbNullString) = 0 Then

MsgBox "Please enter the valid data for all the required fields.", vbInformation, "Information"

Exit Sub

Else

If Not IsNumeric(txtQuantity) Or Int(Val(Me.txtQuantity)) <> Val(Me.txtQuantity) Then

MsgBox "Sorry, please enter number only for the quantity field" & vbCrLf & _

"and decimal numbers not allowed!" & vbCrLf & _

"Thank you", vbCritical, "Numbers Only pls!"

txtQuantity.SetFocus

Else

If DCount("Product", "Products", "Product = '" & txtProduct.Value & "'") > 0 Then

Dim strProd As String

strProd = txtProduct

Dim msg As String

msg = "The value already exists in the databse." & vbCrLf & _

"You entered a duplicate value or sequence of values that must be Unique for every records."

MsgBox "^_^ " & strProd & " ^_^" & vbCrLf & _

msg, vbExclamation, "Duplicate Data!"

txtProduct.SetFocus

Exit Sub

Else

Dim dbs As Database

Set dbs = CurrentDb

dbs.Execute " INSERT INTO [Products] " _

& "(Product,Quantity) VALUES " _

& "('" & txtProduct & "', '" & txtQuantity & "');"

dbs.Close

Set dbs = Nothing

Call ReOpenTBL

End If

End Sub

'----------------------------------End Part 3

Part 4 - SQL Update Statement

Part 4 VBA Code :

Private Sub sqlUpdate()

If IsNull(txtID) Or Len(txtID & vbNullString) = 0 Or IsNull(txtProduct) Or Len(txtProduct & vbNullString) = 0 Or _
IsNull(txtQuantity) Or Len(txtQuantity & vbNullString) = 0 Then

MsgBox "Please enter the valid data for all the required fields.", vbInformation, "Information"
Exit Sub

Else

If DCount("ID", "Products", "ID = " & Forms![Command Form]!txtID) > 0 Then

If DCount("Product", "Products", "Product = '" & txtProduct & "'") > 0 Then

Dim Response As Integer
Dim prod As String

prod = txtProduct
Response = MsgBox("^_^ " & prod & " ^_^" & vbCrLf & _
"The value already exists in the database." & vbCrLf & _
"only Quantity will be updated! " & " Do you wish to continue?", vbYesNo + vbInformation, "Duplicate data! - Continue?")

If Response = vbYes Then

Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute " UPDATE [Products] " _
& "SET Quantity = '" & txtQuantity & "'" _
& "WHERE ID = " & txtID & ";", dbFailOnError
dbs.Close
Set dbs = Nothing

Call ReOpenTBL

Else

Exit Sub

End If

Else

Dim ddbs As Database
Set ddbs = CurrentDb
ddbs.Execute " UPDATE [Products] " _
& "SET Product = '" & txtProduct & "', Quantity = '" & txtQuantity & "'" _
& "WHERE ID = " & txtID & ";", dbFailOnError
ddbs.Close
Set ddbs = Nothing

Call ReOpenTBL

MsgBox "The data have been updated!.", vbInformation, "SQL Update Results"

End If

Else

MsgBox "Could not find Product ID [ไม่พบรหัสสินค้าที่ระบุครับ]", vbInformation, "Product ID not found!"

End If

End If

End Sub

'----------------------------------End Part 4

Part 5 - SQL Delete Statement

Part 5 VBA Code:

Private Sub sqlDelete()

If Me.chkDelete.Value = 0 Then '-----"Unchecked

If IsNull(txtID) Or Len(txtID & vbNullString) = 0 Then

MsgBox "Please endter Product ID", vbInformation
txtID.SetFocus

Else

Dim proID As Integer
proID = txtID

If DCount("ID", "Products", "ID = " & Forms![Command Form]!txtID) > 0 Then

Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute " DELETE * FROM " _
& "[Products] WHERE ID = " & txtID & ";"
dbs.Close
Set dbs = Nothing

Call ReOpenTBL

Else

MsgBox "Cound not find Product ID: " & proID, vbInformation, "Product ID not found!."
txtID.SetFocus

End If

End If

Else

If IsNull(txtID) Or Len(txtID & vbNullString) = 0 Or IsNull(txtID2) Or Len(txtID2 & vbNullString) = 0 Then

MsgBox "Please enter the valid data for all the required fields. [Product ID]", vbInformation, "Information!"

Else

If DCount("ID", "Products", "ID Between " & Forms![Command Form]!txtID & " And " & Forms![Command Form]!txtID2) > 0 Then

Dim dbsx As Database
Set dbsx = CurrentDb
dbsx.Execute " DELETE * FROM " _
& "[Products] WHERE ID Between " & txtID & " And " & txtID2 & ";"
dbsx.Close
Set dbsx = Nothing

Call ReOpenTBL

Else

MsgBox "Could not find Product ID", vbInformation, "Sorry!, no results"
txtID.SetFocus

End If

End If

End If
End Sub

Private Sub ReOpenTBL()
DoCmd.Close acTable, "Products", acSavePrompt
DoCmd.OpenTable "Products", acViewNormal, acEdit
End Sub

★ Follow me on ★

Twitter

» https://twitter.com/#!/IBasskung

Facebook (English)

» https://www.facebook.com/CodeAMinute

Facebook (สำหรับคนไทย)

» https://www.facebook.com/IbasskungTutorial

Google+

» https://plus.google.com/u/0/107523775437712370629/posts

YouTube Channel

» http://www.youtube.com/user/TheRockmankung

Dailymotion Channel

» http://www.dailymotion.com/Ibasskung-Courses

Free Source Code can be found here:

» https://goo.gl/UNNzp2

Thank you very much.

ขอบคุณครับ.

#CodeAMinute #AccessVBA #Programming #IbasskungTutorial #IbasskungCourses

Comments

UnknownJune 26, 2015 at 2:42 PM
Hello,
how it will work if you want to insert, update, delete data more than one data? For example from a tempTable with 100 rows and 10 culomns. Culd be possible to select column?
UnknownAugust 6, 2015 at 5:34 PM
thank you , thank you , it is very very very good
Micheal AlexanderDecember 17, 2020 at 11:31 PM
This is a really informative knowledge, Thanks for posting this informative Information. VBA Online Course

Visual Basic .NET and C# Tutorial with Source Code

Search This Blog

How to INSERT UPDATE and DELETE Records From a Table (Executing SQL Statements in Access VBA)

Comments

Post a Comment