IF statement Optimisation

[Lightwave]

Access 2003 designing a form.
I have three yes no fields and I want to show the fields if they are yes and not show them if their value is no.

I can do it as follows

On Load Event

If Field1.Value = Yes and Field.Value2.Value = Yes and Field3.Value = Yes then

Field1.Visible = True
Field2.Visible = True
Field3.Visible = True

Else

If Field1.Value = No and Field2.Value = Yes and Feild3.Value = Yes then

Field1.Visible = False
Field2.Visible = True
Field3.Visible = True

and continuing for 2 * 2 * 2 options...

and finishing with 8 End Ifs

Now that does work and for three fields with only 2 values its not too onerous I get eight IF statements that I have to make up.

But if I want to do that on eight fields I'd have to have a massive statement with 2 ^ 8 If statements


So I was wondering whether I could do more succintly as follows..

If Field1.Value = False then

Feild1.Visible = False

If Field2.Value = False then

Field2.Visible = False

If Field3.Value = False then

Field3.Value = False

Else

End If
End If
End If

The second code is much shorter and additionally it is not necessary to have every combination of the three fields as the fields are tested consecutitvely.

Unfortunately when I try the second coding in a form it doesn't seem to work with it missing some of the switches.

Can someone indicate what the optimal way of doing this sort of If switch is.
Do you just have to go through all the possibilities or is there a way of consecutively going through switches so you define each individual switch and not the sum of all of them.??
Or do you think there's a glitch in my form and the second code should work?

I think the problem with the second one is that it goes through and tests and so long as its consistent with the result its fine. Unfortunately as soon as you trigger a single switch it jumps to the Else section therefore potentially missing one or more IFs

Thanks in advance

 

[vbaInet]

You would have just written 3 IF...ELSE statements, one for each control. But you can use this construct:

Field1.Visible = Field1.Value
Field2.Visible = Field2.Value
Field3.Visible = Field3.Value

 

[Lightwave]

Thanks for the quick response VBA

How does it know what value to test...?

Do I put that in conditional formatting ?

Sorry I'm missing something

 

[vbaInet]

It looks like I'm the one that misunderstood what you were trying to do.

So the result is based on a combination of the three conditions? Can you give me two more examples.

 

[vbaInet]

I'm talking about examples based on the 8 controls or fields.

 

[smig]

you could write it like this:

If Field1.Value= True then
  Field1.Visible = True
Else     ' -- Field1.Value= False
  Field1.Visible = False
End If
 
If Field2.Value= True then
  Field2.Visible = True
Else     ' -- Field2.Value= False
  Field2.Visible = False
End If
 
If Field3.Value= True then
  Field3.Visible = True
Else     ' -- Field3.Value= False
  Field3.Visible = False
End If

Now you can easily see that the .Visible is just the sane as .Value

 

[vbaInet]

you could write it like this:

If Field1.Value= True then
  Field1.Visible = True
Else     ' -- Field1.Value= False
  Field1.Visible = False
End If
 
If Field2.Value= True then
  Field2.Visible = True
Else     ' -- Field2.Value= False
  Field2.Visible = False
End If
 
If Field3.Value= True then
  Field3.Visible = True
Else     ' -- Field3.Value= False
  Field3.Visible = False
End If

Now you can easily see that the .Visible is just the sane as .Value

That was what I mentioned in my first post and gave an example of a shorter version using three lines of code, but that's not what Lightwave is after apparently. He's using AND conditions tested against a combination of fields.

 

[Lightwave]

VBA your response was completely correct I just had a bit of a simple moment were I wasn't realising that the value of the vield was true or false and as such I could get away without the need for an If statement at all.

 

[vbaInet]

Does that answer your question?

So the AND conditions were not necessary?

 

[Lightwave]

Yes - so much simpler that way

 

[vbaInet]

Ah, alright. If the AND condition was required then you would have had some GREAT difficulty testing against EIGHT fields

 

[smig]

That was what I mentioned in my first post and gave an example of a shorter version using three lines of code, but that's not what Lightwave is after apparently. He's using AND conditions tested against a combination of fields.

I know you did, I just had the feeling he didn't see how it works

 

[vbaInet]

I know you did, I just had the feeling he didn't see how it works

It would have sunk in after a few more minutes. Lightwave is an old timer

But thanks for expanding though.

 

[smig]

Lightwave is an old timer
QUOTE]