MS Access Forum / Forms Programming / April 2005
select case not working right
|
|
Thread rating:  |
Angi - 28 Apr 2005 03:06 GMT 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 - 28 Apr 2005 03:57 GMT 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_BitMaskingSettingClearingandInvertingUsingBoole anL.htm
 Signature Wayne Morgan MS Access MVP
> 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 [quoted text clipped - 12 lines] > Me.txtCoCOID > End Select Angi - 28 Apr 2005 04:11 GMT 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 - 28 Apr 2005 04:52 GMT 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
 Signature Wayne Morgan MS Access MVP
> Wayne... > Thanks for that!! I would've never thought of bit masking. Honestly, [quoted text clipped - 3 lines] > > Thanks Angi - 28 Apr 2005 05:08 GMT 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.
Wayne Morgan - 28 Apr 2005 13:23 GMT 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.
 Signature Wayne Morgan MS Access MVP
> Wayne, > Forgive me, but where did you type the ?1 and 1??? It says immediate [quoted text clipped - 10 lines] > 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 - 28 Apr 2005 05:18 GMT 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)
> 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 [quoted text clipped - 12 lines] > Me.txtCoCOID > End Select Angi - 28 Apr 2005 05:26 GMT 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 - 28 Apr 2005 05:32 GMT 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 - 28 Apr 2005 06:20 GMT 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)
> Van, > Thanks! That was my next option. I was going to do that originally [quoted text clipped - 8 lines] > BR, > Angi Angi - 28 Apr 2005 07:01 GMT 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 - 28 Apr 2005 08:19 GMT 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)
> Van, > Yes, that explanation worked...thank you! It does work now using the & [quoted text clipped - 9 lines] > Thanks to you too, Van! Sorry I didn't put that in earlier... was a > little excited about it working! :-) Angi - 28 Apr 2005 08:32 GMT Van, Thank you! Thought I was going crazy!
br, Ang
|
|
|