0

Let's imagine I have the following VBA code in a Microsoft Access database.

Option Compare Database

Public Enum testdata
    foo = 0
    bar = 1
    baz = 2
End Enum

Just for context, I'm declaring this in a module like so: enter image description here

Now, what I want to happen is to have a category field in a table auto-fill with list items based on this enum, like so:

enter image description here

I had to manually type each list item out in order to show what I want, but surely there must be a way to "link" an enumerated list of constants in VBA code to a field in the table, so that when I add new entries to the enum, the list of categories updates accordingly. I tried looking it up on the internet but I couldn't figure out the best way to explain what I'm trying to do without using pictures.

puppydrum64
  • 1,598
  • 2
  • 15
  • This is normally done in a database table (MsAccess table). You can then create relationships between the columns you want to use this list for (Foreign Key). – tinazmu Jan 27 '22 at 12:48
  • @userMT My goal is to create a class that uses the enum for one of its members, and in the table pick one of the valid categories (foo, bar, baz) to assign to that class object. – puppydrum64 Jan 27 '22 at 12:54
  • I doubt there can be a direct link. Why Enum and not just a lookup table? – June7 Jan 27 '22 at 13:11
  • 1
    The problem you're facing in VBA is that there's no easy way to retrieve the _name_ of an enum member. The .NET framework caters for this lack with its [Enum.GetName](https://learn.microsoft.com/en-us/dotnet/api/system.enum.getname?view=net-6.0) method. There's a way in VBA (search for "+tlbinf32 +vba"), but it's more complicated than the already suggested lookup DB table. Especially as you're using a database anyway. – Hel O'Ween Jan 27 '22 at 13:41
  • 2
    The dropdown in the table will only bring you headaches. Create a lookup table and store only the id. – Kostas K. Jan 27 '22 at 16:21
  • While Kostas suggestion is the 'right way', if the list of options is small, and unlikely to change, I think it is OK to just use a combo value list. Still, I like your integrative thinking. Sadly, VBA doesn't bend that way. – kismert Jan 27 '22 at 17:56

1 Answers1

0

I have found no other way than creating a clumsy Select Case to return the literals, for example:

' Windows Phone colour enumeration.
Public Enum wpThemeColor
    ' Official colour names from WP8.
    Lime = &HC4A4&
    Green = &H17A960
    Emerald = &H8A00&
    Teal = &HA9AB00
    Cyan = &HE2A11B
    Cobalt = &HEF5000
    Indigo = &HFF006A
    Violet = &HFF00AA
    Pink = &HD072F4
    Magenta = &H7300D8
    Crimson = &H2500A2
    Red = &H14E5&
    Orange = &H68FA&
    Amber = &HAA3F0
    Yellow = &HC8E3&
    Brown = &H2C5A82
    Olive = &H64876D
    Steel = &H87766D
    Mauve = &H8A6076
    Sienna = &H2D52A0
    ' Colour name aliases from WP7.5
    Viridian = &HA9AB00
    Blue = &HE2A11B
    Purple = &HFF00AA
    Mango = &H68FA&
    ' Used for black in popups.
    Darken = &H1D1D1D
    ' Additional must-have names for grey scale.
    Black = &H0&
    DarkGrey = &H3F3F3F
    Grey = &H7F7F7F
    LightGrey = &HBFBFBF
    White = &HFFFFFF
End Enum


' Returns the literal name of the passed colour value if
' it is one of the Windows Phone Theme Colors.
'
' 2017-04-21. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function LiteralWpThemeColor( _
    ByVal Color As wpThemeColor) _
    As String

    Dim Name    As String
    
    Select Case Color
        Case wpThemeColor.Lime
            Name = "Lime"
        Case wpThemeColor.Green
            Name = "Green"
        Case wpThemeColor.Emerald
            Name = "Emerald"
        Case wpThemeColor.Teal
            Name = "Teal"
        Case wpThemeColor.Cyan
            Name = "Cyan"
        Case wpThemeColor.Cobalt
            Name = "Cobalt"
        Case wpThemeColor.Indigo
            Name = "Indigo"
        Case wpThemeColor.Violet
            Name = "Violet"
        Case wpThemeColor.Pink
            Name = "Pink"
        Case wpThemeColor.Magenta
            Name = "Magenta"
        Case wpThemeColor.Crimson
            Name = "Crimson"
        Case wpThemeColor.Red
            Name = "Red"
        Case wpThemeColor.Orange
            Name = "Orange"
        Case wpThemeColor.Amber
            Name = "Amber"
        Case wpThemeColor.Yellow
            Name = "Yellow"
        Case wpThemeColor.Brown
            Name = "Brown"
        Case wpThemeColor.Olive
            Name = "Olive"
        Case wpThemeColor.Steel
            Name = "Steel"
        Case wpThemeColor.Mauve
            Name = "Mauve"
        Case wpThemeColor.Sienna
            Name = "Sienna"
        Case wpThemeColor.Viridian
            Name = "Viridian"
        Case wpThemeColor.Blue
            Name = "Blue"
        Case wpThemeColor.Purple
            Name = "Purple"
        Case wpThemeColor.Mango
            Name = "Mango"
        Case wpThemeColor.Darken
            Name = "Darken"
        Case wpThemeColor.Black
            Name = "Black"
        Case wpThemeColor.DarkGrey
            Name = "DarkGrey"
        Case wpThemeColor.Grey
            Name = "Grey"
        Case wpThemeColor.LightGrey
            Name = "LightGrey"
        Case wpThemeColor.White
            Name = "White"
    End Select
    
    LiteralWpThemeColor = Name
    
End Function

From here, you can create a loop to fill a value list to populate the combobox.

Side note: Be careful with MV (Multi-Value) fields, as these will sort the list the names alphabetically which may not be what you want.

Gustav
  • 53,498
  • 7
  • 29
  • 55