How can I fix a run-time error when capturing text from a textbox in a form?

Question

I am trying to capture the value entered into the text box of a form instance which was created using the following module code:

Public myForm As Form_Form1    
'Dim myForm As Form_Form1       ' tried this

Sub test()
'Dim myForm As New Form_Form1   ' tried this

Set myForm = New Form_Form1
With myForm
    .Visible = True
'    .SetFocus          ' tried this
'    .Modal = True      ' tried this

    If .IsCancelled Then
        Exit Sub
    End If

    Debug.Print .RptDt

End With

The form is very basic with an OK and Cancel button and a single text box named Text7. The form code-behind is:

Private cancelling As Boolean

Public Property Get RptDt() As String
    RptDt = Text7.Text
End Property

Public Property Get IsCancelled() As Boolean
    IsCancelled = cancelling
End Property

Private Sub Command2_Click()
    'DoCmd.Close acForm, Me.Name
    Me.Visible = False
    'Me.Visible
End Sub

Private Sub Command4_Click()
    cancelling = True
    'DoCmd.Close acForm, Me.Name
    'MsgBox Me.Name
    'MsgBox Me.OpenArgs

    'Me.Hide
    Me.Visible False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        cancelling = True
    Me.Visible = False
    End If
End Sub

When I run the code as is I get:

"Run-time error '2185' You can't reference a property or method for a control unless the control has the focus"

I have also tried:

 Public Property Get RptDt() As String
    RptDt = Text7.Value
End Property

I then get Run-time error '94' Invalid use of null. The code above was modified from a comparable Excel VBA code which uses the Userform Show method (only works in Excel) in the Sub Test() instead of .Visible = True.

The error is being raised at the ``` Debug.Pring .RptDt``` line. — ChM

RazorKillBen RazorKillBen · Accepted Answer · 2020-02-26T13:18:28

Just a side point, but on the Command4_Click event change the below line to add an equals:

Me.Visible = False

This can change the syntax and is a different function to the one intended.

The .Text property can only be called when a Control has the focus. Similarly, a null exception will be called if you use .Value in it's place when the textbox is empty.

There are two ways around this:

Option 1 - Handling the NULL value

Public Property Get RptDt() As String
    If IsNull(Text7.Text) = True Then
        RptDt = "EmptyString"    'Or whatever string you want to set this to
    Else
        RptDt = Text7.Text
    End if
End Property

Option 2 - Setting the focus

Public Property Get RptDt() As String
    Text7.SetFocus
    RptDt = Text7.Text
End Property

How can I fix a run-time error when capturing text from a textbox in a form?

1 Answers