Reference fields on a form using a variable

[liddlem]

I have a form that contains fields named Crition1, Criterion 2 .. up to .....Criterion 6 (And also Max1 to Max 6
The form is being used in continuous form mode.
In the records Before_Update event, I want to ensure that IF the user has typed text in the Criterion field, then there must be a Max value in the corresponding Max box.
I'm using MS Access - Office 365 Version 2112 (Build 14729.20260 Click to run)

Looking at a few posts, the following code should work. But I am getting a "242 runtime 'object required' " error.

Dim MyStr as String
Dim CritFld as String
DimMaxFld as String

MyStr = ""

For i = 1 To 6
    CritFld = "Criterion" & i
    MaxFld = "Max" & i
    Debug.Print "Fields : " & CritFld & ", " & MaxFld
    If Me.Controls(CritFld) Is Not Null And Me.Controls(MaxFld) Is Null Then      'Error is here
        If Me.Controls(MaxFld) Is Null Then                                                             'Error also occurs here
            MyStr = "Max" & i & vbCr
        End If
    End If
Next

if MyStr <>"" then "Display a message to the user"    'and cancel the update

Any thought welcome.

 

[The_Doc_Man]

On which line are you getting the error?

What would be the expected contents of Criterion1 thru Criterion6 and of Max1 thru Max6? (Obviously in some cases, nulls - but what would they look like when not null?)

Where is this code of yours running? More specifically, since ALL code in Access has to run in event routines of one kind or another, which event?

You have a logical tautology between the first and second IF since you can only execute the 2nd If ...maxfld... Is Null if the 1st ...maxfld... is also null. In other words, you don't need the 2nd IF at all.

Whichever line it is that triggers the 242 is probably telling you that one of the Me.Controls(control-name-in-a-string) objects is what doesn't exist. Which is something that you probably recognized and that is why you have a Debug.Print to show you the names of the controls you think you are trying.

 

[liddlem]

Ah - after some testing, I found that its not about the variable, but rather about the values in the fields.
If I change my code to this, then I am getting the expected result.

For i = 1 To 6
    CritFld = "Criterion" & i
    MaxFld = "Max" & i

'  #######    THE FOLLOWING LINE WAS CHANGED #######
    If Me.Controls(CritFld) <> "" And Me.Controls(MaxFld) = 0 Then

        If Me.Controls(MaxFld) = 0 Then
            MyStr = MyStr & "Max" & i & vbCr
        End If
     End If
Next

 

[theDBguy]

Sometimes, you might want to test for both empty and null, just to be safe.

 

[MajP]

If Me.Controls(CritFld) "" <> ""

That appears to be a typo and should read

If Me.Controls(CritFld) & "" <> ""

Because
Null & "" = ""
"" & "" = ""

Also in VBA the check to see if a variant is null is the function ISNULL which returns True or False. In SQL it can be written more like you have it.

If Me.Controls(CritFld) Is Not Null And Me.Controls(MaxFld) Is Null Then      'Error is here

Would be written as

 If not isnull(Me.Controls(CritFld)) AND isNull(Me.Controls(MaxFld)) Then

Also you can never do

if SomeVariable = NULL

This returns false always.

It truth is pretty hard to actually get a ZLS into a field unless you import from somewhere else or have code to set it to "", but it is a good habit to always check for both.