Run-Time error '3075' (1 Viewer)

Option Compare Database



Private Sub btnClear_Click()
    Dim intIndex As Integer
    
    ' Clear all search items
    Me.txtODFNumber = ""
    Me.txtUserName = ""
    Me.txtODFScanDate = ""
    Me.txtfollow_up = ""
    Me.cmbStatus = ""
    Me.cmbQueue = ""
    Me.txtDayMin = ""
    Me.txtDayMax = ""
    
    
        
   
End Sub

Private Sub btnSearch_Click()
    ' Update the record source
    Me.frmSubODFSearch.Form.RecordSource = "SELECT * FROM qryODFData " & BuildFilter
    
    ' Requery the subform
    Me.frmSubODFSearch.Requery
End Sub

Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varColor As Variant
    Dim varItem As Variant
  

    varWhere = Null  ' Main filter
  
    
    ' Check for ODF Number
    If Me.txtODFNumber > "" Then
        varWhere = varWhere & "[ODFNumber] LIKE """ & Me.txtODFNumber & "*"" AND "
    End If
    
    ' Check for UserName
    If Me.txtUserName > "" Then
        varWhere = varWhere & "[UserName] LIKE """ & Me.txtUserName & "*"" AND "
    End If
    
    ' Check for Date
    If Me.txtODFScanDate > "" Then
        varWhere = varWhere & "[ODFScanDate] LIKE """ & Me.txtODFScanDate & "*"" AND "
    End If
    
        ' Check for Date
    If Me.txtfollow_up > "" Then
        varWhere = varWhere & "[LastFollowup] LIKE """ & Me.txtfollow_up & "*"" AND "
    End If
    
    
    ' Check for Status
    If Me.cmbStatus > "" Then
        varWhere = varWhere & "[Status] LIKE """ & Me.cmbStatus & "*"" AND "
    End If
    
        ' Check for Queue
    If Me.cmbQueue > "" Then
        varWhere = varWhere & "[Queue] LIKE """ & Me.cmbQueue & "*"" AND "
    End If
    
            ' Check for Minimum Count Day
    If Me.txtDayMin > "" Then
        varWhere = varWhere & "[DayCount] > " & Me.txtDayMin & " AND"
    End If
    
            ' Check for Maximum Count Day
    If Me.txtDayMax > "" Then
        varWhere = varWhere & "[DayCount] < " & Me.txtDayMax & " AND "
    End If
    
    
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

SELECT tblODF.UserName, tblODF.ODFNumber, tblODF.Queue, tblODF.ODFScanDate, tblODF.Status, tblODF.LastFollowup, IIf(([Status]="Complete") Or ([Status]="Maturity") Or ([Status]="Agent is Aware of Pending") Or ([Status]="Licensing"),"-",Date()-[LastFollowup]) AS DayCount
FROM tblODF
ORDER BY tblODF.ODFScanDate;

    ' Check for Minimum Count Day
    If Me.txtDayMin > "" Then
        varWhere = varWhere & "[DayCount] > " & Me.txtDayMin & " AND"
    End If

   Me.frmSubODFSearch.Form.RecordSource = "SELECT * FROM qryODFData " & BuildFilter