CODEWITHSUNDEEP
vbaformsms-access
SidCharming
SidCharming

Reputation: 25

Can I use a variable as the name of a field in a form?

I am trying to set a validation rule for a form to see if fields are populated.

I figure using an array of field names within the current form and a loop will solve this without too much coding. Trying to use the array value in the loop is my struggle.

Dim avFields As Variant
Dim vItem As Variant
      
avFields = Array("inSurvey#", "txtProjectName", "cboPracticeType", "cboProjectType", "cboClient", "txtLaunchDt", "txtEndDt", "cboCRM", "cboPM1", "cboLC1", "cboAN1", "cboCO1", "cboSC1", "cboPRI")
      
For Each vItem In avFields
    Debug.Print vItem
    If IsNull(Me.[vbitem]) Then
        MsgBox "Please Enter a Project Name", vbOKOnly
        Me.vItem.SetFocus
        Exit Sub
    End If
Next

The hang up is in Me.vItem.SetFocus.

Upvotes: 0

Views: 2786

Answers (1)

ComputerVersteher
ComputerVersteher

Reputation: 2686

Duplicate of How can a reference fields on an Access form using a variable?

You can't use a variable after a dot, as it is has to be a member of the object (here: the form). You have to use the Controls-Collection, which is a member of the form and pass the variable to the Item-Property Me.Controls.Item(vItem), that returns the reference to the control. 

If IsNull(Me.Controls(vItem)) Then   ' short for Me.Controls.Item(vItem).Value
  MsgBox "Please Enter a Project Name", vbOKOnly
  Me(vItem).SetFocus ' short for Me.Controls(vItem).SetFocus
  Exit Sub
End If

If you need to refer a member by a var that has no collection you can use the CallByName function like this:

Set myControl = CallByName(Me, vItem, vbGet)

Update:

I changed my code to include your suggestion and it bypasses the if statement when the fields are actually blank

brings me to IsNull(Me.myTextBox)is not reliable in any cases. If you want to use the Fields input before the AfterUpdate event of the control, the value is not set and you have to use Me.myTextBox.Text.

Another trap: Me.myTextBox(.Value)can be an empty string "" (different to NULL). This has to be catched by:

If Len(Me.Controls(vItem) & vbNullString) = 0 Then ' if control.value is NULL the result string is an empty string (vbNullString is the const for "") as NULL & "" = "". Len() is used instead of Me.Controls(vItem) & vbNullString = vbNullString, because it is faster.

Upvotes: 2

Related Questions