Case statement Problem

T

tony Jacobs

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
 
S

strive4peace

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 :)
*
 
J

John Spencer

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
'====================================================

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 :)
*




tony said:
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
Click to expand...
Click to expand...
 
T

tony Jacobs

Sorry John; I did not work ! Any other ideas?


John Spencer said:
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
'====================================================

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 :)
*




tony said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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

(no e-mails, please!)


tony Jacobs said:
Sorry John; I did not work ! Any other ideas?


John Spencer said:
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
'====================================================

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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

Doug;

No luck. No Errors but no results.

Thanks for your time


Douglas J. Steele said:
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

(no e-mails, please!)


tony Jacobs said:
Sorry John; I did not work ! Any other ideas?


John Spencer said:
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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

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 said:
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 said:
Doug;

No luck. No Errors but no results.

Thanks for your time


Douglas J. Steele said:
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

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 :)
*




tony said:
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 said:
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 said:
Doug;

No luck. No Errors but no results.

Thanks for your time


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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If the Case statement isn't working, then stDocName shouldn't have a value
and the OpenReport command should fail.

Put a breakpoint in the code and make sure stDocName is, in fact, being set
correctly.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tony Jacobs said:
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 said:
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 said:
Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

another thing to keep in mind is that column indexes start at zero ...
so Column(1) is the second column

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 :)
*




Damon said:
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 said:
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 said:
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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

Yes I did, I always do. Thanks though ;-)

strive4peace said:
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 :)
*




tony said:
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 said:
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


Doug;

No luck. No Errors but no results.

Thanks for your time


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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

Compile Error
Expected Expression if I put ? Debug.Print Left(Me.Combo28.Column(1) &
"", 1) in thr immediate window

Also I get an error that the Value of the Combo Box is not set yet.or not
calculated. I used to be able to use the immediate window very successfully.
Please tell me if I am doing something wrong.

Thanks



Damon Heron said:
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 said:
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 said:
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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

Crystal

Yes ;Column 1 is the second column which is column(1), which contains the
value that shows up in the combobox.and is the one I need.

Thanks

strive4peace said:
another thing to keep in mind is that column indexes start at zero ...
so Column(1) is the second column

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 :)
*




Damon said:
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 said:
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.

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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

Okay a quick update: I put a message box that printed the value of the Combo
box, and the left statement returns the letter "L" only

that's why the report selection is bypassed to the Case Else Statement .

Please note that the coulmn(1) in the combobox is as follows

Product Name
Like 'C*'
Like 'P*'
Like 'B*'
Like '*'
Like 'Z*'
Like"*"
Like "[BC]*"


I am thinking of trying the combobox without the Left function.


tony Jacobs said:
Compile Error
Expected Expression if I put ? Debug.Print Left(Me.Combo28.Column(1) &
"", 1) in thr immediate window

Also I get an error that the Value of the Combo Box is not set yet.or not
calculated. I used to be able to use the immediate window very successfully.
Please tell me if I am doing something wrong.

Thanks



Damon Heron said:
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
Click to expand...
"
&
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 :)
*




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
Click to expand...
Click to expand...
IN
"
Click to expand...
Click to expand...
 
T

tony Jacobs

I tried the code without the left function, and my debugging message box
returns what is actually in the combobox column(1) which is any of the below
criteria. here is the code:
.............................................................................
.............................................................................
..............

Debug.Print Me.Combo28.Column(1)

' Select Case Left(Me.Combo28 & "", 1)
' Select Case Left(Me![Combo28].Column(1), 1)

Dim myrpt As Variant
myrpt = Me.Combo28.Column(1)

Select Case myrpt

Case "Like'B*'"

stDocName = "Productsreport1"

Case "Like'C*'"

' Case Left(Me![Combo28].Column(1), 1) = "C"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select

MsgBox ("The ComboBox Value is " & Me.Combo28.Column(1)), vbInformation,
" OOps "
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
strSelected

EndIT:

End Sub
.............................................................................
.............................................................................
....................

the immediate window returns the following or has returned the following ( I
figured out how to work it now)
(below using the left statement)
L
L
L

using only the (Me.Combo28.Column(1))

Like 'C*'
Like 'C*'
Like 'C*'
Like 'P*'
Like 'B*'
.............................................................................
.............................................................................
.....................

Still the report printed is the last one which is in the case else
condition.








tony Jacobs said:
Okay a quick update: I put a message box that printed the value of the Combo
box, and the left statement returns the letter "L" only

that's why the report selection is bypassed to the Case Else Statement .

Please note that the coulmn(1) in the combobox is as follows

Column(1) is:
Like 'C*'
Like 'P*'
Like 'B*'
Like '*'
Like 'Z*'
Like"*"
Like "[BC]*"


I am thinking of trying the combobox without the Left function.


tony Jacobs said:
Compile Error
Expected Expression if I put ? Debug.Print Left(Me.Combo28.Column(1) &
"", 1) in thr immediate window

Also I get an error that the Value of the Combo Box is not set yet.or not
calculated. I used to be able to use the immediate window very successfully.
Please tell me if I am doing something wrong.

Thanks
Click to expand...
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 :)
*




tony Jacobs wrote:
I am having trouble with this code can anyone help.

I have the follwing case statement that checks the value
Click to expand...
of
the
combo
box,
and based on that it will select which report to print,
Click to expand...
Click to expand...
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
Click to expand...
IN
"
&
strSelected


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
T

tony Jacobs

Damon;
I tried your code and I added 3 message boxes at the end of the code to see
the value of the stDocName and it is always the report name in the Case else
statement.

It seems that we are close, but the conditional reports are not being picked
up .


Damon Heron said:
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 said:
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 said:
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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

Hi Tony,

this is not right:
Case "Like'B*'"

firstly, you would need a space after the word Like; second, you would
not include Like in the quotes (it is, essentially, the operator) -- and
Access does not interpret what is in quotes as part of code syntax ...
but anyway, you cannot use 'Like' in VBA code (works fine in SQL -- ie:
queries). If you want to test the first character for "B", you need to
do what Doug suggested:

Select Case Left(Me.Combo28.Column(1) & "", 1)
Case "B"



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 :)
*




tony said:
I tried the code without the left function, and my debugging message box
returns what is actually in the combobox column(1) which is any of the below
criteria. here is the code:
............................................................................
............................................................................
.............

Debug.Print Me.Combo28.Column(1)

' Select Case Left(Me.Combo28 & "", 1)
' Select Case Left(Me![Combo28].Column(1), 1)

Dim myrpt As Variant
myrpt = Me.Combo28.Column(1)

Select Case myrpt

Case "Like'B*'"

stDocName = "Productsreport1"

Case "Like'C*'"

' Case Left(Me![Combo28].Column(1), 1) = "C"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select

MsgBox ("The ComboBox Value is " & Me.Combo28.Column(1)), vbInformation,
" OOps "
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
strSelected

EndIT:

End Sub
............................................................................
............................................................................
...................

the immediate window returns the following or has returned the following ( I
figured out how to work it now)
(below using the left statement)
L
L
L

using only the (Me.Combo28.Column(1))

Like 'C*'
Like 'C*'
Like 'C*'
Like 'P*'
Like 'B*'
............................................................................
............................................................................
....................

Still the report printed is the last one which is in the case else
condition.








tony Jacobs said:
Okay a quick update: I put a message box that printed the value of the Combo
box, and the left statement returns the letter "L" only

that's why the report selection is bypassed to the Case Else Statement .

Please note that the coulmn(1) in the combobox is as follows

Column(1) is:
Like 'C*'
Like 'P*'
Like 'B*'
Like '*'
Like 'Z*'
Like"*"
Like "[BC]*"


I am thinking of trying the combobox without the Left function.


tony Jacobs said:
Compile Error
Expected Expression if I put ? Debug.Print Left(Me.Combo28.Column(1) &
"", 1) in thr immediate window

Also I get an error that the Value of the Combo Box is not set yet.or not
calculated. I used to be able to use the immediate window very successfully.
Please tell me if I am doing something wrong.

Thanks



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



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.

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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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
Click to expand...
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 :)
*




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,
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
S

strive4peace

what is the SQL for the RowSource of your combobox? If it is based on a
query, then, from the query design, choose --> View, SQL

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 :)
*




tony said:
Okay a quick update: I put a message box that printed the value of the Combo
box, and the left statement returns the letter "L" only

that's why the report selection is bypassed to the Case Else Statement .

Please note that the coulmn(1) in the combobox is as follows

Product Name
Like 'C*'
Like 'P*'
Like 'B*'
Like '*'
Like 'Z*'
Like"*"
Like "[BC]*"


I am thinking of trying the combobox without the Left function.


tony Jacobs said:
Compile Error
Expected Expression if I put ? Debug.Print Left(Me.Combo28.Column(1) &
"", 1) in thr immediate window

Also I get an error that the Value of the Combo Box is not set yet.or not
calculated. I used to be able to use the immediate window very successfully.
Please tell me if I am doing something wrong.

Thanks



Damon Heron said:
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



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.

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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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
Click to expand...
"
&
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 :)
*




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
Click to expand...
IN
"
&
strSelected


Thanks in advance
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The problem would appear to be that the value in Me.Combo28.Column(1) is not
what you're expecting it to be. In other words, whatever value is there
never starts with either a B or a C.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tony Jacobs said:
Damon;
I tried your code and I added 3 message boxes at the end of the code to
see
the value of the stDocName and it is always the report name in the Case
else
statement.

It seems that we are close, but the conditional reports are not being
picked
up .


Damon Heron said:
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 said:
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.

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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Crystal,

I believe you can use Like in VBA. I do so on occasion.

Perhaps you meant that you can't use Like in Case statements - which I
believe is correct.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi Tony,

this is not right:
Case "Like'B*'"

firstly, you would need a space after the word Like; second, you would
not include Like in the quotes (it is, essentially, the operator) -- and
Access does not interpret what is in quotes as part of code syntax ...
but anyway, you cannot use 'Like' in VBA code (works fine in SQL -- ie:
queries). If you want to test the first character for "B", you need to
do what Doug suggested:

Select Case Left(Me.Combo28.Column(1) & "", 1)
Case "B"



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 :)
*




tony said:
I tried the code without the left function, and my debugging message box
returns what is actually in the combobox column(1) which is any of the
below
criteria. here is the code:
............................................................................

............................................................................

.............

Debug.Print Me.Combo28.Column(1)

' Select Case Left(Me.Combo28 & "", 1)
' Select Case Left(Me![Combo28].Column(1), 1)

Dim myrpt As Variant
myrpt = Me.Combo28.Column(1)

Select Case myrpt

Case "Like'B*'"

stDocName = "Productsreport1"

Case "Like'C*'"

' Case Left(Me![Combo28].Column(1), 1) = "C"

stDocName = "Productsreport2"

Case Else

stDocName = "Productsreport"

End Select

MsgBox ("The ComboBox Value is " & Me.Combo28.Column(1)),
vbInformation,
" OOps "
DoCmd.OpenReport stDocName, acViewPreview, , "ProductID IN " &
strSelected

EndIT:

End Sub
............................................................................

............................................................................

...................

the immediate window returns the following or has returned the
following ( I
figured out how to work it now)
(below using the left statement)
L
L
L

using only the (Me.Combo28.Column(1))

Like 'C*'
Like 'C*'
Like 'C*'
Like 'P*'
Like 'B*'
............................................................................

............................................................................

....................

Still the report printed is the last one which is in the case else
condition.








tony Jacobs said:
Okay a quick update: I put a message box that printed the value of the Combo
box, and the left statement returns the letter "L" only

that's why the report selection is bypassed to the Case Else Statement .

Please note that the coulmn(1) in the combobox is as follows

Column(1) is:
Like 'C*'
Like 'P*'
Like 'B*'
Like '*'
Like 'Z*'
Like"*"
Like "[BC]*"


I am thinking of trying the combobox without the Left function.


Compile Error
Expected Expression if I put ? Debug.Print Left(Me.Combo28.Column(1) &
"", 1) in thr immediate window

Also I get an error that the Value of the Combo Box is not set yet.or not
calculated. I used to be able to use the immediate window very
successfully.
Please tell me if I am doing something wrong.

Thanks



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



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.

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


Doug;

No luck. No Errors but no results.

Thanks for your time


message
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

(no e-mails, please!)


Sorry John; I did not work ! Any other ideas?


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 :)
*




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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with this code to set the List Row Source 10
statement if vs select case 1
Access Choosing Printer VBA Access 0
Problem with If statement and combo box value 2
Exporting a Report to Excel 2
View criteria used to filter a report 2
Like "MU*" OR Like "MS*" 14
Filter Report using Option Group 6

Top