Microsoft Access 2010 tutorial:append record

Append record

VI.2. Appending a record to a table by using recordset method

To append a record to a table, you can use one among the syntaxes below:



                   RecordsetObject(Index) = Value




                   RecordsetObject(FieldName) = Value




                   RecordsetObject! FieldName = Value


For example, you have a form as figure below:

Access 2010 append records

After you filled category id and category name in the two text boxes(CategoryID, CategoryName) and click the Save command button, the data will append to the table. Previously there are only six records. Now you can see seven records in the list. See the demonstrable figure below:

Access 2010 result of appending records to a table 

Do you know the process of the program clearly, right?  It's time to think about the code now. Look at the demonstrative 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 list box 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 cmdSave_Click()

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

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

   Else: MsgBox "Category ID can not be blank!"

   End If

End Sub


Sub PopListbox()

' Populate list box
  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.