0

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.

FanoFN
  • 6,815
  • 2
  • 13
  • 33
SidCharming
  • 25
  • 9
  • @sSidChamring are "fields" form controls? or are they table columns? – Doug Coats Mar 09 '18 at 00:30
  • Yes you can but you have to use the `Controls` collection. `If IsNull(Me.Controls(vbitem)) Then ...` – Kostas K. Mar 09 '18 at 07:01
  • @Kostas: I think the 'controls' feature does not direct me to the field. I changed my code to include your suggestion and it bypasses the if statement when the fields are actually blank. Thanks though! – SidCharming Mar 09 '18 at 16:31
  • @ Doug Coats: Fields are the names of [Text Boxes] within a form. example Last Name text box which I'll name [txtLastName] is being verified as populated before continuing to the next step in the submittal process of the form. – SidCharming Mar 09 '18 at 16:34

1 Answers1

2

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.
ComputerVersteher
  • 2,638
  • 1
  • 10
  • 20
  • this didn't help me other than I did use it to structure my if statement. – SidCharming Mar 09 '18 at 16:44
  • You don't want to refer to `Me.inSurvey#, Me.txtProjectName, .. `? `Me.[vItem]` is not a control? `IsNull(Me.[vbitem]) `is not a typo? Use [Option Explicit](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/option-explicit-statement)! – ComputerVersteher Mar 09 '18 at 17:42
  • @SidCharming: I think your If-Statement is wrong, see my updated answer. – ComputerVersteher Mar 09 '18 at 20:11
  • I changed my "If" statement as indicated in your update above... I got an error that I have a suspician is due to the name having a special character. Error is: "Run-time error 2465: Microsoft Access can't find the field 'inSurvey#' referred to in your expression" Am I on the right track of thinking? – SidCharming Mar 09 '18 at 20:30
  • Rename(suggestet, allowed chars `A-Z,a-z,0-9`,_ and avoid keywords like Name, etc.) or use square-brackets `[inSurvey#]`. – ComputerVersteher Mar 09 '18 at 20:35
  • Thank you! It's been a while since I've done this much detail in Access so I seem to have lost the limitations (I also thought Access removed the old limitations)... :) – SidCharming Mar 09 '18 at 20:44
  • That's no Ms-Access limitation (as you can use s-brackets), but a general programming convention. Special-chars sometimes want to be "special" ;). Problem solved? – ComputerVersteher Mar 09 '18 at 21:04
  • Thank you ComputerVersteher! – SidCharming Mar 12 '18 at 15:32