VBA example - Microsoft Access: Loop show tables in combobox


VBA Loop show tables to combobox

In this example, you will learn VBA For Each Loop Statement to pull all tables in the current database into the combo box. The example illustrates a Wizard form that has a combo box contains the tables in  the current database except the the system tables.  See the figure below:

 VBA example - Microsoft Access VBA Form-Wizard Form  

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

-If you want to select any table, click the arrow down of combo box.

-If you click Add command button, one selected field will remove from the left list box(List0)  to the right list box(List1) with selection. See the figure:

VBA example - Microsoft Access VBA Form-Wizard Remove Field Form  

-If you continue to click this command button, the action of removing the field will perform till the left list box(List0) contains no field and the two command buttons Add and AddAll will disable. See the figure:

VBA example - Microsoft Access VBA Form-Wizard Remove Fields Form  

-If you click AddAll command button, all fields of a selected table will be removed from the left list box in to the right list box and the last field is selected.

-For the Back and BackAll command button, their works are opposite from the works of Add and AddAll command buttons.

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

VBA example - Microsoft Access VBA Form-Wizard Form Design 

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

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

- Set Name and Caption properties of four command buttons:

Name: CmdAddOne

Caption: Add

Name: CmdAddAll

Caption: AddAll

Name: CmdBackOne

Caption: Back

Name: CmdBackAll

Caption: BackAll

- Set Name properties of a combo box:

Name: CboData

Then apply the VBA Code below:

 

Option Compare Database

Option Explicit

Dim dt As TableDef

Dim df As Field

Dim i As Integer

 

Private Sub Form_Load()

    cboData.RowSource = ""

    List0.RowSource = ""

    CmdBackOne.Enabled = False

    CmdBackAll.Enabled = False

    i = 0

    Me.cboData.RowSourceType = "Value List"

    For Each dt In CurrentDb.TableDefs

       ' Display all tables execpt system tables

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

            Me.cboData.AddItem dt.Name

        End If

    Next   

    Me.cboData.Value = cboData.ItemData(0)

    showfield

    SelectedItem

End Sub

 

Private Sub cboData_Change()

    List0.RowSourceType = "Value List"

    List0.RowSource = ""

    showfield

    SelectedItem

End Sub

 

Private Sub CmdAddOne_Click()

    Dim lstdata0 As String

    List1.RowSourceType = "Value List"

    lstdata0 = List0.ItemData(List0.ListIndex)

    Me.List1.AddItem lstdata0

    SelectedItem1

    Me.List0.RemoveItem (List0.ListIndex)

    If Me.List0.ListCount = 0 Then

        List1.SetFocus

        Me.cmdaddone.Enabled = False

        Me.cmdaddall.Enabled = False

        Me.CmdBackOne.Enabled = True

        Me.CmdBackAll.Enabled = True

    End If

    SelectedItem

End Sub

 

Private Sub cmdAddAll_Click()

    Dim lst0alldata As String

    List1.RowSourceType = "Value List"

    For i = 0 To List0.ListCount - 1

        lst0alldata = List0.ItemData(i)

        List1.AddItem lst0alldata

        SelectedItem1

    Next

    List0.RowSource = ""

    If List0.ListCount = 0 Then

        List1.SetFocus

        cmdaddone.Enabled = False

        cmdaddall.Enabled = False

        CmdBackOne.Enabled = True

        CmdBackAll.Enabled = True

    End If

End Sub

 

Private Sub CmdBackOne_Click()

    List0.RowSourceType = "Value List"

    Dim lstdata1 As String

    lstdata1 = List1.ItemData(List1.ListIndex)

    List0.AddItem lstdata1, 0

    SelectedItem

    List1.RemoveItem (List1.ListIndex)

    SelectedItem1

    If List1.ListCount = 0 Then

        List0.SetFocus

        cmdaddone.Enabled = True

        cmdaddall.Enabled = True

        CmdBackOne.Enabled = False

        CmdBackAll.Enabled = False

    End If

End Sub

 

Private Sub CmdBackAll_Click() 

    Dim lst1alldata As String

    List0.RowSourceType = "Value List"

    For i = 0 To List1.ListCount - 1

        lst1alldata = List1.ItemData(i)

        List0.AddItem lst1alldata

        SelectedItem

    Next

    List1.RowSource = ""

    If List1.ListCount = 0 Then

        List0.SetFocus

        cmdaddone.Enabled = True

        cmdaddall.Enabled = True

        CmdBackOne.Enabled = False

        CmdBackAll.Enabled = False

    End If

End Sub

 

' Show the fields of selected table

Sub showfield()

    For Each dt In CurrentDb.TableDefs

        If UCase(dt.Name) = UCase(cboData.Value) Then

            For Each df In dt.Fields

                List0.AddItem df.Name

            Next

        End If

    Next

End Sub

 

' Select index zero field

Sub SelectedItem()

    List0.Selected(0) = True

End Sub

 

' Select last index field

Sub SelectedItem1()

    List1.Selected(List1.ListCount - 1) = True

End Sub



Comments

Clive comment

 Clive

Hello - would love to try and get this working, but experiencing a few issues when attempting... I've managed to solve the first 3 issues [please see below] but hit a problem with the next...

1. You have a naming issue with your Combo Box, asking for it to be named CboData but then referencing cboData in the code.
2. You don't explicitly ask for the two List Boxes to be named; your code assumes they will be named List0 and List1 respectively, mine were named List7 and List9 [no idea where].
3. Your method showfield generates an error on the lines "List0.AddItem df.Name" because it wants the List0.RowSourceType to be = "Value List". I was able to copy this from further up the script...

Putting in these three quick "hot fixes" gets a mostly-working solution. However, the last bug is that 2 of the buttons - CmdBackOne and CmdBackAll - remain greyed out [false] until *all* the returned rows have been moved across to the List1 box; then those 2 buttons become active.

However, the logic in i.e. CmdBackOne_Click kind of suggests that as long as the item count in i.e. List1 is greater than 0, then we would expect the "Back" buttons to be active. They only seem to activate when List0 is empty, not when List1 is non-empty.

I will continue to experiment and see if I figure out this last part. However, thanks much for posting this - I think you've provided real insight for a solid solution.


2017-04-24



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.