Case statement Problem

External Since: Apr 04, 2006 Posts: 946

Hi Tony,

did you compile your code?

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database

~~~~~~~~~~~~~~~~

"Happy 4th of July. Proud to be an American."

Happy Independence Day!

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day Smile

*

tony Jacobs wrote:
> No Damon;
>
> The problem is that the same report opens in all cases. Which means that the
> case statement is not picking up the value of the combo box.
> The open report works perfectly and it tells me which records in the list
> box it is about to print.
>
> Now what I am trying to do is limit the list box to all items that start
> with a C, then print the C report
>
> Limit items to all that start with a B then print all items that Start with
> a B , then Print the B report (I called it report2 for example).
>
> The "ProductID IN " & strSelected statement.is flawless. I worked it out.
>
> Happy 4th of July. Proud to be an American.
>
> "Damon Heron" <damon_327 RemoveThis @hotmail.com> wrote in message
> news:9dmdnXMuvpjXy_PVnZ2dnUVZ_rbinZ2d@comcast.com...
>> I would suggest you put a breakpoint at the beginning of your case stmt
> and
>> step thru the code, checking values-- if the code picks the correct report
>> name, then the problem is in your DoCmd.OpenReport stDocName,
> acViewPreview,
>> , "ProductID IN " & strSelected statement.
>>
>> Damon
>>
>>
>> "tony Jacobs" <tony RemoveThis @nospam.net> wrote in message
>> news:uw4i46e3IHA.5060@TK2MSFTNGP02.phx.gbl...
>>> Doug;
>>>
>>> No luck. No Errors but no results.
>>>
>>> Thanks for your time
>>>
>>>
>>> "Douglas J. Steele" <NOSPAM_djsteele RemoveThis @NOSPAM_canada.com> wrote in message
>>> news:u$XS#Re3IHA.1808@TK2MSFTNGP04.phx.gbl...
>>>> Given you were trying to use Column(1) in your first example, try
>>>>
>>>> Select Case Left(Me.Combo28.Column(1) & "", 1)
>>>>
>>>> --
>>>> Doug Steele, Microsoft Access MVP
>>>> http://I.Am/DougSteele
>>>> (no e-mails, please!)
>>>>
>>>>
>>>> "tony Jacobs" <tony RemoveThis @nospam.net> wrote in message
>>>> news:uvAAKLe3IHA.3348@TK2MSFTNGP03.phx.gbl...
>>>>> Sorry John; I did not work ! Any other ideas?
>>>>>
>>>>>
>>>>> "John Spencer" <spencer RemoveThis @chpdm.umbc> wrote in message
>>>>> news:uB2eF3d3IHA.4272@TK2MSFTNGP03.phx.gbl...
>>>>>> Select Case Left(Me.Combo28 & "",1)
>>>>>>
>>>>>> Case "B"
>>>>>> stDocName = "Productsreport1"
>>>>>>
>>>>>> Case "C"
>>>>>>
>>>>>> stDocName = "Productsreport2"
>>>>>>
>>>>>> Case Else
>>>>>>
>>>>>> stDocName = "Productsreport"
>>>>>>
>>>>>> End Select
>>>>>>
>>>>>> DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
>>>>>> strSelected
>>>>>>
>>>>>>
>>>>>> '====================================================
>>>>>> John Spencer
>>>>>> Access MVP 2002-2005, 2007-2008
>>>>>> The Hilltop Institute
>>>>>> University of Maryland Baltimore County
>>>>>> '====================================================
>>>>>>
>>>>>>
>>>>>> strive4peace wrote:
>>>>>>> hi Tony
>>>>>>>
>>>>>>> "Me![Combo28].column(1) = Like'B*'"
>>>>>>> --> left(Me![Combo28].column(1),1) = "B"
>>>>>>>
>>>>>>> "Me![Combo28].column(1) = 'Like'C*''"
>>>>>>> --> left(Me![Combo28].column(1),1) = "C"
>>>>>>>
>>>>>>>
>>>>>>> Warm Regards,
>>>>>>> Crystal
>>>>>>>
>>>>>>> remote programming and training
>>>>>>>
>>>>>>> Access Basics
>>>>>>> 8-part free tutorial that covers essentials in Access
>>>>>>> http://www.AccessMVP.com/strive4peace
>>>>>>>
>>>>>>> *
>>>>>>> (: have an awesome day Smile

>>>>>>> *
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> tony Jacobs wrote:
>>>>>>>> I am having trouble with this code can anyone help.
>>>>>>>>
>>>>>>>> I have the follwing case statement that checks the value of the
>>> combo
>>>>>>>> box,
>>>>>>>> and based on that it will select which report to print, but I can
>>> not
>>>>>>>> get it
>>>>>>>> to work
>>>>>>>>
>>>>>>>> Select Case ReportPrint
>>>>>>>>
>>>>>>>> Case "Me![Combo28].column(1) = Like'B*'"
>>>>>>>> stDocName = "Productsreport1"
>>>>>>>>
>>>>>>>> Case "Me![Combo28].column(1) = 'Like'C*''"
>>>>>>>>
>>>>>>>> stDocName = "Productsreport2"
>>>>>>>>
>>>>>>>> Case Else
>>>>>>>>
>>>>>>>> stDocName = "Productsreport"
>>>>>>>>
>>>>>>>> End Select
>>>>>>>> DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
>>>>>>>> strSelected
>>>>>>>>
>>>>>>>>
>>>>>>>> Thanks in advance
>>>>>>>>
>>>>>>>>
>>>>>
>>>>
>>>
>>
>
>

External Since: Jul 06, 2007 Posts: 68

Before the Select Case statement, put:

debug.print Left(Me.Combo28.column(1) & "",1)

if the correct answer appears in the immediate window, then you might try:
dim myrpt as variant
myrpt= Left(Me.Combo28.Column(1) & "",1)
Select Case myrpt
Case "B"
............
Case "C"
etc.

There is no way the correct report name would not be assigned to stDocName
in this case.

Damon

"tony Jacobs" <tony.DeleteThis@nospam.net> wrote in message
news:eBGBGrf3IHA.2580@TK2MSFTNGP06.phx.gbl...
> No Damon;
>
> The problem is that the same report opens in all cases. Which means that
> the
> case statement is not picking up the value of the combo box.
> The open report works perfectly and it tells me which records in the list
> box it is about to print.
>
> Now what I am trying to do is limit the list box to all items that start
> with a C, then print the C report
>
> Limit items to all that start with a B then print all items that Start
> with
> a B , then Print the B report (I called it report2 for example).
>
> The "ProductID IN " & strSelected statement.is flawless. I worked it out.
>
> Happy 4th of July. Proud to be an American.
>
> "Damon Heron" <damon_327.DeleteThis@hotmail.com> wrote in message
> news:9dmdnXMuvpjXy_PVnZ2dnUVZ_rbinZ2d@comcast.com...
>> I would suggest you put a breakpoint at the beginning of your case stmt
> and
>> step thru the code, checking values-- if the code picks the correct
>> report
>> name, then the problem is in your DoCmd.OpenReport stDocName,
> acViewPreview,
>> , "ProductID IN " & strSelected statement.
>>
>> Damon
>>
>>
>> "tony Jacobs" <tony.DeleteThis@nospam.net> wrote in message
>> news:uw4i46e3IHA.5060@TK2MSFTNGP02.phx.gbl...
>> > Doug;
>> >
>> > No luck. No Errors but no results.
>> >
>> > Thanks for your time
>> >
>> >
>> > "Douglas J. Steele" <NOSPAM_djsteele.DeleteThis@NOSPAM_canada.com> wrote in
>> > message
>> > news:u$XS#Re3IHA.1808@TK2MSFTNGP04.phx.gbl...
>> >> Given you were trying to use Column(1) in your first example, try
>> >>
>> >> Select Case Left(Me.Combo28.Column(1) & "", 1)
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no e-mails, please!)
>> >>
>> >>
>> >> "tony Jacobs" <tony.DeleteThis@nospam.net> wrote in message
>> >> news:uvAAKLe3IHA.3348@TK2MSFTNGP03.phx.gbl...
>> >> > Sorry John; I did not work ! Any other ideas?
>> >> >
>> >> >
>> >> > "John Spencer" <spencer.DeleteThis@chpdm.umbc> wrote in message
>> >> > news:uB2eF3d3IHA.4272@TK2MSFTNGP03.phx.gbl...
>> >> >> Select Case Left(Me.Combo28 & "",1)
>> >> >>
>> >> >> Case "B"
>> >> >> stDocName = "Productsreport1"
>> >> >>
>> >> >> Case "C"
>> >> >>
>> >> >> stDocName = "Productsreport2"
>> >> >>
>> >> >> Case Else
>> >> >>
>> >> >> stDocName = "Productsreport"
>> >> >>
>> >> >> End Select
>> >> >>
>> >> >> DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
>> >> >> strSelected
>> >> >>
>> >> >>
>> >> >> '====================================================
>> >> >> John Spencer
>> >> >> Access MVP 2002-2005, 2007-2008
>> >> >> The Hilltop Institute
>> >> >> University of Maryland Baltimore County
>> >> >> '====================================================
>> >> >>
>> >> >>
>> >> >> strive4peace wrote:
>> >> >> > hi Tony
>> >> >> >
>> >> >> > "Me![Combo28].column(1) = Like'B*'"
>> >> >> > --> left(Me![Combo28].column(1),1) = "B"
>> >> >> >
>> >> >> > "Me![Combo28].column(1) = 'Like'C*''"
>> >> >> > --> left(Me![Combo28].column(1),1) = "C"
>> >> >> >
>> >> >> >
>> >> >> > Warm Regards,
>> >> >> > Crystal
>> >> >> >
>> >> >> > remote programming and training
>> >> >> >
>> >> >> > Access Basics
>> >> >> > 8-part free tutorial that covers essentials in Access
>> >> >> > http://www.AccessMVP.com/strive4peace
>> >> >> >
>> >> >> > *
>> >> >> > (: have an awesome day Smile

>> >> >> > *
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > tony Jacobs wrote:
>> >> >> >> I am having trouble with this code can anyone help.
>> >> >> >>
>> >> >> >> I have the follwing case statement that checks the value of the
>> > combo
>> >> >> >> box,
>> >> >> >> and based on that it will select which report to print, but I
>> >> >> >> can
>> > not
>> >> >> >> get it
>> >> >> >> to work
>> >> >> >>
>> >> >> >> Select Case ReportPrint
>> >> >> >>
>> >> >> >> Case "Me![Combo28].column(1) = Like'B*'"
>> >> >> >> stDocName = "Productsreport1"
>> >> >> >>
>> >> >> >> Case "Me![Combo28].column(1) = 'Like'C*''"
>> >> >> >>
>> >> >> >> stDocName = "Productsreport2"
>> >> >> >>
>> >> >> >> Case Else
>> >> >> >>
>> >> >> >> stDocName = "Productsreport"
>> >> >> >>
>> >> >> >> End Select
>> >> >> >> DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN "
>> >> >> >> &
>> >> >> >> strSelected
>> >> >> >>
>> >> >> >>
>> >> >> >> Thanks in advance
>> >> >> >>
>> >> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>