Taming an If...ElseIf

  • Thread starter Thread starter 77m.grub
  • Start date Start date
7

77m.grub

I have 3 checkboxes on a form, and a user can select one *or more* of
the
checkboxes, in any combination. When the user clicks on a command
button, some code is run that builds a SQL Statement based on the
user's checking of the various checkboxes. The code currently reads,
in relevant
part:

If chkOne And Not chkTwo and Not chkThree Then
Do This...
Elseif Not chkOne And chkTwo and Not chkThree Then
Do This...
Elseif Not chkOne And Not chkTwo and chkThree Then
Do This...
Elseif chkOne And chkTwo and Not chkThree Then
Do This...
Elseif chkOne And Not chkTwo and chkThree Then
Do This...
Elseif Not chkOne And chkTwo and chkThree Then
Do This...
Elseif chkOne And chkTwo and chkThree Then
Do This...
Elseif Not chkOne And Not chkTwo and Not chkThree Then
Do This...
End If


Problem is that this is about to become a migraine when I add 7 more
checkboxes, for a total of 10. Ack! Is there a more efficient way
to
do this? Thanks!


Mike
 
(e-mail address removed) wrote in
I have 3 checkboxes on a form, and a user can select one *or more*
of the
checkboxes, in any combination. When the user clicks on a command
button, some code is run that builds a SQL Statement based on the
user's checking of the various checkboxes. The code currently
reads, in relevant
part:

If chkOne And Not chkTwo and Not chkThree Then
Do This...
Elseif Not chkOne And chkTwo and Not chkThree Then
Do This...
Elseif Not chkOne And Not chkTwo and chkThree Then
Do This...
Elseif chkOne And chkTwo and Not chkThree Then
Do This...
Elseif chkOne And Not chkTwo and chkThree Then
Do This...
Elseif Not chkOne And chkTwo and chkThree Then
Do This...
Elseif chkOne And chkTwo and chkThree Then
Do This...
Elseif Not chkOne And Not chkTwo and Not chkThree Then
Do This...
End If


Problem is that this is about to become a migraine when I add 7
more checkboxes, for a total of 10. Ack! Is there a more
efficient way to
do this? Thanks!


Mike
Click to expand...
my technique is to assign numbers to each checkbox and use a select
case statement, so rename your boxes chk1, chk2, etc. to chk10 the
loop gets the value of each textbox and raises the value to 2^
checkboxnumber Note that with 10 checkboxes you'll get 1024
possible combinations.(0 to 1023)


Dim idx As Integer ' index for loop
Dim cbs As Integer 'sum of combo box values
For idx = 1 To 10
cbs = cbs + Abs(Nz(Me("chk" & idx), 0) * 2 ^ (idx - 1))
Next

select case CBS
case 0 'no boxes checked.
case 1 ' only 1 checked
do this
case 2
do that
case 3 ' checkbox 1 + 2
case 3, 7 11
do the other thing
end select
 
The answer really depends on what the "Do this" section is in each case. Is
there something that's always done if chkOne is True, and never done if
chkOne is False regardless of the values of the other check boxes? Is this
something that depends on how many check boxes are True, regardless of which
specific ones are True? Are check boxes really the best interface for what
you're doing?
 
The answer really depends on what the "Do this" section is in each case. Is
there something that's always done if chkOne is True, and never done if
chkOne is False regardless of the values of the other check boxes? Is this
something that depends on how many check boxes are True, regardless of which
specific ones are True? Are check boxes really the best interface for what
you're doing?
Click to expand...

To answer your first question, yes -- the same thing is done if chkOne
is true regardless of the other values of the checkboxes... To answer
your second question, each is independent, except for the appending of
SQL Code (see below -- the options have to be separated with OR; they
are independent). The form has 3 checkboxes: chkOne, chkTwo, and
chkThree and a "Go" command button that executes the following code,
which builds a SQL statement to query a single database. The database
contains individuals names, etc. and a field that I want to query
called "cd_campus" -- the various options that can go in the Campus
field (FT, FA, FB) are the exact same options that the user can check
on the form. Does that make sense? Here is the code with the "Do
Thisses" -- very much appreciate any advice you have!

If chkOne And Not chkTwo and Not chkThree Then
strOption = " AND ((Person.cd_Campus) = ""FT"")"
Elseif Not chkOne And chkTwo and Not chkThree Then
strOption = " AND ((Person.cd_Campus) = ""FA"")"
Elseif Not chkOne And Not chkTwo and chkThree Then
strOption = " AND ((Person.cd_Campus) = ""FB"")"
Elseif chkOne And chkTwo and Not chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FT"") OR
((Person.cd_Campus) = ""FA""))"
Elseif chkOne And Not chkTwo and chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FT"") OR
((Person.cd_Campus) = ""FB""))"
Elseif Not chkOne And chkTwo and chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FA"") OR
((Person.cd_Campus) = ""FB""))"
Elseif chkOne And chkTwo and chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FT"") OR
((Person.cd_Campus) = ""FA"") OR (Person.cd_Campus) = ""FB""))"
Elseif Not chkOne And Not chkTwo and Not chkThree Then
strOption = ""
End If

strWhere = "((Person.cd_status)=""NYA"")" & strOption

DoCmd.OpenReport "rptListing", acViewPreview, , strWhere
 
(e-mail address removed) wrote in

Based on what you say below, you do not need any elseif statements,
just 10 if statements. And if you use the IN statement instead of
ORs, you can make the code even simpler.

IfCheck1 then strOption = strOption & ",'FT'"
IfCheck2 then strOption = strOption & ",'FA'"
.. . .

strOption = mid(strOption,2) ' remove leading comma.

If len(trim(strOption) > 0 then
strOption = "AND [Person.cd_Campus] IN (" & strOption & ")"
end if
strWhere = "((Person.cd_status)= "NYA")" & strOption



To answer your first question, yes -- the same thing is done if
chkOne is true regardless of the other values of the checkboxes...
To answer your second question, each is independent, except for
the appending of SQL Code (see below -- the options have to be
separated with OR; they are independent). The form has 3
checkboxes: chkOne, chkTwo, and chkThree and a "Go" command
button that executes the following code, which builds a SQL
statement to query a single database. The database contains
individuals names, etc. and a field that I want to query called
"cd_campus" -- the various options that can go in the Campus field
(FT, FA, FB) are the exact same options that the user can check
on the form. Does that make sense? Here is the code with the "Do
Thisses" -- very much appreciate any advice you have!

If chkOne And Not chkTwo and Not chkThree Then
strOption = " AND ((Person.cd_Campus) = ""FT"")"
Elseif Not chkOne And chkTwo and Not chkThree Then
strOption = " AND ((Person.cd_Campus) = ""FA"")"
Elseif Not chkOne And Not chkTwo and chkThree Then
strOption = " AND ((Person.cd_Campus) = ""FB"")"
Elseif chkOne And chkTwo and Not chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FT"") OR
((Person.cd_Campus) = ""FA""))"
Elseif chkOne And Not chkTwo and chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FT"") OR
((Person.cd_Campus) = ""FB""))"
Elseif Not chkOne And chkTwo and chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FA"") OR
((Person.cd_Campus) = ""FB""))"
Elseif chkOne And chkTwo and chkThree Then
strOption = " AND (((Person.cd_Campus) = ""FT"") OR
((Person.cd_Campus) = ""FA"") OR (Person.cd_Campus) = ""FB""))"
Elseif Not chkOne And Not chkTwo and Not chkThree Then
strOption = ""
End If

strWhere = "((Person.cd_status)=""NYA"")" & strOption

DoCmd.OpenReport "rptListing", acViewPreview, , strWhere
Click to expand...
 
Modify your code to:

Dim VarOption As Variant
Dim C As Access.Control
Dim CCb As Access.CheckBox

varOption = Null
For Each C In Me.Controls
If C.ControlType = acCheckBox Then ' Put the Criteria in the Tag Property
Set CCb = C
If CCb.Value = True Then
VarOption = (VarOption + ", ") & "'" & CCb.Tag & "'"
End If
End If
Next
strWhere = "Person.cd_status='"NYA"'"
If Not VBA.IsNull(VarOption) Then
strWhere = strWhere & " And Person_Cd In (" & varOption & ")"
End If
HtH

Pieter
 
Here's one approach to consider.

***************
If chkOne Then strOption = "((Person.cd_Campus = 'FT') OR "
If chkTwo Then strOption = strOption & "((Person.cd_Campus) ='FA') OR "
If chkThree Then strOption = strOption & "((Person.cd_Campus) = 'FB') OR "

If Len(strOption) >0 Then
' At least one checkbbox was selected. Strip the trailing " OR "
strOption = Left(strOption,Len(strOption)-4)
' Add the AND clause and a pair of parentheses
strOption = " AND (" & strOption & ")"
strWHERE = strWHERE & strOption
End If
***************

If you are only referring to one field, a better approach would probably be
to construct an IN() clause:

***************
If chkOne Then strOption = "'FT', "
If chkTwo Then strOption = strOption & "'FA', "
If chkThree Then strOption = strOption & "'FB', "

If len(strOption)>0 Then
' At least one checkbox was selected. Remove trailing ", ".
strOption = strOption & Left(strOption, Len(strOption)-2)
' Add AND and IN clauses (and a pair of parentheses for both)
strOption = " AND (Person.cd_Campus IN(" & strOption & "))"
strWHERE = strWHERE & strOption
End If
***************

If you are increasing the number of options to 10, consider using a
multi-select listbox instead. The IN() approach lends itself well to that:
just loop through the ItemsSelected property, adding the selections (+
quotes + commas) as you go. I think you'll find maintenance much easier
When (not if) another value needs to get added to the listbox, you probably
won't need to touch either the form or your code, just add the value to the
table that feeds the query used for the listbox...

What if the user selects ALL options? I assume that would be the same as
selecting No options (Show All = No Filter = (strOption = "")), but I
haven't included any check for that possibility here.

HTH,
 
Here's one approach to consider.

***************
If chkOne Then strOption = "((Person.cd_Campus = 'FT') OR "
If chkTwo Then strOption = strOption & "((Person.cd_Campus) ='FA') OR "
If chkThree Then strOption = strOption & "((Person.cd_Campus) = 'FB') OR "

If Len(strOption) >0 Then
' At least one checkbbox was selected. Strip the trailing " OR "
strOption = Left(strOption,Len(strOption)-4)
' Add the AND clause and a pair of parentheses
strOption = " AND (" & strOption & ")"
strWHERE = strWHERE & strOption
End If
***************

If you are only referring to one field, a better approach would probably be
to construct an IN() clause:

***************
If chkOne Then strOption = "'FT', "
If chkTwo Then strOption = strOption & "'FA', "
If chkThree Then strOption = strOption & "'FB', "

If len(strOption)>0 Then
' At least one checkbox was selected. Remove trailing ", ".
strOption = strOption & Left(strOption, Len(strOption)-2)
' Add AND and IN clauses (and a pair of parentheses for both)
strOption = " AND (Person.cd_Campus IN(" & strOption & "))"
strWHERE = strWHERE & strOption
End If
***************

If you are increasing the number of options to 10, consider using a
multi-select listbox instead. The IN() approach lends itself well to that:
just loop through the ItemsSelected property, adding the selections (+
quotes + commas) as you go. I think you'll find maintenance much easier
When (not if) another value needs to get added to the listbox, you probably
won't need to touch either the form or your code, just add the value to the
table that feeds the query used for the listbox...

What if the user selects ALL options? I assume that would be the same as
selecting No options (Show All = No Filter = (strOption = "")), but I
haven't included any check for that possibility here.

HTH,










- Show quoted text -
Click to expand...

Hey all, "IN" was the missing piece. The code Bob posted above
worked like a charm. Many thanks!! Mike
 
Bob Quintal said:
(e-mail address removed) wrote in

my technique is to assign numbers to each checkbox and use a select
case statement, so rename your boxes chk1, chk2, etc. to chk10 the
loop gets the value of each textbox and raises the value to 2^
checkboxnumber Note that with 10 checkboxes you'll get 1024
possible combinations.(0 to 1023)


Dim idx As Integer ' index for loop
Dim cbs As Integer 'sum of combo box values
For idx = 1 To 10
cbs = cbs + Abs(Nz(Me("chk" & idx), 0) * 2 ^ (idx - 1))
Next

select case CBS
case 0 'no boxes checked.
case 1 ' only 1 checked
do this
case 2
do that
case 3 ' checkbox 1 + 2
case 3, 7 11
do the other thing
end select
Click to expand...
 
Back
Top