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:

Syntax1:

                   RecordsetObject.Edit

                   RecordsetObject(Index) = NewValue

                   RecordsetObject.Update

Syntax2:

                   RecordsetObject.Edit

                   RecordsetObject(FieldName) = NewValue

                   RecordsetObject.Update

Syntax3:

                   RecordsetObject.Edit

                   RecordsetObject! FieldName = NewValue

                   RecordsetObject.Update 

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.Edit
     Rs(0).Value = txtCategoryID.Value
     Rs(1).Value = txtCategoryName.Value
     Rs.Update
     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
   rs.MoveFirst

   For i = 0 To LstResult.ListIndex

         rs.MoveNext

   Next

   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.SetFocus

  LstResult.ListIndex = 0

  LstResult.ListIndex = i

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.