Shiraz S Kaderuppan Shiraz S Kaderuppan - 2 months ago 15
SQL Question

How to create a Lookup field in Microsoft Access 2013 using SQL?

May I enquire as to how does one create a look-up field in a table in Microsoft Access 2013 (using the CREATE TABLE statement in SQL) which provides a drop-down box in Datasheet View that allows the user to select one or more entries in the field?

An example is as shown below (where the user can choose the fruit/(s) he/she likes from the drop down box in the Fav_Fruit field):

enter image description here

The values for the drop-down control are obtained from another table called Fruits (stored within the same Access database) shown below:

enter image description here

Please advise.

Thanks.

Answer

Shiraz,

These are properties you will need to create in VBA code. Is this an option for you?

Function AddLookupField()


    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim tdf As DAO.TableDef

    Set db = CurrentDb
    Set tdf = db.TableDefs("MyTable")

    Set fld = tdf.CreateField("Fav_Fruit", dbText)
    tdf.Fields.Append fld

    Set prp = fld.CreateProperty("DisplayControl", dbInteger, AcControlType.acComboBox)
    fld.Properties.Append prp

    Set prp = fld.CreateProperty("RowSourceType", dbText, "Value List")
    fld.Properties.Append prp

    Set prp = fld.CreateProperty("RowSource", dbText, "Orange; Pear; Apple")
    fld.Properties.Append prp


End Function

This example uses a value list (string of values). If you want the values to come from another table/query, experiment with these properties:

Set prp = fld.CreateProperty("RowSourceType", dbText, "Table/Query")
fld.Properties.Append prpProperty
Set prp = fld.CreateProperty("RowSource", dbText, "SELECT ID FROM SomeOtherTable")
fld.Properties.Append prpProperty