Microsoft Access 2010 tutorial:delete record


Delete record

VI.4. Deleting a record in a table by using recordset method

To delete an existing record in a table, you can use the syntaxes below:

Syntax1:

       RecordsetObject.Delete

For example, you have the frmModifyData form as figure below:

Access 2010 display all records in a table

If you want to delete any existing record(e.g. you would like to remove the last record(Cate007) from the table), you have to double click it and then click the Delete command button. The record will be removed from the table and the List Box immediately. See the figure:

Access 2010 delete record 

Through appending and updating the record, you perhaps have more concept about the writing of VBA code with Recordset object. You maybe already know to how to write the VBA code to settle the example. However, you can check the VBA code below:    

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 cmdDelete_Click()

  If Not Rs.BOF Then

      Rs.Delete
      txtCategoryID = ""
      txtCategoryName = ""
      Rs.MoveNext

  End If
  Call
PopListbox

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.