Carry a String from one procedure to another

[Sandra Walsh]

Hello -

I have a form called f_ReportsDashbo ard with several dropdowns that I use to generate a string of criteria values for reports.

Using the code below, I can succesfully generate the strCriteria in a stand-alone procedure.

Private Sub ReportFilters()

' Generate the String that will filter the reports
Dim strCriteria As String
First Field
   If IsNull(Me.RecStatus_DD.Value) Then
       strCriteria = strCriteria & " AND [RecordStatus] Like '*'"
   Else
       strCriteria = strCriteria & " AND [RecordStatus] ='" & Me.RecStatus_DD.Value & "'"
   End If
'
' several more additions to the strCriteria here ....
'
'Last Field
   If IsNull(Me.Approver_DD.Value) Then
       strCriteria = strCriteria & " AND [ApproverName] Like '*'"
   Else
       strCriteria = strCriteria & " AND [ApproverName] ='" & Me.Approver_DD.Value & "'"
   End If
Debug.Print strCriteria
End Sub

The strCriteria for the above code prints correctly in the Immediate window.

I would like to be able to call Sub ReportFilters() in the OnClick event of the buttons on f_ReportsDashbo ard so I can generate filtered reports.

I currently have the following code for the OnClick event of a button:

Private Sub Report_ByPriority_Btn_Click()

Call ReportFilters
Debug.Print [B]strCriteria[/B]
DoCmd.OpenReport "r_MyReport", acViewPreview, , [B]strCriteria[/B], acWindowNormal
End Sub

The strCriteria does not print in the Immediate window, so strCriteria is not being carried over to this procedure.

QUESTION:

How do I "save" the strCriteria from Sub ReportFilters() so that I can use it on my button codes?

Can I save strCriteria as a named filter somehow?

I can generate the string each time but I am hoping there is a more efficient way to do this.

Much appreciated, as always :)

Sandra

[zmbd]

I am guessing this code is in a standard module; thus,
Line 1
Change Private to Public
-z

[Sandra Walsh]

Thanks for the quick reply.

I tried changing to Public Sub ReportFilters() but I get a Run-time error '3219' Invalid Operation error on the second procedure.

Sandra

[zmbd]

Which line in the second procedure does this occur?
Does the strCriteria evaluate correctly in the Immediate window when called in the second procedure?

{{{EDIT}}}
Post the strCriteria as it evaluates to in the immediate window so we can disect it too ;-)

-z

[Sandra Walsh]

I think it is evaluating correctly, but only in the first procedure. I get no debug result at all in the second procedure.

In the first procedure, for only 2 selected criteria:

Target = Firm
Year = 2014

Debug.Print strCriteria generates the following in the Immediate Window:

[Target] = 'Firm' 
  AND [Type] Like '*' 
  AND [PersonName] Like '*' 
  AND [Office] Like '*' 
  AND [Year] ='2014' 
  AND [City] Like '*' 
  AND [StateProv] Like '*' 
  AND [Region] Like '*' 
  AND [Publisher] Like '*' 
  AND [Publication] Like '*' 
  AND [RecordStatus] Like '*' 
  AND [ApproverName] Like '*'

[zmbd]

Ok,
Very first thing...
Do not use "Type" or "Year" as field names... these are reserved words in SQL/VBA. Although this is not the issue now.. they may very well cause you issues later. Google VBA/SQL reserved words to get a list (or do a search here on bytes I've seen a link to those last month).

To the issue
The position where strCriteria is requires that the string be a valid "WHERE" sql clause.
So let's start with all of those 'Like" and Stars... you shouldn't need them.
So now we get down to [Target] and [Year]...

If both are text fields then you'll need something like:
((Target='Firm' ) AND (Z_Year='2014') )

-z

[Rabbit]

This is a matter of variable scope.

A variable declared within a method can not be seen or accessed outside of the method in which it is declared.

There are two solutions to this. One is to use a global variable. Two is to use a function to return the value. The rule of thumb is to avoid option one when possible. In your case, it is very much possible.

So what you want to do is to use option two.

Function GetCriteria() As String
   GetCriteria = "someField = 'some value'"
End Function

Private Sub someControl_someEvent()
   DoCmd.OpenReport "reportName", , , GetCriteria()
End Sub

[zmbd]

Sorry,
I was called away to the lab so I had to end my last post very short.

Thank you Rabbit for picking up the pieces.

What I had intended to say after the example string was that we needed to relocate the code from the standard module to the command button or change it to a function which could build the string and pass it back.

-z

[Sandra Walsh]

Thanks to you both! This is solved with a combination of both your suggestions :-)

Here is the full code for the Function:

Function ReportFilters() As String

'Generate the String that will filter the reports
'First Field
   If IsNull(Me.AccoladeTarget_DD.Value) Then
       ReportFilters = "[AccoladeTarget] Like '*'"
   Else
       ReportFilters = "[AccoladeTarget] ='" & Me.AccoladeTarget_DD.Value & "'"
   End If

'Next Field
   If IsNull(Me.AccoladeType_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [AccoladeType] ='" & Me.AccoladeType_DD.Value & "'"
   End If
 
'Next Field
   If IsNull(Me.PersonName_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [PersonName] ='" & Me.PersonName_DD.Value & "'"
   End If
 
'Next Field
   If IsNull(Me.PersonOffice_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [Office] ='" & Me.PersonOffice_DD.Value & "'"
   End If
 
'Next Field - number format
   If IsNull(Me.Year_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [AccoladeYear] =" & Me.Year_DD.Value & ""
   End If
 
'Next Field
   If IsNull(Me.City_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [AccoladeCity] ='" & Me.City_DD.Value & "'"
   End If
   
'Next Field
   If IsNull(Me.ProvState_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [AccoladeStateProv] ='" & Me.ProvState_DD.Value & "'"
   End If
   
'Next Field
   If IsNull(Me.Region_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [AccoladeRegion] ='" & Me.Region_DD.Value & "'"
   End If
   
'Next Field
   If IsNull(Me.Publisher_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [PublisherName] ='" & Me.Publisher_DD.Value & "'"
   End If

'Next Field
   If IsNull(Me.Publication_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [PublicationName] ='" & Me.Publication_DD.Value & "'"
   End If

'Next Field
   If IsNull(Me.RecStatus_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [RecordStatus] ='" & Me.RecStatus_DD.Value & "'"
   End If
   
'Last Field
   If IsNull(Me.Approver_DD.Value) Then
       ReportFilters = ReportFilters
   Else
       ReportFilters = ReportFilters & " AND [ApproverName] ='" & Me.Approver_DD.Value & "'"
   End If

Debug.Print ReportFilters

End Function

I had to keep the " Like '*' " for the first field otherwise the resulting string would start with "AND" if Me.AccoladeTarg et_DD.Value was empty.

Here is the code for the command button:

Private Sub Report_ByPriority_Btn_Click()

DoCmd.OpenReport "r_ByPriority", acViewPreview, , ReportFilters, acWindowNormal

End Sub

Now how do I choose you BOTH as best answer?!!

Sandra

[zmbd]

Sandra,
I droped the ball last night... Rabbit deserves the credit.

I'm sure we can clean this code up a tad; however, that should be in a different thread.

Once again I have to run... busy busy...

-z

[Sandra Walsh]

A final addition:

The code to clear the filters is as follows:

Private Sub ClearFilters_Btn_Click()

'Clear form filter fields

   AccoladeTarget_DD = Null
   AccoladeType_DD = Null
   Me.PersonName_DD = Null
   Me.PersonOffice_DD = Null
   Me.Year_DD = Null
   Me.City_DD = Null
   Me.ProvState_DD = Null
   Me.Region_DD = Null
   Me.Publisher_DD = Null
   Me.Publication_DD = Null
   Me.Approver_DD = Null
   Me.RecStatus_DD = Null
   
End Sub

Using the following format does not work as the filter then looks for records where these fields are empty:

AccoladeTarget_DD = ""
AccoladeType_DD = ""