Can I change Controltype with VBA?

[SiberBob]

I'd like to change a control from a text box to a combo box when certain criteria are met. Is there a way to do this in vba?

If chkPref = True then 
    ctlWrecker.ControlType = acComboBox
    ctlWrecker.RowSourceType = "Table/Query"
    ctlWrecker.RowSource = "blah blah blah"
ElseIf chkPref = False then 
    ctlWrecker.ControlType = acTextBox
    ctlWrecker = strsql
End If
[code]


Bob

 

[FancyPrairie]

I don't believe so, and if you were able to, the form would have to be changed to design view. You could put a text box on top of a combobox. When you want to "change" the text box to a combobox, just set the visible property of the text box to false (hidden) and change the visible property of the combobox to true (visible).

 

[Zion7]

This is out of Help...

...The ControlType property is useful not only for checking for a specific control type in code, but also for changing the type of control to another type. For example, you can change a text box to a combo box by setting the ControlType property for the text box to acComboBox while in form Design view.

So, as FancyPrairie said, you would have to re-open the form in design view. Something like,

...
ElseIf chkPref = False then
DoCmd.OpenForm "frmName,,acDesign
ctlWrecker.ControlType = acTextBox
ctlWrecker = strsql
DoCmd.OpenForm "frmName,,acNormal

End If...

I imagine this might be a little distracting for the user?

Maybe there's a smoother, less noticeable method(workaround)?

Is it the aesthetics, or the functionality of a textbox that you want?
There was a previous thread, on how to make a combo look like a text. One idea I liked, was to cover the arrow, with a small textbox? But, I'm thinking it's the functionality you want, reduce the choices that a combo offers.

maybe using OnCurrent, disable, lock change rowsource?

Either way, good luck!