Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / April 2005

Tip: Looking for answers? Try searching our database.

select case not working right

Thread view: 
Enable EMail Alerts  Start New Thread
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.