Select Case Help

OldCityCat · August 10th, 2010, 09:42 AM

Here's my challenge, I'v been tasked with simplify the running of reports for my executive staff.
Currently we have around 40 stored reports, reporting on 3 years of data.
My goal is to avoid creating duplicate queries & reports and entering the start and ending every time a report is run.
Being this is the only criteria that changes, I thought about using Option Group to select a predefined date range, that would reference the variable "srtCriteria" that selects the correct date range.
So all the exec has to do is select the year and click a button with the report he needs.

Here's what I have :

Code:

Private Sub WhatYear()
Dim strCriteria As String
Select Case Me.Seasons
Case 2009
    strCriteria = "[ExpDate] Between #09/01/2009# and #09/01/2010#"
Case 2010
    strCriteria = "[ExpDate] Between #09/01/2010# and #09/01/2011#"
Case 2011
    strCriteria = "[ExpDate] Between #09/01/2011# and #09/01/2012#"
End Select
End Sub

This works fine as long as I add the code to each report button.
I know there's a way to use this code only once.
I tried making it a public sub but that didn't work either
What I'm missing?

Thanks for any and all suggestions
Gil

JP · August 10th, 2010, 11:27 AM

You can write one function or procedure that can be called from anywhere. It only has to appear once.

But if you have multiple buttons from which to call the procedure, the code to do so must be added to each button's Click Event. There is no way around that.

For example, I want to call WhatYear() from three buttons on a form. I have to add "Call WhatYear" to each button's Click Event in order for each button to use that code.

HTH,
JP

OldCityCat · August 10th, 2010, 02:17 PM

Thanks JP

I have added WhatYear or Call WhatYear to the Click Event but nether worked,
but the PrintQuestion does.

[Code]
Private Sub cmdAsiaBillOfLadingCount_Click()
On Error GoTo cmdAsiaBillOfLadingCount_Click_Err

WhatYear

DoCmd.OpenReport "rptBillOfLadingCount_Asia_Tbl", acViewPreview, "", "", acNormal

PrintQuestion

cmdAsiaBillOfLadingCount_Click_Exit:
Exit Sub

cmdAsiaBillOfLadingCount_Click_Err:
MsgBox Error$
Resume cmdAsiaBillOfLadingCount_Click_Exit

End Sub

Private Sub PrintQuestion()
Me.Visible = False
If MsgBox("Do you want to print report?", vbYesNo) = vbYes Then
On Error Resume Next
DoCmd.RunCommand acCmdPrint
ElseIf vbNo Then
On Error Resume Next
DoCmd.RunCommand acCmdClose
End If
DoCmd.RunCommand acCmdClose
Me.Visible = True

End Sub

[\Code]

Any ideas?
Gil

JP · August 10th, 2010, 02:59 PM

What do you mean "nether worked"?

FYI, the WhatYear procedure doesn't appear to do anything meaningful. All it does is check the value of Me.Seasons, then set a string variable accordingly, then exit.

OldCityCat · August 10th, 2010, 03:23 PM

I tried using "Call WhatYear" and just "WhatYear", nether worked

Code:

WhatYear

DoCmd.OpenReport "rptBillOfLadingCount_Asia_Tbl", acViewPreview, , strCriteria

WhatYear should return one of the following depending on the option selected.

Case 2009
strCriteria = "[ExpDate] Between #09/01/2009# and #09/01/2010#"
Case 2010
strCriteria = "[ExpDate] Between #09/01/2010# and #09/01/2011#"
Case 2011
strCriteria = "[ExpDate] Between #09/01/2011# and #09/01/2012#"

for use in the DoCmd.OpenReport.

As I mention the code work if it included in the Click Event
I did notice that the "srtCriteria" was missing from my test procedure.
It still didn't work after I corrected it.
I hope I'm making sense.

Gil

JP · August 10th, 2010, 03:40 PM

OK. Your Click Event code didn't include "strCriteria" until now.

The problem is that the strCriteria variable is locally scoped to the WhatYear procedure. After the WhatYear procedure ends, strCriteria is lost because it's scope is limited to the WhatYear procedure. You either need to convert WhatYear to a function that passes the value of strCriteria to the calling procedure, or make strCriteria a module-level variable that can be read by any procedure in the module (I recommend the former).

OldCityCat · August 10th, 2010, 03:58 PM

Sorry,
I should have mentioned I tried making it a function,
but because I'm still learning, I'm not sure what I doing.
I have looked at a few examples and tried them but I'm still missing something.
Weather it in the conversion or how I'm call it I'm not sure.

Thanks again for all your insight
Gil

JP · August 11th, 2010, 12:47 PM

Here's what I would do.

First, alter WhatYear so it returns a value to the calling procedure:

Code:

Function WhatYear() As String
Dim strCriteria As String
  Select Case Me.Seasons
    Case 2009
      strCriteria = "[ExpDate] Between #09/01/2009# and #09/01/2010#"
    Case 2010
      strCriteria = "[ExpDate] Between #09/01/2010# and #09/01/2011#"
    Case 2011
      strCriteria = "[ExpDate] Between #09/01/2011# and #09/01/2012#"
  End Select
 
  WhatYear = strCriteria
End Function

If you want a procedure to return a value, it should be declared as a Function, with an explicit return type value (i.e. As String) (technically I don't think it has to, but it should). Then simply assign the return value using the name of the function (i.e. WhatYear = strCriteria).

Now at the top of your Click Event, create a new variable to hold the return value of WhatYear:

Code:

Dim criteria As String

Now instead of calling WhatYear, return its value to the new variable:

Code:

criteria = WhatYear

and amend the OpenReport method call to use the expression returned by the WhatYear Function:

Code:

DoCmd.OpenReport "rptBillOfLadingCount_Asia_Tbl", acViewPreview, , criteria

You could also use the amended WhatYear function directly in the OpenReport call:

Code:

DoCmd.OpenReport "rptBillOfLadingCount_Asia_Tbl", acViewPreview, , WhatYear

HTH

OldCityCat · August 12th, 2010, 07:27 AM

JP,
Simply Elegant !

Thank your for taking the time to share your knowledge.

Gil