Public Sub SearchForm()
'Main body of the query
strSQL = "SELECT Distinct tblDocs.* FROM tblDocs INNER JOIN tblDocumentContents ON tblDocs.fldDocID=tblDocumentContents.fldDocID"
'Create Where conditions based on the selected search controls
strWhere = " WHERE tblDocs.fldDocID In (Select fldDocID From tblDocs "
strWhere = strWhere & "WHERE fldDocNameID Like " & Nz(Me.cboSearchDocuments, " '*' ") & ") "
If Not IsNull(Me.cboSearchPeople) Then
strWhere = strWhere & "AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldEntityID Like " & Me.cboSearchPeople & ")"
End If
If Not IsNull(Me.cboSearchPlaces) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldPlaceID Like " & Me.cboSearchPlaces & ")"
End If
If Not IsNull(Me.cboSearchTopics) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocumentContents WHERE fldTopicID Like " & Me.cboSearchTopics & ")"
End If
If Not IsNull(Me.txtDateFrom) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocs WHERE fldDate >= #" & Me.txtDateFrom & "#)"
End If
If Not IsNull(Me.txtDateTo) Then
strWhere = strWhere & " AND tblDocs.fldDocID "
strWhere = strWhere & "In (Select fldDocID From tblDocs WHERE fldDate <= #" & Me.txtDateTo & "#)"
End If
'Complete the SQL
strSQL = strSQL & " " & strWhere
'Assign strSQL to the form recordsource.
Me.RecordSource = strSQL
'Refresh form if there is no records
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "This search produce 0 records"
Call cmdRefresh_Click
End If
End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE