Creating Multi-Value Lookup Fields

There are new data types in DAO that are used to define a multi-value lookup field. The names of these types begin with dbComplex and contain the name of a type that can be used for the lookup field. In other words, if the related field for the lookup is an Integer, you can use dbComplexInteger for a multi-value lookup field. The valid field types are:

• dbComplexByte
• dbComplexDecimal
• dbComplexDouble
• dbComplexGUID
• dbComplexInteger
• dbComplexLong
• dbComplexSingle
• dbComplexText

Let's say that you have a database that tracks students and classes, with respective tables tblStudents and tblClasses. The tblClasses table defines a ClassID field, which is an AutoNumber field and the primary key. The Students table includes a field that is defined as dbComplexLong that is the multivalue lookup field for the tblClasses table. To create a multi-valued field, you must use the new Field2 object defined in DAO.

This code also demonstrates an alternate technique you can use when executing the CreateField method:

Because CreateField returns a DAO.Field2 object, it is passed as the argument to the Append method of the Fields property on the TableDef object. Using this approach reduces the amount of code you have to write and maintain.

Here's the code:

' Creates the Classes table
Sub CreateClassesTable()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field2

    'Get the database
    Set dbs = CurrentDb

    'Create the classes table
    Set tdf = dbs.CreateTableDef("tblClasses")

    'Create the ClassID field
    Set fld = tdf.CreateField("ClassID", dbLong)
    fld.Attributes = dbAutoIncrField
    tdf.Fields.Append fld

    'Create the Primary Key index using ClassID
    Set idx = tdf.CreateIndex("PrimaryKey")
    idx.Primary = True
    idx.Fields.Append tdf.CreateField("ClassID")
    idx.Fields.Refresh

    'Append the index and refresh
    tdf.Indexes.Append idx
    tdf.Indexes.Refresh

    'Create and append the ClassCode field using the abbreviated syntax
    tdf.Fields.Append tdf.CreateField("ClassCode", dbText, 25)

    'Create and append the ClassDescription field
    tdf.Fields.Append tdf.CreateField("ClassDescription", dbMemo)
    tdf.Fields.Refresh

    'Append the table to the database
    dbs.TableDefs.Append tdf

    'Cleanup
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Sub

'Creates the students table
Sub CreateStudentsTable()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim idx As DAO.Index
    Dim fld As DAO.Field2

    'Get the database
    Set dbs = CurrentDb

    'Create the Students table
    Set tdf = dbs.CreateTableDef("tblStudents")

    'Create the StudentID field
    Set fld = tdf.CreateField("StudentID", dbLong)
    fld.Attributes = dbAutoIncrField
    tdf.Fields.Append fld

    'Create the Primary Key (Student - AutoNumber)
    Set idx = tdf.CreateIndex("PrimaryKey")
    idx.Primary = True
    idx.Fields.Append tdf.CreateField("StudentID")
    idx.Fields.Refresh

    'Append the index and refresh
    tdf.Indexes.Append idx
    tdf.Indexes.Refresh

    'Create and append the following fields:
    'FirstName, LastName, Address, City, StateOrProvince, Region, PostalCode,
Country
    tdf.Fields.Append tdf.CreateField("FirstName", dbText, 50)
    tdf.Fields.Append tdf.CreateField("LastName", dbText, 50)
    tdf.Fields.Append tdf.CreateField("Address", dbText, 50)
    tdf.Fields.Append tdf.CreateField("City", dbText, 50)
    tdf.Fields.Append tdf.CreateField("StateOrProvince", dbText, 50)
    tdf.Fields.Append tdf.CreateField("Region", dbText, 50)
    tdf.Fields.Append tdf.CreateField("PostalCode", dbText, 50)
    tdf.Fields.Append tdf.CreateField("Country", dbText, 50)

    'Ok, now for the multi-value lookup field.
    'For this, define the field as dbComplexLong since it will
    'perform a lookup to a Long Integer field (ClassID) in the Classes table
    Set fld = tdf.CreateField("Classes", dbComplexLong)

    'Append the field
    tdf.Fields.Append fld
    'Append the table to the database
    dbs.TableDefs.Append tdf

    'Set Access properties to use the combo box control
    '- DisplayControl: ComboBox
    '- ColumnCount: 2
    '- ColumnWidths: "0"
    '- RowSource: tblClasses - This is the lookup table
    With fld

	.Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
	.Properties.Append .CreateProperty("RowSource", dbText, "tblClasses")
	.Properties.Append .CreateProperty("ColumnCount", dbInteger, 2)
	.Properties.Append .CreateProperty("ColumnWidths", dbText, "0")
    End With

    'Cleanup
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Sub