423,850 Members | 1,562 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

If...Then...Else

P: n/a
Hello all,

I have 13 check boxes on a form.

I am trying to check all the check boxes to determine if they are true or false when I close the form.

At present only the first IF...Then...Else works and then Exits the Sub.

Example:

If Me.First = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If

If Me.Second = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If

If Me.Third = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If
....
Total of 13 check Boxes to be checked on Form Close.

I have tried with only one End If statement at the end to no luck.

Thank you for your help.

Glen
Nov 12 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
Mal
If this is how your code appears then you really don't which IF statement is evaluating to true...since all of them are affecting the same me.fieldname
I'm not sure why you would want a check box AND a field name since added only YES to the field is the same as having a checkbox.

If its for a report/list somewhere else then handle that then.
ie. a control on a report can be set to display whatever text you like based on the value of the text box.

If this was just an example of code...not the real deal...then look for an exit sub or something within the first IF

With a little more explanation of what you want to achieve, and probably a cut & paste of the actual code...you will more than likely get a better answer

HTH
Mal.
PS: IF this really is how your code works....start changing all the names...FIRST, SECOND etc. are all ACCESS reserved words and may cause you a lot of grief later.
"GMKS" <gm**@shaw.ca> wrote in message news:8GbYb.532433$X%5.241805@pd7tw2no...
Hello all,

I have 13 check boxes on a form.

I am trying to check all the check boxes to determine if they are true or false when I close the form.

At present only the first IF...Then...Else works and then Exits the Sub.

Example:

If Me.First = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If

If Me.Second = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If

If Me.Third = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If
...
Total of 13 check Boxes to be checked on Form Close.

I have tried with only one End If statement at the end to no luck.

Thank you for your help.

Glen

Nov 12 '05 #2

P: n/a
I will explain in more detail.

This is a sports registration database for Disabled and Ablebodied members. The check boxes are there to determine the type of disability if any for those registering. As there could be family members of disabled members registering in this program that are not disabled, any check box = true would place the Disabled name in the Disability field else they would be classed as Ablebodied.

Private Sub Form_Close()

On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If
....
Me.Refresh

I hope this helps.

I have looked at the If..Then...Else including the ElseIf but I have had nothing that will go through the complete list.

I think I need to run a Do Until Record set or Block If statement but do not know how.

Glen
"Mal" <me@myHome.com> wrote in message news:10*************@corp.supernews.com...
If this is how your code appears then you really don't which IF statement is evaluating to true...since all of them are affecting the same me.fieldname
I'm not sure why you would want a check box AND a field name since added only YES to the field is the same as having a checkbox.

If its for a report/list somewhere else then handle that then.
ie. a control on a report can be set to display whatever text you like based on the value of the text box.

If this was just an example of code...not the real deal...then look for an exit sub or something within the first IF

With a little more explanation of what you want to achieve, and probably a cut & paste of the actual code...you will more than likely get a better answer

HTH
Mal.
PS: IF this really is how your code works....start changing all the names...FIRST, SECOND etc. are all ACCESS reserved words and may cause you a lot of grief later.
"GMKS" <gm**@shaw.ca> wrote in message news:8GbYb.532433$X%5.241805@pd7tw2no...
Hello all,

I have 13 check boxes on a form.

I am trying to check all the check boxes to determine if they are true or false when I close the form.

At present only the first IF...Then...Else works and then Exits the Sub.

Example:

If Me.First = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If

If Me.Second = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If

If Me.Third = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If
...
Total of 13 check Boxes to be checked on Form Close.

I have tried with only one End If statement at the end to no luck.

Thank you for your help.

Glen
Nov 12 '05 #3

P: n/a
"GMKS" <gm**@shaw.ca> wrote in
news:g1dYb.533006$X%5.299520@pd7tw2no:
I will explain in more detail.

This is a sports registration database for Disabled and
Ablebodied members. The check boxes are there to determine
the type of disability if any for those registering. As there
could be family members of disabled members registering in
this program that are not disabled, any check box = true would
place the Disabled name in the Disability field else they
would be classed as Ablebodied.

Private Sub Form_Close()

On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If
...
Me.Refresh

I hope this helps.

I have looked at the If..Then...Else including the ElseIf but
I have had nothing that will go through the complete list.

I think I need to run a Do Until Record set or Block If
statement but do not know how.

I know what your problem is!!!!!!
It is the ELSE:

modify the location of me.disability = "ablebodies" to place it
above your IF statements and all will be right
On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
End If
...
Me.Refresh
Why? In your code, if someone is blind but not deaf, watch what
happens: Blind = true, therefore you set disability to disabled,
then you evaluate Deaf = true, and it is not, so you set disability
= "ablebodied". That's not what you want. So start with
"ablebodied", and only make the if statements make the change one
way, to "disabled"

By the way, I don't know whether ELSE: is a valid construct, it has
always been else on one line, the statement to execute on the
following lines, and the end if on a line underneath. .
Bob Q
Nov 12 '05 #4

P: n/a
RE/
On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If


With Me
If ((.Blind=True) OR (.Deaf=True) OR (.Quad=True) OR (.Bald=True)) Then
.Disabled=True
Else
.Disabled=False
End If
End With
--
PeteCresswell
Nov 12 '05 #5

P: n/a
RE/
If Me.First = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If


In the example, "Else:" has become a paragraph name by virtue of the colon.
--
PeteCresswell
Nov 12 '05 #6

P: n/a
Thank you Pete

I have adjusted your example to the following:

With Me
If ((.Blind = True) _
Or (.Deaf = True) _
Or (.Paraplegic = True) _
Or (.Quadriplegic = True) _
Or (.Amputee = True) _
Or (.Disability = True) _
Or (.DevelopmentallyDelayed = True) _
Or (.LearningDisabled = True) _
Or (.BrainInjury = True) _
Or (.SteelRod = True) _
Or (.CerebralPalsy = True) _
Or (.Shunt = True) _
Or (.Braces = True) _
Or (.SpinaBifida = True)) Then
.Disability = "Disabled"
Else
.Disability = "Ablebodied"
End If
End With

I added a breakpoint to view the results.

With a single check box clicked, the value of that single check boxes is -1
and all is well but with no check boxes clicked the value of each = 0, but
the final result is .Disability = "Disabled" where it should be .Disability
= "Ablebodied".

Is there something missing?

Glen

"(Pete Cresswell)" <x@y.z> wrote in message
news:f2********************************@4ax.com...
RE/
On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If


With Me
If ((.Blind=True) OR (.Deaf=True) OR (.Quad=True) OR (.Bald=True)) Then
.Disabled=True
Else
.Disabled=False
End If
End With
--
PeteCresswell

Nov 12 '05 #7

P: n/a
"(Pete Cresswell)" <x@y.z> wrote in
news:f2********************************@4ax.com:
RE/
On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If


With Me
If ((.Blind=True) OR (.Deaf=True) OR (.Quad=True) OR
(.Bald=True)) Then
.Disabled=True
Else
.Disabled=False
End If
End With


Er, why not:

With Me
.Disabled = (!Blind) OR (!Deaf) OR (!Quad) OR (!Bald)
End With

Why use an IF/THEN/ELSE to test Boolean values that are then used to
set a Boolean value?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #8

P: n/a
On Tue, 17 Feb 2004 01:45:09 GMT, "(Pete Cresswell)" <x@y.z> wrote:
RE/
If Me.First = True Then
Me.FieldName = "Yes"
Else: Me.FieldName = "No"
End If


In the example, "Else:" has become a paragraph name by virtue of the colon.


A "paragraph name?" Do you mean a Label?

Test the wonders of VB syntax :-)

If False Then
Call MsgBox("True")
Else: Call MsgBox("False"): Call MsgBox("False again")
Call MsgBox("... and False")
End If

But you will get a compiler error message "Else without If" here:

If True Then Call MsgBox("True")
Else
Call MsgBox("False")
End If

And check out these:

If True Then Call MsgBox("True"): Call MsgBox("True again")
If True Then: Call MsgBox("True"): Call MsgBox("True again")

This means that the ":" in all these cases is a Statement separator.
You cannot have a label named "Else:" in VB, since "Else" is a
reserved word.

With kind regards
Matthias Kläy
--
www.kcc.ch
Nov 12 '05 #9

P: n/a
With Me

If .Blind And .Deaf And .Paraplegic And .Quadriplegic And .Amputee _
And .Disability And .DevelopmentallyDelayed And .LearningDisabled _
And .BrainInjury And .SteelRod And .CerebralPalsy And .Shunt _
And .Braces = True And .SpinaBifida Then

.Lucky = False

End If

End With
Nov 12 '05 #10

P: n/a
With Me

If .Blind And .Deaf And .Paraplegic And .Quadriplegic And .Amputee _
And .Disability And .DevelopmentallyDelayed And .LearningDisabled _
And .BrainInjury And .SteelRod And .CerebralPalsy And .Shunt _
And .Braces = True And .SpinaBifida Then

.Lucky = False

End If

End With
Nov 12 '05 #11

P: n/a
On Tue, 17 Feb 2004 16:00:40 GMT, "GMKS" <gm**@shaw.ca> wrote:
Thank you Pete

I have adjusted your example to the following:

With Me
If ((.Blind = True) _
Or (.Deaf = True) _
Or (.Paraplegic = True) _
Or (.Quadriplegic = True) _
Or (.Amputee = True) _
Or (.Disability = True) _
Or (.DevelopmentallyDelayed = True) _
Or (.LearningDisabled = True) _
Or (.BrainInjury = True) _
Or (.SteelRod = True) _
Or (.CerebralPalsy = True) _
Or (.Shunt = True) _
Or (.Braces = True) _
Or (.SpinaBifida = True)) Then
.Disability = "Disabled"
Else
.Disability = "Ablebodied"
End If
End With

I added a breakpoint to view the results.

With a single check box clicked, the value of that single check boxes is -1
and all is well but with no check boxes clicked the value of each = 0, but
the final result is .Disability = "Disabled" where it should be .Disability
= "Ablebodied".

Is there something missing?

Glen

You have a name conflict: Here
Or (.Disability = True) _
you treat Me.Disability as a check box, and here
.Disability = "Disabled"


you treat it as a text box.

HTH
Matthias Kläy
--
www.kcc.ch

Nov 12 '05 #12

P: n/a
Bob, thank you for your ideas.

I modified what you provided and it is the following. Originally I could
not get to cycle through the If statements.

I removed the code from the Form On Close [Event Procedure] and placed it on
a Close Form Button. It cycles through all different combinations with the
correct result.

Me.Disability = "Ablebodied"
With Me
If (.Blind = True) Then
.Disability = "Disabled"
End If

If (.Deaf = True) Then
.Disability = "Disabled"
End If

If (.Deaf = True) Then
.Disability = "Disabled"
End If

If (.Paraplegic = True) Then
.Disability = "Disabled"
End If

If (.Quadriplegic = True) Then
.Disability = "Disabled"
End If

If (.Amputee = True) Then
.Disability = "Disabled"
End If

If (.Disability = True) Then
.Disability = "Disabled"
End If

If (.DevelopmentallyDelayed = True) Then
.Disability = "Disabled"
End If

If (.LearningDisabled = True) Then
.Disability = "Disabled"
End If

If (.BrainInjury = True) Then
.Disability = "Disabled"
End If

If (.SteelRod = True) Then
.Disability = "Disabled"
End If

If (.CerebralPalsy = True) Then
.Disability = "Disabled"
End If

If (.Shunt = True) Then
.Disability = "Disabled"
End If

If (.Braces = True) Then
.Disability = "Disabled"
End If

If (.SpinaBifida = True) Then
.Disability = "Disabled"
End If
End With

Thanks to all that provided excellent samples.

Glen

"Bob Quintal" <bq******@generation.net> wrote in message
news:a7******************************@news.teranew s.com...
"GMKS" <gm**@shaw.ca> wrote in
news:g1dYb.533006$X%5.299520@pd7tw2no:
I will explain in more detail.

This is a sports registration database for Disabled and
Ablebodied members. The check boxes are there to determine
the type of disability if any for those registering. As there
could be family members of disabled members registering in
this program that are not disabled, any check box = true would
place the Disabled name in the Disability field else they
would be classed as Ablebodied.

Private Sub Form_Close()

On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
Else: Me.Disability = "Ablebodied"
End If
...
Me.Refresh

I hope this helps.

I have looked at the If..Then...Else including the ElseIf but
I have had nothing that will go through the complete list.

I think I need to run a Do Until Record set or Block If
statement but do not know how.

I know what your problem is!!!!!!
It is the ELSE:

modify the location of me.disability = "ablebodies" to place it
above your IF statements and all will be right
On Error GoTo PROC_ERR

If Me.Blind = True Then
Me.Disability = "Disabled"
End If

If Me.Deaf = True Then
Me.Disability = "Disabled"
End If
...
Me.Refresh
Why? In your code, if someone is blind but not deaf, watch what
happens: Blind = true, therefore you set disability to disabled,
then you evaluate Deaf = true, and it is not, so you set disability
= "ablebodied". That's not what you want. So start with
"ablebodied", and only make the if statements make the change one
way, to "disabled"

By the way, I don't know whether ELSE: is a valid construct, it has
always been else on one line, the statement to execute on the
following lines, and the end if on a line underneath. .
Bob Q

Nov 12 '05 #13

P: n/a
"GMKS" <gm**@shaw.ca> wrote in
news:Z0tYb.541127$X%5.317511@pd7tw2no:
Me.Disability = "Ablebodied"
With Me
If (.Blind = True) Then
.Disability = "Disabled"
End If

If (.Deaf = True) Then
.Disability = "Disabled"
End If

If (.Deaf = True) Then
.Disability = "Disabled"
End If

If (.Paraplegic = True) Then
.Disability = "Disabled"
End If

If (.Quadriplegic = True) Then
.Disability = "Disabled"
End If

If (.Amputee = True) Then
.Disability = "Disabled"
End If

If (.Disability = True) Then
.Disability = "Disabled"
End If

If (.DevelopmentallyDelayed = True) Then
.Disability = "Disabled"
End If

If (.LearningDisabled = True) Then
.Disability = "Disabled"
End If

If (.BrainInjury = True) Then
.Disability = "Disabled"
End If

If (.SteelRod = True) Then
.Disability = "Disabled"
End If

If (.CerebralPalsy = True) Then
.Disability = "Disabled"
End If

If (.Shunt = True) Then
.Disability = "Disabled"
End If

If (.Braces = True) Then
.Disability = "Disabled"
End If

If (.SpinaBifida = True) Then
.Disability = "Disabled"
End If
End With


This can be replaced with:
With Me
!Disability = "Ablebodied"
If (!Blind) OR (!Deaf) OR (!Paraplegic) OR (!Quadriplegic) _
OR (!Amputee) OR (!Disability) OR (!DevelopmentallyDelayed) _
OR (!LearningDisabled) OR (!BrainInjury) OR (!SteelRod) _
OR (!CerebralPalsy) OR (!Shunt) OR (!Braces) OR (!SpinaBifida) _
Then !Disability = "Disabled"
End With

(I don't like the dot operator for referring to controls or fields;
others disagree)

The point here:

If you're testing Booleans and any one of them being true leads to a
particular result, you should do a Boolean operation. True Or False
is always True, so any one of the choices being True will lead to
the whole construct returning True.

Your chain of IF/THENs is way too much work, as it would repeatedly
set the Disability field for each True condition (i.e., the deaf
quadraplegic would have it set twice).

Also, there's clearly something wrong as you have .Deaf twice and
you also have .Disability itself being tested for True. I assume
both are just typos.

I'm also assuming that you're not *storing* the value, as it is
derived data, and that the Disability field is for display only. In
that case, you could have the controlsource of the Disability
textbox be (no line wrapping, of course):

=IIf((Blind) OR (Deaf) OR (Paraplegic) OR (Quadriplegic) OR
(Amputee) OR (Disability) OR (DevelopmentallyDelayed) OR
(LearningDisabled) OR (BrainInjury) OR (SteelRod) OR
(CerebralPalsy) OR (Shunt) OR (Braces) OR
(SpinaBifida)),"Disabled","AbleBodied")

You would, of course, have to resolve the circularity of having the
control called Disability refer to itself in the expression above.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #14

P: n/a
RE/
Is there something missing?


Are you 100% sure the values are zero and not Null?

Set .DefaultValue=False on the control to be sure.
--
PeteCresswell
Nov 12 '05 #15

P: n/a
RE/
You have a name conflict: Here


I didn't want to go there for fear of confusing the issue, but since somebody
mentioned it:

With Me
If ((.chkBlind=True) OR (.chkDeaf=True) OR (.chkQuad=True) OR
(.chkBald=True)) Then
!Disabled=True
Else
!Disabled=False
End If
End With
--
PeteCresswell
Nov 12 '05 #16

P: n/a
"GMKS" <gm**@shaw.ca> wrote in
news:Z0tYb.541127$X%5.317511@pd7tw2no:
Bob, thank you for your ideas.

I modified what you provided and it is the following.
Originally I could not get to cycle through the If statements.

I removed the code from the Form On Close [Event Procedure]
and placed it on a Close Form Button. It cycles through all
different combinations with the correct result.

Glad to hear you got it working.

Note that your (.blind = true) statements can be simplified to just
"If .blind then" statements. Your way of writing them makes Access
do a lot of extra work for nothing, because they work out to "if
(true = true) then" of "If (false = true)"
Access actually resolves those statements and returns true or false
before doing the IF test. Doing this will make your code run
faster. I also think it reads better, but that is just my opinion.
Me.Disability = "Ablebodied"
With Me
If (.Blind = True) Then
.Disability = "Disabled"
End If

If (.Deaf = True) Then
.Disability = "Disabled"
End If

....

Bob Q
Nov 12 '05 #17

P: n/a
"(Pete Cresswell)" <x@y.z> wrote in
news:b4********************************@4ax.com:
RE/
You have a name conflict: Here


I didn't want to go there for fear of confusing the issue, but
since somebody mentioned it:

With Me
If ((.chkBlind=True) OR (.chkDeaf=True) OR (.chkQuad=True) OR
(.chkBald=True)) Then
!Disabled=True
Else
!Disabled=False
End If
End With


I keep seeing people posting this kind of thing.

It makes no sense to me.

You're basically saying:

IF x=True Then
y=True
ELSE
y=False
END IF

Why not just:

y = x

The fact that x happens to be a Boolean OR construction does not
really change the logic at all:

With Me
!Disabled = (!chkBlind) OR (!chkDeaf) OR (!chkQuad) OR (!chkBald)
End With

And if !Disabled needs to be a value and not a Boolean:

With Me
If (!chkBlind) OR (!chkDeaf) OR (!chkQuad) OR (!chkBald) Then
!Disabled = "Disabled"
Else
!Disabled = "Ablebodied"
End If
End With

In that not simpler?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #18

P: n/a
On Tue, 17 Feb 2004 22:23:36 GMT, "(Pete Cresswell)" <x@y.z> wrote:
RE/
You have a name conflict: Here


I didn't want to go there for fear of confusing the issue, but since somebody
mentioned it:

With Me
If ((.chkBlind=True) OR (.chkDeaf=True) OR (.chkQuad=True) OR
(.chkBald=True)) Then
!Disabled=True
Else
!Disabled=False
End If
End With


.... or, to simplify things a bit more:

With Me
.Disability = .Disability Or _
.Blind Or _
.Deaf Or _
.Paraplegic Or _
.Quadriplegic Or _
.Amputee Or _
.DevelopmentallyDelayed Or _
.LearningDisabled Or _
.BrainInjury Or _
.SteelRod Or _
.CerebralPalsy Or _
.Shunt = Or _
.Braces Or _
.SpinaBifid
End With

which avoids If...Then...Else completely.
Thus the "Disability" Checkbox will become True if at leat one of the
checkboxes is checked; it will become False if and only if all
checkboxes are unchecked.

Greetings, Matthias Kläy
--
www.kcc.ch
Nov 12 '05 #19

P: n/a
RE/
The fact that x happens to be a Boolean OR construction does not
really change the logic at all:

With Me
!Disabled = (!chkBlind) OR (!chkDeaf) OR (!chkQuad) OR (!chkBald)
End With

And if !Disabled needs to be a value and not a Boolean:

With Me
If (!chkBlind) OR (!chkDeaf) OR (!chkQuad) OR (!chkBald) Then
!Disabled = "Disabled"
Else
!Disabled = "Ablebodied"
End If
End With
In that not simpler?

Point is well taken because it's so much more concise.

[!Disabled = (!chkBlind) OR (!chkDeaf) OR (!chkQuad) OR (!chkBald)] can be
grasped by a glance at the line while my way makes the reader wade through
line-after line.

With me, I think it's just inertia - started coding that way, somewhere along
the line the more concise method dawned on me, but my fingers just kept typing
the same old stuff....

In that not simpler?


--
PeteCresswell
Nov 12 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.