I
Incognito
I have (2)cbo's to filter a subform. One works fine, but
when i select the second for filter as well - nothing
happens. I also want the form to populate ALL records, if
nothing is selected, but doesn't seem to like my code.
Maybe I just need another pair of eyes to look at it.
Can't find the error. Thanks a million!!!
Here is what I have:
This is the code for the cbo:
Set Me.Recordset = FilterData
This is the code to pull ALL records:
Dim Update As String
Dim tblExclude_Error As subform
If Toggle45 = Update Then
tblExclude_Error.Visible = True
End If
This is the code for filtering:
Dim ErrorValue As String
Dim APPSYSID As String
Dim SalesID As String
Dim FilterValue As String
Dim SQL As String
Dim rs As DAO.Recordset
Dim myDB As DAO.Database
' get the value of the Error from the combo box
Me.cboError.SetFocus
ErrorValue = Me.cboError.Text
' get the value of the AppSysID from the combo box
Me.cboAppID.SetFocus
APPSYSID = Me.cboAppID.Text
' get the value of the SalesID from the combo box
' get an instance of the database
Set myDB = CurrentDb
' set up the filter
SQL = "SELECT * FROM [tblExclude] "
If Len(Trim$(ErrorValue & "")) > 0 Then
' we have an error value
If Len(Trim$(APPSYSID & "")) > 0 Then
' we have a app sys id
'If Len(Trim$(SalesID & "")) > 0 Then
' we have a sales id
SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & " "
' we do not have an app sys id
Else
SQL = SQL & "where [Error]=" & ErrorValue
' we do not have a error value
End If
Else
If Len(Trim$(APPSYSID & "")) > 0 Then
' we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & APPSYSID
'If Len(Trim$(SalesID & "")) > 0 Then
'we have a sales id
'SQL = SQL & "where [Sales ID]=" & SalesID
Else
' we do not have an app sys id
' we do not have a sales id
' do not change the SQL
End If
End If
Set rs = myDB.OpenRecordset(SQL)
Set FilterData = rs
Exit Function
err_routine:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbOKOnly + vbInformation, "Error"
Set FilterData = Nothing
Exit Function
End Function
when i select the second for filter as well - nothing
happens. I also want the form to populate ALL records, if
nothing is selected, but doesn't seem to like my code.
Maybe I just need another pair of eyes to look at it.
Can't find the error. Thanks a million!!!
Here is what I have:
This is the code for the cbo:
Set Me.Recordset = FilterData
This is the code to pull ALL records:
Dim Update As String
Dim tblExclude_Error As subform
If Toggle45 = Update Then
tblExclude_Error.Visible = True
End If
This is the code for filtering:
Dim ErrorValue As String
Dim APPSYSID As String
Dim SalesID As String
Dim FilterValue As String
Dim SQL As String
Dim rs As DAO.Recordset
Dim myDB As DAO.Database
' get the value of the Error from the combo box
Me.cboError.SetFocus
ErrorValue = Me.cboError.Text
' get the value of the AppSysID from the combo box
Me.cboAppID.SetFocus
APPSYSID = Me.cboAppID.Text
' get the value of the SalesID from the combo box
' get an instance of the database
Set myDB = CurrentDb
' set up the filter
SQL = "SELECT * FROM [tblExclude] "
If Len(Trim$(ErrorValue & "")) > 0 Then
' we have an error value
If Len(Trim$(APPSYSID & "")) > 0 Then
' we have a app sys id
'If Len(Trim$(SalesID & "")) > 0 Then
' we have a sales id
SQL = SQL & "where [Error]=" & ErrorValue & "
and [APP SYS ID]=" & APPSYSID & " "
' we do not have an app sys id
Else
SQL = SQL & "where [Error]=" & ErrorValue
' we do not have a error value
End If
Else
If Len(Trim$(APPSYSID & "")) > 0 Then
' we have a app sys id
SQL = SQL & "where [APP SYS ID]=" & APPSYSID
'If Len(Trim$(SalesID & "")) > 0 Then
'we have a sales id
'SQL = SQL & "where [Sales ID]=" & SalesID
Else
' we do not have an app sys id
' we do not have a sales id
' do not change the SQL
End If
End If
Set rs = myDB.OpenRecordset(SQL)
Set FilterData = rs
Exit Function
err_routine:
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbOKOnly + vbInformation, "Error"
Set FilterData = Nothing
Exit Function
End Function