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:

Syntax1:

                   RecordsetObject.AddNew

                   RecordsetObject(Index) = Value

                   RecordsetObject.Update

Syntax2:

                   RecordsetObject.AddNew

                   RecordsetObject(FieldName) = Value

                   RecordsetObject.Update

Syntax3:

                   RecordsetObject.AddNew

                   RecordsetObject! FieldName = Value

                   RecordsetObject.Update 

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.AddNew
   Rs(0).Value = txtCategoryID.Value
   Rs(1).Value = txtCategoryName.Value
   Rs.Update
   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.SetFocus

LstResult.ListIndex = 0

LstResult.ListIndex = i

End Sub




Comments

CAPTCHA image



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.