MS Access 2007 tutorial: Recordset add, update, delete records


Recordset: add, update, delete records

In previous example, you learned how to add, update and delete records in a table by using SQL INSERT, UPDATE, and DELETE statements. In this example, you will learn the alternative way to add, update, and delete a record by using Recordset Methods. The example illustrates a CategoryInfo3 form that has two text boxes(CategoryID, CategoryName), a list box(lstData), eight command buttons(MoveFirst, MoveLast, MoveNext, Save, Update, Delete, and Clear). See the figure below:

 VBA example - Microsoft Access VBA CategoryInfo3 Form  

- Save command button is used for adding new record to a table.

- Update command button is used for updating the existing records in a table.

- Delete command button is used for deleting the existing records in a table.

- Clear command button is used to clear data in the two text boxes.

-When the form loads, the three command buttons Save, Update, and Delete are disabled. All records are populated in the list box; the first record displays in the two text boxes and  that record is selected in the list box automatically(See CategoryInfo3 form above).

-If you want to add new records, click Save command button. The saved record will display in the list box immediately.

-If you want to change category information, double click any record in the list box, make changing the category information(only category name is changeable) then click Update command button. You can see the updated record in the list box.

-If you want to delete any record in a table, double click that record and click Delete command buttons. The record will be removed from a table, text boxes and list box.

To have a form as the figure above, you have to create the form in Form Design. See the figure below:

VBA example - Microsoft Access VBA CateInfo3 Form Design 

Note: Unselect Use Control Wizards before you drag these controls to the form.

- Drag and drop two text boxes, one list box and eight command buttons.

For the four command buttons MoveFirst, MoveLast, MoveNext and MovePrevious, we do not demonstrate in setting the properties and coding again. To learn how to move records in recorset, visit: moving records in Recordset object.

- Set Name properties of two text boxes and one list box:

Name: txtCategoryID

Name: txtCategoryName

Name: LstResult

- Set Name and Caption properties of four command buttons:

Name: CmdSave

Name: CmdUpdate

Name: CmdDelete

Name: CmdClear

 

Then apply the VBA Code below:

 

Option Compare Database

Option Explicit

Dim Db As DAO.Database

Dim Rs As DAO.Recordset

................................................................................................. 

Private Sub Form_Load()

    Set Db = CurrentDb

    Set Rs = Db.OpenRecordset("TblCategory", dbOpenDynaset)

    PopListBox

    DisplayData

    SelectList (Rs.AbsolutePosition)

    cmdSave.Enabled = False

    DisableCmd

End Sub

................................................................................................. 

Private Sub CmdSave_Click()

    If txtCategoryID <> "" And txtCategoryName <> "" Then

        Rs.AddNew

        Rs("CategoryID").Value = txtCategoryID.Value

        Rs("CategoryName").Value = txtCategoryName.Value

        Rs.Update

        PopListBox

    Else

        MsgBox "CategoryID cannot be blank.", vbInformation

    End If

End Sub

 .................................................................................................

Private Sub CmdUpdate_Click()

    If txtCategoryID <> "" And txtCategoryName <> "" Then

        Rs.Edit

        Rs(0).Value = txtCategoryID.Value

        Rs(1).Value = txtCategoryName.Value

        Rs.Update

    End If

    PopListBox

End Sub

 .................................................................................................

Private Sub CmdDelete_Click()

    If Not Rs.BOF Then

        Rs.Delete

        txtCategoryID = ""

        txtCategoryName = ""

        Rs.MoveNext

    End If

    PopListBox

End Sub

 .................................................................................................

Private Sub CmdClear_Click()

    txtCategoryID = ""

    txtCategoryName = ""

    cmdSave.Enabled = True

    txtCategoryID.Enabled = True

    txtCategoryID.SetFocus

    DisableCmd

End Sub

 .................................................................................................

Sub PopListBox()

'Populate a list box

    LstResult.ColumnHeads = True

    LstResult.ColumnCount = 2

    LstResult.RowSourceType = "Table/Query"

    LstResult.RowSource = "Select * from TblCategory"

    LstResult.Requery

End Sub

 .................................................................................................

Sub DisplayData()

'Display a record to text boxes

    txtCategoryID = Rs!CategoryID

    txtCategoryName = Rs!CategoryName

End Sub

 .................................................................................................

Sub SelectList(i As Integer)

'Scrollable list

    LstResult.SetFocus

    LstResult.ListIndex = 0

    LstResult.ListIndex = i

End Sub

................................................................................................. 

Private Sub LstResult_DblClick(Cancel As Integer)

    Dim i As Integer

    txtCategoryID = LstResult.Column(0, LstResult.ListIndex + 1)

    txtCategoryName = LstResult.Column(1, LstResult.ListIndex + 1)

    Rs.MoveFirst

    For i = 1 To LstResult.ListIndex

         Rs.MoveNext

    Next

    cmdUpdate.Enabled = True

    cmdDelete.Enabled = True

    cmdSave.Enabled = False

    txtCategoryID.Enabled = False

End Sub

 .................................................................................................

Sub DisableCmd()

    cmdUpdate.Enabled = False

    cmdDelete.Enabled = False

End Sub

 .................................................................................................

Private Sub Form_Close()

    Rs.Close

    Db.Close

    Set Rs = Nothing

    Set Db = Nothing

End Sub



Comments




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.