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