Variable as Control Name for Setfocus

[BoxcarArty]

Within VBA I can successfully do this:

Forms!F_Contact_Data_Entry!Combo_Rep_ID.SetFocus

But what I really need to do is replace the control name (Combo_Rep_ID) with a variable. I've tried using a string variable. I assigned a value to the string, then tried to execute the .SetFocus.

Option Explicit
Dim target_field_name As String

Private Function RequiredFieldsArePopulated() As Boolean

Dim rc As Long

RequiredFieldsArePopulated = False

If IsNull(Forms!F_Contact_Data_Entry.Combo_Rep_ID) Then
    target_field_name = "Combo_Rep_ID"
    Forms!F_Contact_Data_Entry.SetFocus
    rc = MsgBox("A Representative ID is required", vbExclamation, _
            "Function CheckRequiredFieldsForData")

ElseIf IsNull(Forms!F_Contact_Data_Entry.Combo_Show_ID) Then
    target_field_name = "Combo_Show_ID"
    Forms!F_Contact_Data_Entry.Combo_Show_ID.SetFocus
    rc = MsgBox("A Show ID is required", vbExclamation, _
            "Function CheckRequiredFieldsForData")

Else
    RequiredFieldsArePopulated = True
    End If

End Function  ' CheckRequiredFieldsForData


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rc As Integer
If Not RequiredFieldsArePopulated() Then
    Me.Undo
    'Forms!F_Contact_Data_Entry!Combo_Rep_ID.SetFocus  'WORKS BUT IS NOT WHAT I NEEED
    Forms!F_Contact_Data_Entry!target_field_name.SetFocus
    End If
End Sub

The above attempt yielded Run-Time error 2465, "Can't find the field 'target_field_name' referred to in your expression."

Any ideas on getting the variable control name to work?

 

[ByteMyzer]

That should be:

Forms("F_Contact_Data_Entry").Controls(target_field_name).SetFocus

 

[mhartman]

Hello:

What you want to do is declare your variable as a CONTROL. Then use the SET statement to set that control equal to a control or other object in your database.
Regards
Mark

 

[BoxcarArty]

That should be:

Forms("F_Contact_Data_Entry").Controls(target_field_name).SetFocus

You are correct. I just tried your suggestion and it works. It was obvious it would as soon as I read it.

I believe I'm about to get the hang of this (MS Access).