select case not working right

[Angi]

Pretty straight forward, I think. Everything works fine except case 1
and 3...runs 1 and 1 code. I can't, for the life of me, figure out
why. I've made sure it's enabled. I've tried "coid =" &
forms!frmsearch!txtcocoid...didn't work either. Any ideas??

Select Case frmSearch And frmOptions
Case 1 And 1
DoCmd.OpenReport "newcorec", acViewPreview, , "coid=" &
Me.txtCoCOID
Case 1 And 2
DoCmd.OpenReport "companyrecord", acNormal, , "coid=" &
Me.txtCoCOID
Case 1 And 3
DoCmd.OpenForm "companymain", acNormal, , "coid=" &
Me.txtCoCOID
End Select

 

[Wayne Morgan]

From the immediate window:

?1 and 1
1
?1 and 2
0
?1 and 3
1

As you can see, the result for "1 and 3" is the same as the result for "1
and 1". I believe the term for this is "bit masking". 1 in binary is 01, 2
is 10, and 3 is 11.

Perhaps someone more familiar with this technique can explain the problem
better. I did find this explanation using a Google search.

http://www.tcpipguide.com/free/t_BitMaskingSettingClearingandInvertingUsingBooleanL.htm

 

[Angi]

Wayne...
Thanks for that!! I would've never thought of bit masking. Honestly,
I've never heard of it. My first question is, how did you get those
results? And, secondly, would it help to change the option values to
different numbers??

Thanks

 

[Wayne Morgan]

You saw how I got the results. I typed in the lines with the question marks
(including the question marks) and when I pressed Enter, the following line
was the result.

For what you're trying to do, you may want to concatenate instead of using
the Logical And. To do this, try replacing the word And with the & sign.
This is actually a string function, but I believe it will do what you're
after.

Select Case frmSearch & frmOptions
Case "11"
DoCmd.OpenReport "newcorec", acViewPreview, , "coid=" & Me.txtCoCOID
Case "12"
DoCmd.OpenReport "companyrecord", acNormal, , "coid=" & Me.txtCoCOID
Case "13"
DoCmd.OpenForm "companymain", acNormal, , "coid=" & Me.txtCoCOID
End Select

 

[Angi]

Wayne,
Forgive me, but where did you type the ?1 and 1??? It says immediate
window, but what window? Just wondering, because that's pretty handy.

Also, I tried everything you said:
Case 1 & 1
Case "1" & "1"
Case "1 & 1"
Case "11"

Nothing works...now I don't get anything. Before I was at least
getting the reports to show and print. That's what's so frustrating!!
I read through the link you posted, but I don't see anything that would
help me there. I must be missing something.

 

[Van T. Dinh]

SELECT Case doesn't work with 2 "index" variables which you have (frmSearch
& frmOptions - I think you use "frm" as the prefix for Option Frames but
"frm" is normally use for Forms. I use "fra" for Option Frames.).

If I work out your logic correctly, try:

Select Case frmSearch
Case 1
Select Case frmOptions
Case 1
DoCmd.OpenReport "newcorec", acViewPreview, , _
"coid=" & Me.txtCoCOID
Case 2
DoCmd.OpenReport "companyrecord", acNormal, , _
"coid=" & Me.txtCoCOID
Case 3
DoCmd.OpenForm "companymain", acNormal, , _
"coid=" & Me.txtCoCOID
End Select '(frmOptions)

Case Else
' What ever you want to do if frmSearch <> 1
End Select '(frmSerach)

There are other fancy ways to use a single Select Case statement but I think
a nested Select Case statement is much clearer than the fancy ways.

HTH
Van T. Dinh
MVP (Access)

 

[Angi]

Van,
Thanks! That was my next option. I was going to do that originally
but thought I would try it this way first. Why does it work right for
the first 2 cases?? Doesn't make much sense. They have different
commands but yet it knows how to differentiate them.

Also, I know frm is the correct syntax for forms, but in a form I use
it to refer to a frame control...don't ask...makes sense in my crazy
head!! Thanks for the FYI though!

BR,
Angi

 

[Angi]

I spoke too soon!! It does work!! I forgot to change the frmSearch
and frmOptions to frmSearch & frmOptions. After I changed ALL the ands
to &...it works like a charm!! Thank you, Wayne for the help!!

 

[Van T. Dinh]

Your old statement works correctly for the first 2 cases by accident but
logically. Wayne actually answered this in his explanation of the "bit-wise
And".

I am sure your logic was:

Case 1: If frmSearch = 1 and frmOptions = 1, open "newcorec"
Case 2: If frmSearch = 1 and frmOptions = 2, open "companyrecord"
Case 3: If frmSerach = 1 and frmOptions = 3, open "companymain"

(don't know what you wanted to do if frmSearch <> 1)

However, this is not how the case statement works. Remember that I wrote
that Select Case doesn't work with 2 "index" variables. It can only accept
1 "index" variable and therefore VBA interprets the single "index" variable
to be (frmSearch And frmOptions), i.e. the result of the bit-wise And
operation as the "index" variable for Select case.

Note also that (according to your code of the cases inside the Select Case):

Case A: (1 And 1) gives 1 in bit-wise And calculation.
Case B: (1 And 2) gives 0 in bit-wise And calculation.
Case C: (1 And 3) gives 1 in bit-wise And calculation.

Thus:

* If you have Case 1 above, i.e. (frmSearch And frmOptions) evaluated to 1 ,
the code will execute Case A which happens to be what you wanted.

* If you have Case 2 above, i.e. (frmSearch And frmOptions) evaluated to 0,
the code will execute Case B which happens to be what you want.

* However, if you have case 3 above, the expression (frmSearch And
frmOptions) is evaluated to 1 using bit-wise And operation and your code
will execute Case A which is _not_ what you wanted.

In fact, the Case C will never be executed in your code because it it the
same as Case A, i.e. 1 in bit-wise operation and since Case A appears first
in your code, it will be executed if the "index" Variable happens to be
(evaluated to) 1.

I mentioned "frm" just in case ...

Does that explains why the code behaved as it did?

HTH
Van T. Dinh
MVP (Access)

 

[Angi]

Van,
Yes, that explanation worked...thank you! It does work now using the &
instead of AND without having to write the extra case statements (which
helps me keep my logic straight!). As far as frmSearch<>1, I have
cases all the way through 6, but just used the 1 as the example since
it was going to be the same problem all the way down.

I still haven't figured out where Wayne put the ?1 and 1 to get the 1
answer. That would be helpful to me for future situations to check if
that's the problem.

Thanks to you too, Van! Sorry I didn't put that in earlier... was a
little excited about it working!

 

[Van T. Dinh]

Open the VBE window.

"Immediate" window / pane is where you want to type "?1 And 1". IIRC, this
pane used to be called "Debug" window in A97 & earlier (???).

If you don't see this pane, use the Menu View / Immediate Window to make it
visible.

HTH
Van T. Dinh
MVP (Access)

 

[Angi]

Van,
Thank you! Thought I was going crazy!

br,
Ang

 

[Wayne Morgan]

The Immediate Window is at the bottom of the code window, unless you've
moved it. A quick key entry to get there is Ctrl+G.