VBA example - Microsoft Access: Loop read data to listbox


VBA Loop read data to listbox

In this example, you will learn VBA For Each Loop Statement to retrieve all tables in the current database into the combo box and display its data in the list box. The example illustrates a DisplayData form that has a combo box and a list box. The combo box contains the tables in  the current database except the the system tables. The list box populates the data of selected table in the combo box. See the figure below:

  VBA example - Microsoft Access VBA Display Data Form  

-When the form loads, the first table displays in the combo box and  its data are populated in the list box automatically(See DisplayData form above).

-If you want to select any table, click the arrow down of combo 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 Display Data Form Design 

Note: Unselect Use Control Wizards before you drag these controls to the form. For the combo box and the list box controls, you can set or not set its name property. However, set the name property of control is better. It provides you an easy way to remember the control's name when you  call its name to use in coding.

- Drag and drop one combo box and one list box to the form.

- Set Name properties of the two controls:

Name: CboData

Name: lstData

Then apply the VBA Code below:

 

Option Compare Database

Option Explicit

Dim td As TableDef

Dim qd As QueryDef

 

Private Sub Form_Load()

    CboData.RowSource = ""

    LstData.RowSource = ""

    CboData.RowSourceType = "Value List"

    For Each td In CurrentDb.TableDefs

        If Left(td.Name, 4) <> "Msys" Then

            CboData.AddItem td.Name

        End If

    Next

    ' Select the first table in combo box

    CboData.Value = CboData.ItemData(0)

    DisplayData

End Sub

 

Private Sub CboData_Change()

    DisplayData

End Sub

 

' Populate data in list box

Sub DisplayData()

    LstData.RowSourceType = "Table/Query"

    LstData.ColumnHeads = True

    For Each td In CurrentDb.TableDefs

        If UCase(td.Name) = UCase(CboData.Value) Then

            LstData.ColumnCount = td.Fields.Count

            LstData.RowSource = "SELECT * FROM " & CboData.Value

        End If

    Next

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.