Listing Code to Fill a Combo Box Value List Property

'Create a string variable named NewValList Dim NewValList As String

'Build NewValList string in chunks.

NewValList = Chr(34) + "First Item" + Chr(34) + ";"

NewValList = NewValList + Chr(34) + "Second Item" + Chr(34) + ";"

NewValList = NewValList + Chr(34) + "Third Item" + Chr(34) + ";"

NewValList = NewValList + Chr(34) + "Fourth Item" + Chr(34)

'At this point, NewValList contains...

'"First Item";"Second Item";"Third Item";"Fourth Item"

'Make new string the Row Source for value list named OpsCombo Me!OpsCombo.RowSourceType = "Value List" Me!OpsCombo.RowSource = NewValList 'Set selection to first item in drop-down menu. Me.OpsCombo.Value = Me.OpsCombo.ItemData(O)

When you create a list box or combo box with its Row Source Type set to Value List, you can leave the Row Source property empty. When the form first opens, the list will also be empty, which means that the user can't select anything. However, you can write some code that fills the list and then attach it to the form's On Load event. This allows you to create dynamic, flexible lists that adapt themselves to the current database. I look at some examples in the sections that follow.

Make a list of table and query names

Sometimes you might want a combo box or list box to display a list of all the tables, or all the queries, or both. There isn't a simple property setting that will let you do that. You need to programmatically fill the list with names as soon as the form opens. Any time that you want code to execute as soon as a form opens, attach that code to the form's On Load event.

For example, Figure 10-5 shows an empty control named TblQryCombo. Its Row Source Type is set to Value List, but its Row Source property is empty. So without any code, when the form opens, TblQryCombo displays nothing.

Suppose now that when the form opens, you want it to display a list of all table names in the current database. You can write some code that loops through the AllTables collection and adds the name of each table to a string. Then use that string as the Row Source for the control.

Figure 10-5:

Sample empty combo box named Tbl QryCombo.

Empty TblQryCombo _I_

control

Empty TblQryCombo _I_

control

Figure 10-5:

Sample empty combo box named Tbl QryCombo.

Note this catch, though. The AllTables collection includes hidden system tables that Access uses behind the scenes. Because the names of those system tables normally don't appear in the database window, you want to exclude them from the drop-down list as well.

Luckily, all the system tables have names that start with the letters MSys. To eliminate those table names from the drop-down list, you can use an If... Else...End If block to skip over any name that starts with MSys. The complete code to fill TblQryCombo with a list of table names as soon as the form opens is shown in Listing 10-2. Each comment refers to the line (or lines) that follow the comment.

0 0

Responses

  • amelie
    How to code valuelist vba?
    7 years ago

Post a comment