CODEWITHSUNDEEP
vbams-access
Pony99CA
Pony99CA

Reputation: 89

How Do I Get a Reference to a Text Box Control Instead of the Text Box's Value in VBA

This seems so simple, but I can't figure out how to get a reference to a Text Box control in the Details section of a form instead of the Text Box's value in VBA.

Here's my exact code. I'm wrapping the DoCmd.FindRecord method to support searching in a specified field on a form or anywhere on the form. To search only in a specified field, I pass the control name.

Public Function FindExactRecord(frm As Form, strField As String, strFind As String) As Boolean
    
    On Error GoTo ERR_FUNC
    
    Dim rc As Boolean
    Dim ctrl As control
    
    rc = True                   ' Presume true
    
    If strField = "" Then
        ' Find the record anywhere on the form
        Call DoCmd.FindRecord(strFind, acEntire, True, acSearchAll, False, acAll, True)
    Else
        ' NOTE:  Currently not working!
        ' Set the current field
        Set ctrl = frm.Controls(strField)
        frm.ctrl.SetFocus
        
        ' Find the record in the specified field
        Call DoCmd.FindRecord(strFind, acEntire, True, acSearchAll, False, acCurrent, True)
        
        ' Check if record found
        rc = (frm.ctrl = strFind)
    End If
  
    ' Return success or failure
    FindExactRecord = rc
End Function

You'd think that would set ctrl to a reference to the txtBox control, but it actually sets ctrl to a string containing the value contained in txtBox (because Me.txtBox actually is an implicit reference to the default property of the txtBox, which is Value). This causes the call to frm.ctrl.SetFocus call to throw error #2465 (Application-defined or object-defined error). If I pass in the empty string for the control name (and therefore search the entire form), I don't get the error (because I don't reference the control at all).

I've tried various other ways to avoid getting the default property, but none of them work.

What's the magic to get a reference to the control?

Upvotes: 2

Views: 1283

Answers (2)

Gustav
Gustav

Reputation: 55816

Try this:

        ' Set the current field
        Set ctrl = frm.Controls(strField)
        ctrl.SetFocus
        
        ' Make sure, that frm is the selected and active form.
        ' Find the record in the specified field
        Call DoCmd.FindRecord(strFind, acEntire, True, acSearchAll, False, acCurrent, True)
        
        ' Check if record found
        rc = (ctrl.Value = strFind)

Upvotes: 1

Andre
Andre

Reputation: 27634

As June7 wrote, you are misinterpreting something (you didn't show what).

ctrl is indeed a reference to the textbox control.
Try this:

Dim ctrl As Control

Set ctrl = Me.txtBox

' These wouldn't work, if ctrl was simply the string in txtBox
Debug.Print ctrl.Name
Debug.Print ctrl.BackColor
' These will both print the content
Debug.Print ctrl
Debug.Print ctrl.Value

frm.ctrl.SetFocus

This looks for a property ctrl in the form object (and fails).

Simply use ctrl.SetFocus, and replace frm.ctrl by ctrl anywhere else too.

Upvotes: 4

Related Questions