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:

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:

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