Reuseable Where Condition (1 Viewer)

VSolano

Registered User.
Local time
Today, 03:03
Joined
Feb 21, 2017
Messages
85
I am looking for some guide from this great community.
I am creating a filter form to run different reports based on the criteria selected on the form. The Where condition on the onclick event is working fine. The issue that I am having is when I call the runreport procedure, the value created from the onclick disappear. My lack of knowledge do not allow me to determine what is wrong with passing the where condition to the next procedure.

I just need to know why the value disappear when I can the runreport

Code:
Option Compare Database
Option Explicit
Dim db As Database
Dim rs As Recordset
Dim CrWord As String
Public StrWhere As String
Public STRSQL As String
Dim StrReport As String
Dim VarianteValue As Variant
Dim ActionFlag As Boolean
 
Function EmptyTbl()
On Error Resume Next
Dim SQL As String
    SQL = "DELETE tbbillingReport.* FROM tbbillingReport;"
 
'    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
'    DoCmd.SetWarnings True
    
End Function
Private Sub brnFilter_Click()

On Error GoTo ProblemHandle
'
'Dim db As Database
'Dim rs As Recordset
Dim I As Integer
Dim STRSQL As String
Dim StrWhere As String
Dim StrIn As String
Dim BEntity As Long
Dim BCoCode As Long
Dim STREntity As String
Dim IngLen As Integer
Dim StartDate As Date
Dim enddate As Date
Dim IngView As Long
Dim Carrier As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tbbilling")
Const StrJetDate = "\#mm\/dd\/yyyy\#"
StrReport = "FilteredReport"
IngView = acViewPreview
StartDate = Nz(Me.StDate)
enddate = Nz(Me.enddate)

BEntity = Nz(Me.cboentity)
BCoCode = Nz(Me.cboCompany)
Carrier = Nz(Me.cbocarrier)
CrWord = "WHERE"
 

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tbbilling")

    'Build the IN string by looping through the listbox
'    For I = 0 To Me.ListBox.ListCount - 1
'
'        StrIn = StrIn & "" & Me.ListBox.Column(0, I) & ","
'
'
'
'        Debug.Print StrIn
'
'
'    Next I
 

 For Each VarianteValue In Me.ListBox.ItemsSelected
             StrWhere = StrWhere & ListBox.ItemData(VarianteValue) & ","
             StrIn = StrIn & "" & Me.ListBox.Column(0, VarianteValue) & ","
    Next VarianteValue

          StrWhere = Left(StrWhere, Len(StrWhere) - 1)

'        Debug.Print StrWhere

     'Create the WHERE string, and strip off the last comma of the IN string
     If (Me.ListBox.ItemsSelected.Count = 0) Then
            StrWhere = " " And " "
          Else
            StrWhere = " [tbbilling.benefitsID] In " & _
               "(" & Left(StrIn, Len(StrIn) - 1) & ") AND "
               ActionFlag = True
     End If
'       Debug.Print StrWhere

     If (BEntity = 0) Then
                 StrWhere = " " And " "
     Else
            StrWhere = StrWhere & " [tbbilling.entityid] = " & BEntity & " AND "
            ActionFlag = True

     End If

      If (BCoCode = 0) Then
            StrWhere = " " And " "
      Else
            StrWhere = StrWhere & " [tbbilling.companyid] = " & BCoCode & " AND "
            ActionFlag = True

     End If

      If (Carrier = 0) Then
            StrWhere = " " And " "
      Else
            StrWhere = StrWhere & " [tbbilling.PlanCarrier] = " & Carrier & " AND "
            ActionFlag = True
        Debug.Print StrWhere
     End If

    If StartDate = 0 Then

        StrWhere = " " And " "
        Else

        StrWhere = StrWhere & " ([tbbilling.invoicedate] >= " & Format(StartDate, StrJetDate) & ")   AND "
        ActionFlag = True
    End If

    If enddate = 0 Then
        StrWhere = " " And " "

        Else
        StrWhere = StrWhere & " ([tbbilling.invoicedate] < " & Format(enddate + 1, StrJetDate) & ")   AND "
        ActionFlag = True
 

    End If

    
    
    
'    Debug.Print StrWhere
    
    
'     this code is used to trim the last AND on the Where construction
    IngLen = Len(StrWhere) - 4
        If IngLen <= 0 Then
                If MsgBox("Are you sure you want to print the entire billing without any filter ? ", vbYesNo) = vbNo Then
                    Me.ListBox.SetFocus
'                    For Each VarianteValue In Me.ListBox.ItemsSelected
'
'                    Me.ListBox.Selected(VarianteValue) = False
'                    Next VarianteValue
                    Exit Sub
                End If

        Else
                StrWhere = Left$(StrWhere, IngLen)
'                Debug.Print StrWhere
        End If
 
'     Debug.Print StrWhere
     
     DoCmd.SetWarnings False
     Call EmptyTbl
     DoCmd.SetWarnings True
     
     STRSQL = "INSERT INTO tbbillingReport SELECT tbbilling.* FROM tbbilling "
   
'        If ActionFlag = False Then
'
'            STRSQL = STRSQL
'
'        Else
'
'            STRSQL = STRSQL & CrWord & StrWhere
'
'        End If
'
'        DoCmd.SetWarnings False
'        DoCmd.RunSQL STRSQL
'        DoCmd.SetWarnings True
'
'        Debug.Print STRSQL
'        DoCmd.OpenReport StrReport, acViewPreview
    
    Call RunReport
    
    
'
Exit Sub
ProblemHandle:
Select Case Err.Number
    
        Case Is = 5
        
                Resume Next
                 
        Case Is = 13
                Resume Next
                
        Case Is = 2467
                Err.Clear
            
        Case Else
                MsgBox ("Please Contact the administrator with ERROR # " & Err.Number & " " & Err.Description & "!")
                
                Resume Next
                
        End Select
        
        Resume Next
 

        
        
   
    
 
Set db = Nothing
rs.Close
 
        
End Sub
Sub RunReport()
    
    Debug.Print StrWhere
    
    Debug.Print STRSQL
    
        
    Select Case Me.OpenArgs
        Case 1
            ColumnarReport
        Case 2
            DoCmd.SetWarnings False
            DoCmd.RunSQL STRSQL
            Debug.Print STRSQL
            DoCmd.SetWarnings True
            DoCmd.Close
            DoCmd.OpenReport StrReport, acViewPreview
        Case Else
           
    End Select
    
    
'    Clear listbox selection after running query
    For Each VarianteValue In Me.ListBox.ItemsSelected
    
        Me.ListBox.Selected(VarianteValue) = False
    Next VarianteValue

End Sub
 

plog

Banishment Pending
Local time
Today, 02:03
Joined
May 11, 2011
Messages
11,613
My lack of knowledge do not allow me to determine what is wrong with passing the where condition to the next procedure.

I think your problem is scope (https://en.wikipedia.org/wiki/Scope_(computer_science)). Give that a read while I drone on about another term.

"Passing" is a technical term, which means it has a specific, objective meaning. In this context "passing" means you are sending the value to the next procedure. None of your procedures accept passed arguments and no where do you pass anything value.

Check out the Month() function:

https://www.techonthenet.com/access/functions/date/month.php

It determines the month number of a date that you pass it. This is an example of how you call it and pass it a value:

Month("1/1/2020")

The value "1/1/2020" is passed to the Month() functon. No where in your data do you call a procedure and include anything in the call (between its parenthesis). None of your procedures require anything to be passed to them.

Back to scope--it seems you want to declare a variable, assign it a value and have it magically show up wherever you need it. Computers don't work that way. Variables exist in very narrowly defined areas (aka scope). You can't declare a variable in one function and have it be available in another. You must either "pass" it as described above, or declare the variable outside of all functions so that all functions have access to it (this called a global variable and is not a best practice).
 

plog

Banishment Pending
Local time
Today, 02:03
Joined
May 11, 2011
Messages
11,613
Ok, so I just gave your code a better look and you did use global variables (defined outside of all functions) but then you used local variables (declared inside a function) with the same names. When you do this you have created 2 variables with the same name and the local one will be used by default.

Here's what you did (psuedo code, probably won't actually work):

Code:
Dim var As String

var = "global"

Function A()
  
  Dim var As String
  var="local"

  print var  

End Function


Function B()
  print var
End Function

When the A function is run it will print "local", when B is run it will print "global". That is what you have done with all those variables declared outside any functions and with counterparts inside functions.

I would recommend not using globals and instead pass values as I described in my initial post. If you do use globals I suggest you name them as such so you are always aware of their scope. I would rename all those variables outside of functions like so:

Code:
...
Dim db As Database
Dim rs As Recordset
Dim CrWord As String
...

--Above should be changed to the below--

Dim GLOBAL_DB AS Database
Dim GLOBAL_RS AS Recordset
Dim GLOBAL_CRWORD As String
...
 

VSolano

Registered User.
Local time
Today, 03:03
Joined
Feb 21, 2017
Messages
85
I just need a way to pass value from one procedure to another or just use the result of the a prior procedure into another
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 07:03
Joined
Jul 9, 2003
Messages
16,245
I just need a way to pass value from one procedure to another or just use the result of the a prior procedure into another
You response suggests that you haven't understood Plogs answers.

Please read Plogs excellent advice again. If you don't understand the advice, then explain the bits you don't understand. If you don't understand any of it say so.

Sent from Newbury UK
 

VSolano

Registered User.
Local time
Today, 03:03
Joined
Feb 21, 2017
Messages
85
I am not following the original advice.

The variable are declared outside de procedure
 

plog

Banishment Pending
Local time
Today, 02:03
Joined
May 11, 2011
Messages
11,613
The variable are declared outside de procedure

They are also declared inside the procedure which is the issue. Read my second post.
 

Users who are viewing this thread

Top Bottom