CODEWITHSUNDEEP
ms-accessvbams-access-2010
rohrl77
rohrl77

Reputation: 3337

How to reference control names dynamically in MS Access

I have the following code in an MS Access Form object. 

Private Sub UpdatePMText(sLang As String)
'Used to pass both Mandate and Language Info to called Sub that will execute the queries
Dim iMandate As Integer

'Check if there is text in the box.
If Me.Controls("txtInput_PM_" & sLang & "_DRAFT") = Null Or Me.Controls("txtInput_PM_" & sLang & "_DRAFT") = "" Then
    MsgBox ("There is no text in the " & sLang & " DRAFT Field." & vbNewLine & "The operation will not be executed.")
    Exit Sub
End If

iMandate = Me.txtMandateID
Call modUpdateText.macro_PMText(iMandate, sLang)

End Sub

If I refer to the Controls directly and simply type out the names of the forms, for example txtInput_PM_EN_DRAFT then the code works as intended.

The error message I get is that Access can't find the "Field" I'm referring to when I am on the first IF statement line. I have tried changing the .Controls to .Fields but that didn't work either.

I do not want to repeat the same code for every language I need to run. How do I reference control names dynamically in MS Access? What am I missing?

Upvotes: 2

Views: 16634

Answers (1)

dbmitch
dbmitch

Reputation: 5386

I think you need to add some basic troubleshooting. The answer is probably simpler than you think. It's likely you're just trying to lookup a textbox with mispelled name or it's failing on the Null comparison (as suggested by @HansUp)

I would try modifying your basic sub and testing it with this subroutine. As long as your code is in your current form and you're not referencing a subform your method will work.

Private Sub UpdatePMText(sLang As String)

    Const ERR_MISSING_CONTROL   As Long = 2465

On Error GoTo Err_UpdatePMText
    Dim sTextBox    As String

    sTextBox = "txtInput_PM_" & sLang & "_DRAFT"

    'Check if there is text in the box.
    If Nz(Me.Controls(sTextBox).Value, "") = "" Then
        MsgBox ("There is no text in the " & sLang & " DRAFT Field." & vbNewLine & "The operation will not be executed.")
        Exit Sub
    End If

    Exit Sub

Err_UpdatePMText:
    If Err.Number = ERR_MISSING_CONTROL Then
        MsgBox "Error: No Such Textbox: " & sTextBox
    Else
        MsgBox "Error Looking Up Textbox: """ & sTextBox & """" & vbCrLf & Err.Description
    End If
End Sub

Upvotes: 7

Related Questions