Microsoft Access 2007 tutorial: append records

Append records

IV.2. Appending Data to the Table by using Recordset Method

To append data to the 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:

Microsoft Access 2007 append data to the table by using Recordset object

After you filled the data 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:

Microsoft Access 2007 append data to the table by using Recordset object result 

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