Reference fields on a form using a variable (1 Viewer)

liddlem

Registered User.
Local time
Today, 18:18
Joined
May 16, 2003
Messages
339
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.

Code:
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

Immoderate Moderator
Staff member
Local time
Today, 13:18
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 18:18
Joined
May 16, 2003
Messages
339
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.

Code:
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

I’m here to help
Staff member
Local time
Today, 11:18
Joined
Oct 29, 2018
Messages
21,358
Sometimes, you might want to test for both empty and null, just to be safe.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:18
Joined
Feb 19, 2002
Messages
42,971
If Me.Controls(CritFld) "" <> ""

Will take care of Null and ZLS.

PS, MS has vacillated over time regarding the default value for AllowZLS. Sadly, they are now on the "Yes" option. I think this causes more problems than it solves. Yours is one of them.

I always modify the AllowZLS property to set it to No because I don't want to have to deal with both null and ZLS in tables. Also, if a text field is required, leaving the AllowZLS will allow it to be ZLS which is wrong. If I require a customer name to be present, It can't be "". That makes no sense.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:18
Joined
May 21, 2018
Messages
8,463
Code:
If Me.Controls(CritFld) "" <> ""
That appears to be a typo and should read
Code:
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.

Code:
If Me.Controls(CritFld) Is Not Null And Me.Controls(MaxFld) Is Null Then      'Error is here
Would be written as
Code:
 If not isnull(Me.Controls(CritFld)) AND isNull(Me.Controls(MaxFld)) Then
Also you can never do
Code:
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:18
Joined
Feb 19, 2002
Messages
42,971
Thanks for fixing my typo.

The way to get a ZLS string into a control on a form is for the user to type something and then instead of using exc to clear the typing or select and del, he uses the backspace.
 

Users who are viewing this thread

Top Bottom