MS Access 2007 VBA Recordset move records in listbox


Move records in listbox

In this example, you will learn Recordset Methods MoveFirst, MoveLast, MovePrevious,  and MoveNext to move records in the recordset object. All records will be populated in in the list box. Each moved record will appear in the text boxes and be selected in the list box. The example illustrates a CategoryInfo2 form that has two text boxes(CategoryID, CategoryName), one list box(LstResult), and four command buttons(MoveFirst, MoveLast, MovePrevious, MoveNext). See the figure below:

 VBA example - Microsoft Access VBA CategoryInfo2 Form  

-When the form loads, the first record displays in the two text boxes and  that record is selected in the list box automatically(See CategoryInfo2 form above).

-If you click MoveFirst(|<)command button, the first record will display in the text boxes and be selected in the list box.

-If you click MoveLast(>|) command button, the Last record will display in the text boxes and be selected in the list box.

-By clicking the MovePrevious(<) and MoveNext (>)command buttons, the record will display in the text boxes and be selected in the list box.

To have a form as the figure above, you have to create the form in Form Design. See the figure below:

VBA example - Microsoft Access VBA CateInfo2 Form Design 

Note: Unselect Use Control Wizards before you drag these controls to the form.

- Drag and drop two text boxes, one list box and four command buttons.

- Set Name properties of two text boxes and one list box:

Name: txtCategoryID

Name: txtCategoryName

Name: LstResult

- Set Name and Caption properties of four command buttons:

Name: CmdFirst

Name: CmdPrevious

Name: CmdNext

Name: CmdLast

 

Then apply the VBA Code below:

 

Option Compare Database

Dim Rs As DAO.Recordset

......................................................................

Private Sub Form_Load()

  ' Use current database to open recordset object in dbOpenDynaset type

   Set Rs = CurrentDb.OpenRecordset("TblCategory", dbOpenDynaset)

   PopListBox

   DisplayData

   SelectList (Rs.AbsolutePosition)

End Sub

......................................................................

Private Sub CmdFirst_Click()

  ' Move to first record

   Rs.MoveFirst

   DisplayData

   SelectList (Rs.AbsolutePosition)

End Sub

......................................................................

Private Sub CmdLast_Click()

  ' Move to last record

  Rs.MoveLast

  DisplayData

  SelectList (Rs.AbsolutePosition)

End Sub

......................................................................

Private Sub CmdNext_Click()

'Move to next record

 On Error GoTo aa

 If Not Rs.EOF Then

     Rs.MoveNext

     DisplayData

     SelectList (Rs.AbsolutePosition)

     aa: Resume Next

  End If

End Sub

......................................................................

Private Sub CmdPrevious_Click()

 'Move to previous record

 On Error GoTo aa

 If Not Rs.BOF Then

     Rs.MovePrevious

     DisplayData

     SelectList (Rs.AbsolutePosition)

     aa: Resume Next

  End If

End Sub

......................................................................

Sub PopListBox()

 'Populate a list box

  LstResult.ColumnHeads = True

  LstResult.ColumnCount = 2

  LstResult.RowSourceType = "Table/Query"

  LstResult.RowSource = "Select * from TblCategory"

End Sub

......................................................................

Sub DisplayData()

' Display a record to text boxes

  txtCategoryID = Rs!CategoryID

  txtCategoryName = Rs!CategoryName

End Sub

 ......................................................................

Sub SelectList(i As Integer)

 'Scrollable list

  LstResult.SetFocus

  LstResult.ListIndex = 0

  LstResult.ListIndex = i

End Sub



Comments

Avi comment

 Avi

ASDFG


2017-02-13



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.