caykamanj1966
Technical User
All,
I have the below code for a search form. It works perfectly, but I have a unbound field (combo box) called: txtFilterStatus and the field in the table is called: status.
That combox has several values and 2 of those values are: Activated and Deactivated.
If I search for Activated, it also gives me the Deactivated records, which I don't want in the results. Of course, it is because the word "activated" is also in deactivated.
How do I modify the code below to not get the deactivated records, when searching for the activated records?:
Any help will greatly be appreciated and thanks in advance!
I have the below code for a search form. It works perfectly, but I have a unbound field (combo box) called: txtFilterStatus and the field in the table is called: status.
That combox has several values and 2 of those values are: Activated and Deactivated.
If I search for Activated, it also gives me the Deactivated records, which I don't want in the results. Of course, it is because the word "activated" is also in deactivated.
How do I modify the code below to not get the deactivated records, when searching for the activated records?:
Code:
Private Sub Command137_Click()
On Error GoTo ErrorHandler
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
'strSQL = "SELECT tbl_weeklyMAs.ma_id,"
'strSQL = strSQL & "tbl_weeklyMAs.impacts, tbl_weeklyMAs.submit_date, tbl_weeklyMAs.status, tbl_weeklyMAs.title, tbl_weeklyMAs.planned_start_date, tbl_weeklyMAs.planned_end_date,"
'strSQL = strSQL & "tbl_weeklyMAs.requestor "
'
strSQL = "SELECT tbl_weeklyMAs.* "
strSQL = strSQL & "FROM tbl_weeklyMAs "
strWhere = "WHERE"
strOrder = "ORDER BY tbl_weeklyMAs.ma_id;"
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtFilterImpacts) And Me.txtFilterImpacts <> "" Then
'<--If the textbox ma_id and the other text boxes contains no data THEN do nothing
strWhere = strWhere & " (tbl_weeklyMAs.impacts) Like '*" &
Me.txtFilterImpacts & "*' AND" '<--otherwise, apply the LIKE statement to the QueryDef
End If
If Not IsNull(Me.txtFilter_ma_id) And Me.txtFilter_ma_id <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.ma_id) Like '*" &
Me.txtFilter_ma_id & "*' AND"
End If
If Not IsNull(Me.txtFilterSubmitDate) And Me.txtFilterSubmitDate <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.submit_date) Like '*" &
Me.txtFilterSubmitDate & "*' AND"
End If
If Not IsNull(Me.txtFilterStatus) And Me.txtFilterStatus <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.status) Like '*" &
Me.txtFilterStatus & "*' AND"
End If
If Not IsNull(Me.txtFilterTitle) And Me.txtFilterTitle <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.title) Like '*" &
Me.txtFilterTitle & "*' AND"
End If
If Not IsNull(Me.txtFilterRequestor) And Me.txtFilterRequestor <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.requestor) Like '*" &
Me.txtFilterRequestor & "*' AND"
End If
If Not IsNull(Me.txtFilterPlannedStartDate) Then
strWhere = strWhere & " (tbl_weeklyMAs.planned_start_date) >= #" &
Me.txtFilterPlannedStartDate & "# AND"
End If
If Not IsNull(Me.txtFilterPlannedEndDate) Then
strWhere = strWhere & " (tbl_weeklyMAs.planned_end_date) <= #" &
Me.txtFilterPlannedEndDate & "# AND"
End If
'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'MsgBox strSQL & " " & strWhere & " " & strOrder
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_Search")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_Search", dbOpenDynaset)
With rst
If .recordcount > 0 Then 'What you want done
DoCmd.OpenForm "frm_SearchResults"
Else
MsgBox "There is no data that meets your criteria. Please try again.", vbOKOnly, "No Data Found" 'What you want done
DoCmd.OpenForm "frm_search_MAs"
End If
End With
Set dbs = Nothing
Set rst = Nothing
Me.txtFilterImpacts = Null
Me.txtFilter_ma_id = Null
Me.txtFilterSubmitDate = Null
Me.txtFilterStatus = Null
Me.txtFilterTitle = Null
Me.txtFilterPlannedStartDate = Null
Me.txtFilterPlannedEndDate = Null
Me.txtFilterRequestor = Null
ExitHandler:
Exit Sub
ErrorHandler:
If Err = 2489 Then
Resume ExitHandler
Else
MsgBox Err.Description
Resume ExitHandler
End If
End SubAny help will greatly be appreciated and thanks in advance!
