I have a form which displays open action items (frmTaskListOpen). On this form is a Search button which when pressed opens another form (frmSearch). On this form, the user can select a field to search on and the search criteria. The idea is for the user to be able to perform a search on select fields in the main table (tblTasks) via a query (qryOpenTasks). This system works if the field selected on the search form is not a lookup field from tblTasks, such as “Title”. However, if I select “ActionItemSubject”, which is a field in tblTasks whose value comes from another table (tblTypeActionItems), then the search action returns a blank form. The code in the On Click Event Procedure for the search command button on the form frmSearch is shown below.
Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
'Filter frmTaskListOpen based on search criteria
Form_frmTaskListOpen.RecordSource = "select * from qryOpenTasks where " & GCriteria
Form_frmTaskListOpen.Caption = "qryOpenTasks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
'Close frmSearch
DoCmd.Close acForm, "frmSearch"
MsgBox "Results have been filtered."
End If
End Sub
I have attached a zip file which contains a sample from my database. The code was borrowed from a sample database, and I just can’t figure out how to adapt it exactly to my situation. I would sincerely appreciate any help in correcting the code. Thanks in advance.
Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."
ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."
Else
'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
'Filter frmTaskListOpen based on search criteria
Form_frmTaskListOpen.RecordSource = "select * from qryOpenTasks where " & GCriteria
Form_frmTaskListOpen.Caption = "qryOpenTasks (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
'Close frmSearch
DoCmd.Close acForm, "frmSearch"
MsgBox "Results have been filtered."
End If
End Sub
I have attached a zip file which contains a sample from my database. The code was borrowed from a sample database, and I just can’t figure out how to adapt it exactly to my situation. I would sincerely appreciate any help in correcting the code. Thanks in advance.
