none
Access 2016 (365) Change YesNo to use ComboBox RRS feed

  • Question

  • Long-standing database moved to SQL back end.

    Created new tables pointing a SQL instance, DisplayControl property of tables has been lost.

    I have hundreds of fields to update so I'm trying to use VBA.

    I can set the property to acComboBox in code and reading it back it has the correct value.

    Opening the table, though, the property is still acTextBox, both in design and datasheet view.

    Is there a trick that I'm missing?

    Thanks,
    e.

    With CurrentDB().TableDefs("TABLE").Fields("FIELD")
      Set prp = .CreateProperty(strProperty, "DisplayControl", acComboBox)
      .Properties.Append prp
      Set prp = Nothing
      .Properties("DisplayControl").Value = acComboBox
    End With
    
    ' You can do this in the immediate window and the value is 111 (acComboBox)
    Debug.Print CurrentDB().TableDefs("TABLE").Fields("FIELD").Properties("DisplayControl").Value

    Thursday, February 8, 2018 10:57 AM

Answers

  • Not sure if this is a bug. The code does function properly as it adds the property to the field and the display control is a ComboBox box on a Form. For some reason it is not reflected in the designer and I would suspect there is probably some metadata that is added when modified through the designer.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by exotericist Thursday, February 8, 2018 3:23 PM
    Thursday, February 8, 2018 3:15 PM

All replies

  • Not sure if this is a bug. The code does function properly as it adds the property to the field and the display control is a ComboBox box on a Form. For some reason it is not reflected in the designer and I would suspect there is probably some metadata that is added when modified through the designer.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by exotericist Thursday, February 8, 2018 3:23 PM
    Thursday, February 8, 2018 3:15 PM
  • Thanks for the reply.

    Looks like I'm going to have to go into each field and update it manually.

    *Sigh*

    Can you point me to the feedback site for Access developers?

    Is there such a thing?

    Thanks again,
    e.

    Thursday, February 8, 2018 3:23 PM
  • Thanks for the reply.

    Looks like I'm going to have to go into each field and update it manually.

    *Sigh*

    Can you point me to the feedback site for Access developers?

    Is there such a thing?

    Thanks again,
    e.

    Try the below link:

    https://access.uservoice.com/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, February 8, 2018 3:32 PM
  • Merely setting the DisplayControl property of a Boolean column to a combo box won't in itself achieve anything.  You would still need set the properties of the combo box so that it allows you to select a value of Yes or No, True or False, or whatever you wish to use.

    Why do you want to do this?  Values should only be entered by users via a form, never in a table's raw datasheet view, so how the values are presented in the latter is immaterial.  In a form you'd simply bind a combo box to the column in the usual way.

    More fundamentally, the fact that you have 'hundreds' of Boolean columns sets my relational antennae twitching, and makes me wonder whether you are 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.


    Ken Sheridan, Stafford, England

    Thursday, February 8, 2018 4:07 PM
  • I wondered when I'd be admonished for my choices.

    Thanks for this.

    This is a data collection app, and the spec is for fields marking "has this thing happened?" and "was this benchmark achieved?"

    (Most of my development time is spent trying to convince them that "N/A" is not a valid or useful choice.)

    The head man hates checkboxes, so I'm forced to use listboxes.

    My code does set the other properties, and correctly.

    (It does it from data, too, which makes it easier still for me.)

    Speaking of making life easier, in Access setting properties on the table copies those properties consistently onto forms and reports. From MSDN:

    If a field had its DisplayControl property set in table
    Design view and you drag it from the field list in form
    Design view, Microsoft Access copies the appropriate
    properties to the control's property sheet.

    And although I never let the users see a datasheet, sometimes when trying to find out what's gone wrong with the data I have to look at one. Seeing values instead of arbitrary numbers is just better.

    I'm intrigued by your "encoding data as column headings" code smell but the internet just gives me stuff about UNICODE. Any pointers to this?

    Again, thanks for your time,
    e.


    Friday, February 9, 2018 10:21 AM