help with Controls (listbox, txtbox)

[jasonhmann]

Hey Everyone,

Questions:

• Is there a way to convert a textbox to a list box in code?
  
  Is there a way to directly select a control from a string containing its name so that I may changes its properties, e.g. something like
  Str = “DeviceNum”
  Me.select(Str)

The problem:
I would like to display to the user a listbox if I want the user to make a selection, otherwise I want to display only one value. The form I have created allows the user to edit tables, the form changes depending on what the user selects in the options menu. E.G. If the user selects update device number, I would like to change the textbox containing the device # to a list box containing all possible device #s.

My current code:

Private Sub dropdown(CaptionName As String, sSQL As String)
    Dim rs As ADODB.Recordset
    Dim sRow As String
    Dim myCtl As Control
    Dim tempLB As New ListBox
    
    Set rs = New ADODB.Recordset
    
    'Loop through each Control in the Current Form
    For Each myCtl In Me.Controls
     Select Case myCtl.ControlType
     Case acTextBox, acComboBox, acListBox     'Control is a Text Box
       If (myCtl.Value = "Hayes") Then      'Control is a Text Box
         'Procesing code here
         sRow = myCtl.Value
         Set tempLB = myCtl.Object	‘ VBA doesn’t like my conversion here
         With rs
            .Open sSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
            tempLB.RowSourceType = "Value List"
          Do Until .EOF
            If sRow = vbNullString Then
                sRow = rs.Fields(1)
            Else
                sRow = sRow + ", " + rs.Fields(1)
            End If
            .MoveNext
           Loop
         .Close
         tempLB.RowSource = sRow
         End With
         Exit Sub  ' completed the search now exit sub
        End If
    Case Else
    
    End Select
    
    Next
    
End Sub

Cheers,
Jason

 

[Andrew Fergus]

Hi Jason

I'm not sure about converting a text box into a list box using VBA (although I'm sure someone somewhere will have done it). Instead, how about having the list box (or combo box) on the form but set its enabled property to true or false depending on the users input? That might be a lot simpler. And to change the properties of an object on the form, the syntax is like this :

Me.ControlName.Property = ..{something}..

e.g. Me.ListView1.Enabled = True

Sometimes you need to include either a Me.Refresh or Me.Requery line in your code so the users view of the form is updated.

HTH, Andrew

 

[VicRauch]

Something that would be much easier than changing a list box to a text box is to define both, put them in the same space, but make the text box hidden (.Visible = False). Then when you would "change" the list box into a text box, make the list box hidden (.Visible = False) and make the text box visible (.Visible = True). That will "change" a list box into a text box as far as the user is concerned. Both controls will still be on the form, it is just that you (as the programmer) have control as to which control is visible when.
HTH,