How CRUD System Works in MS Access

There can be several database structures to use as a CRUD System to understand the four fundamental functions practically. For the best practical example, use Customer related database.

Let’s explore the methods behind the Access CRUD System and how to implement it.

Steps of CRUD System in MS Access

Let’s see how to enable users to perform essential tasks such as adding new records, retrieving existing data, updating information, and removing obsolete entries.

Creating Customer Table

Create a customer table with these fields:

After creating the table, add some data to the table.

Read | Customer List Form

Retrieve data from the database by creating a form based on the Customer’s Table by selecting the “Tabular” layout from the Form Wizard.

• Add some designs and alignments to the form
• Create a Button named “New” which will be used to pop up the Customer form to add a new record.
• Add 2 more buttons related to Update and Delete the records we well.

Create | Customer Add Form

• Create a new form to Add Customers
• Add unbound text boxes with proper labels
• Add an unbound combo box for “Terms of Payment” which contains a Row Source Type as “Value List” and Row Source Options would be Cash, Cheque, and Other.
• Rename all text boxes to unique names like “txt_name” for the Name text box.
• Add Close and SAVE buttons.

SAVE button code for New Customer form:

Private Sub btn_save_Click()
On Error GoTo errnew

If IsNull(Me.txt_name) = True Then
    MsgBox "Mandatory fields must be filled...", vbExclamation, "| Fill the fields |"
        Else
    Set rst = CurrentDb.OpenRecordset("Customer Tbl", dbOpenDynaset, dbSeeChanges)
        With rst
            .AddNew
            .Fields("cname") = Me.txt_name
            .Fields("cphone") = Me.txt_phone
            .Fields("cemail") = Me.txt_email
            .Fields("caddress") = Me.txt_address
            .Fields("copenb") = Me.txt_openingbalance
            .Fields("cremarks") = Me.txt_remarks
            .Fields("cbalance") = Me.txt_balance
            .Fields("cterms") = Me.txt_terms
            .Update
        End With
    Set rst = Nothing
End If
Forms![Customer Form].Requery
DoCmd.Close acForm, "NewCustomer Form", acSaveYes

errnew:
    cancel = True
    Exit Sub
End Sub

Code to open New Customer Form button:

Private Sub btn_new_Click()
DoCmd.OpenForm "NewCustomer Form", , , , , acDialog

End Sub

Update | Customer Update Form

To modify the existing customer record create an UPDATE form (You can copy the New Customer Form and rename it but all the properties have to be changed accordingly due to unique names.

This time the table and the textboxes will be bound to the customer table.

Code for UPDATE button to open Update Form:

Private Sub btn_update_Click()
DoCmd.OpenForm "UpdateCustomer Form", , , "cid = " & Me.cid, , acDialog

End Sub

Delete | Customer Record Delete

The operation to remove the customer record, here is the code for the DELETE button:

Private Sub btn_delete_Click()
'Let make a Yes No response while deleting the record

resp = MsgBox("Do you really want to delete the record?", vbYesNo + vbQuestion, "|Delete Confirmation|")
    If resp = vbYes Then

    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True

        Else
            Cancel = True
            Exit Sub
    End If
End Sub