Validate this and that n stuff

[deejay_totoro]

Hello all,

On my form, I would like to limit some of the user input.

For example, there are three fields:

fld1
fld2
fld3

What I would like is:

fld1 can be entered freely
fld2 can only be entered if there is no data in either fld1 or fld3
fld3 cant be entered if there is data in fld1, otherwise enter data

Any help would be much appreciated (as always!)

Many thanks,

dj_T

 

[ByteMyzer]

Try pasting the following code into your form's code window (the following code assumes that the actual field names are as you outlined):

Private Sub fld2_GotFocus()
    fld2.Locked = Not(Nz(fld1, "") = "" And Nz(fld3, "") = "")
End Sub

Private Sub fld3_GotFocus()
    fld3.Locked = Not(Nz(fld1, "") = "")
End Sub

See if this works for you.

 

[deejay_totoro]

Got it!

Hello again,

Thanks for your reply.

Actually, once I applied your code I realised that my initial rules where wrong

However I was able to work out what to do byt using your code.

Thanks for that!

May I ask a simple question about the syntax?

fld3.Locked = Not(Nz(fld1, "") = "")

I read this as: lock fld3 if fld1 is empty, but I don't understand the last bit:

Not(Nz(fld1, "") = "")

How does this work?

Thanks again!

dj_T

 

[ByteMyzer]

All right, let's break down the expression Not(Nz(fld1, "") = "") into its individual elements, from the inside out.

The function Nz() is used to evaluate an expression or variable and to return its value if it is not null, or a different value if it is null.

For example:
Nz(strTemp, "(Empty)")

If we do not set a value for strTemp, it will be null, so the expression will return (Empty) as the value. If, however, we set strTemp="Hello there", the expression will return Hello There.

Next, let's look at the expression Nz(fld1, "") = "". If fld is null, then Nz(fld1, "") will return an empty string (""). Thus, Nz(fld1, "") = "" will return a value of True.

Finally, the expression Not(Nz(fld1, "") = ""). The operator Not takes the returned value and gives the opposite result. So, if Nz(fld1, "") = "" returns True, then Not(Nz(fld1, "") = "") returns False.

Now, let's look at how that affects the state of fld3.

fld3.Locked = Not(Nz(fld1, "") = "")

If fld1 is null, then the expression Not(Nz(fld1, "") = "") returns False, so the Locked state of fld3 is set to False; thus you can edit data in fld3.

If, however, fld1 is NOT null, then the expression Not(Nz(fld1, "") = "") returns True, so the Locked state of fld3 is set to True; thus you can not edit data in fld3.

 

[WayneRyan]

dj,


fld3.Locked = Not(Nz(fld1, "") = "")

1) Assume fld1 is empty, giving:

fld3.Locked = Not(Nz(Null, "") = "")
fld3.Locked = Not("" = "")
fld3.Locked = Not(True)
fld3.Locked = False

2) Assume fld1 = "x", giving:

fld3.Locked = Not(Nz("x", "") = "")
fld3.Locked = Not("x" = "")
fld3.Locked = Not(False)
fld3.Locked = True

I think that the logic is wrong.

Why not take the simple approach?

If Len(Me.fld1) > 0 Then
   Me.fld3.Locked = False ' fld3 has data
Else
   Me.fld3.Locked = True  ' fld3 has no data
End If

Wayne

 

[ByteMyzer]

No, WayneRyan my logic is correct. deejay_totoro wants fld3 to be locked if there IS data in fld1, so your logic is wrong.

 

[WayneRyan]

Sorry,

mis-read the - "fld3 cant be entered"
to be - "fld3 can be entered"

Wayne