Microsoft Access 2010 tutorial:update record

Update record

VI.3. Editing a record in a table by using recordset method

To edit an existing record in a table, you can use one among the syntaxes below:



                   RecordsetObject(Index) = NewValue




                   RecordsetObject(FieldName) = NewValue




                   RecordsetObject! FieldName = NewValue


For example, you have a form as figure below:

Access 2010 display all records in a table

If you want to edit any existing record, you have to edit it in the text boxes. To pull the record into the text boxes, you have to double click it in the List Box. After the text boxes received the data, you can make the editing(e.g. edit category name from Meat to Fish of Cate004). Finally, click the Update command button to update data. See the figure below:

Access 2010 upate record 

Do you have the concept in writing the code yet? You maybe have some idea in coding because you have seen the VBA code in appending the record into the table already. See the VBA code below that explicate the example above. Compare these VBA code to your VBA code that you have thought in you mind.

Option Compare Database
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim SqlText As String

Private Sub Form_Load()

' Display the first record on form and drop all records to listbox with selection
  Set Db = CurrentDb

  SqlText = " Select * From TblCategory"

  Set Rs = Db.OpenRecordset(SqlText, dbOpenDynaset)

  Call display   ' Call display sub procedure

  selectlist (Rs.AbsolutePosition)   ' Call selectlist sub procedure

  Call PopListbox    ' Call PopListbox sub procedure

  cmdUpdate.Enabled = False

  cmdDelete.Enabled = False

End Sub


Private Sub cmdUpdate_Click()

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

     Rs(0).Value = txtCategoryID.Value
     Rs(1).Value = txtCategoryName.Value
     Call PopListbox

  End If

End Sub


Private Sub LstResult_DblClick(Cancel As Integer)

   Dim i As Integer

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

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

'Update Current Position

   For i = 0 To LstResult.ListIndex



   txtCategoryID.Enabled = False

   cmdUpdate.Enabled = True

   CmdDelete.Enabled = True

   cmdSave.Enabled = False

End Sub


Sub PopListbox()

' Populate listbox
LstResult.ColumnCount = 2

  LstResult.ColumnHeads = True

  LstResult.RowSourceType = "Table/Query"

  LstResult.RowSource = SqlText

End Sub


Sub display()

' Display data on form
  txtCategoryID.Value = Rs(0)

  txtCategoryName.Value = Rs(1)

  selectlist (Rs.AbsolutePosition)

End Sub


Sub selectlist(i As Integer)

' Scrollable list

  LstResult.ListIndex = 0

  LstResult.ListIndex = i

End Sub



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.