Access 2016 VBA TextBox is Null

Question

I don't use VBA from long time....I have this form in Access 2016

When I try to access to the various TextBoxes through the Me.Controls Collection and convert it to a TextBox object, I get a Null reference but some its properties are valid (eg. tb.Name)

Private Sub Form_Load()
  Dim ctrl As Control
  Dim tb As TextBox
  Dim evTb As clsEventTextBox

  Set m_TbColl = New Collection

  For Each ctrl In Me.Controls
    If Left$(ctrl.Name, 4) = "Txt_" Then
      Set tb = ctrl
      'Create the TextBox wrapper 
      Set evTb = New clsEventTextBox
      Set evTb.EventsHandler = Me

      Set evTb.InnerTextBox = tb  <----- HERE tb Is NULL

      m_TbColl.Add evTb, ctrl.Name
    End If
  Next
End Sub

I miss something?
Also, is there a way to get the Type of a Control instead of using

Left$(ctrl.Name, 4) = "Txt_"

score 3 · Accepted Answer · answered Apr 04 '17 at 21:24

3

To get the type, use TypeName like this:

If TypeName(ctrl) = "TextBox" Then

And to ensure tb takes the form of a Textbox object, use this

Set tb = Controls(ctrl.Name)

answered Apr 04 '17 at 21:24

CallumDA

12,025
6
30
52

score 3 · Answer 2 · answered Apr 04 '17 at 23:43

You haven't shown the class that you're using, but assuming it looks something like this:

Private WithEvents f_EH As Access.Form
Private WithEvents f_TB As Access.TextBox

Public Property Set EventsHandler(frm As Access.Form)
  Set f_EH = frm
End Property

Public Property Set InnerTextBox(ctl As Access.TextBox)
   Set f_TB = ctl
End Property

If I use a class with that structure, the code in your post works fine. But notice that I've explicitly set the expected type of the InnerTextBox property to Access.TextBox.

But your code does needless casting, uses Hungarian naming (yuck!), and relies on the first 4 characters of the name being "Txt_" and could be written as:

  Dim ctrl As Control
  Dim evTb As clsEventTextBox

  Set m_TbColl = New Collection

  For Each ctrl In Me.Controls
    If TypeOf ctrl Is Access.TextBox Then
      'Create the TextBox wrapper
      Set evTb = New clsEventTextBox
      Set evTb.EventsHandler = Me

      Set evTb.InnerTextBox = ctrl  'Just pass the ctrl reference without casting

      m_TbColl.Add evTb, ctrl.Name
    End If
  Next

Note the use of TypeOf in If TypeOf ctrl Is Access.TextBox Then to determine whether the control is a TextBox.

thanks, my mistake was to have forgot the 'Set' in InnerTextBox Set property :( Little OT: I have post another question [here](http://stackoverflow.com/questions/43219351/access-2016-set-control-events-at-runtime) can you know why that happens? — Barzo, Apr 05 '17 at 06:55

Access 2016 VBA TextBox is Null

2 Answers2