I am trying to create a search form that has multiple UNBOUND combo and text boxes. Within the form is a subform named "SearchSubform" that displays the filtered/queried data. The SearchSubform's record source is a query i named "SearchQuery".
When the user enters data from a form named "SearchForm", I want the data to be filtered correctly whether or not the user filled in some of the combo and text boxes or all of them.
- I have a command button named "Search" that only has the following code:
Private Sub Search_Click()
SearchSubform.Requery
End Sub
- I have code to make sure all the fields are cleared on open:
Private Sub Form_Open(Cancel As Integer)
FacilitySearch = ""
TypeOfDocumentSearch = ""
ContractNumberSearch = ""
ConsultantSearch = ""
DescriptionSearch = ""
LocationSearch = ""
SearchSubform.Requery
End Sub
- I have individual buttons that clear the field(rest not included)
Private Sub Clear1_Click()
FacilitySearch = ""
SearchSubform.Requery
End Sub
- I have a button that clear all fields:
Private Sub ClearAll_Click()
FacilitySearch = ""
TypeOfDocumentSearch = ""
ContractNumberSearch = ""
DescriptionSearch = ""
ConsultantSearch = ""
LocationSearch = ""
SearchSubform.Requery
End Sub
The following SQL code for my "SearchQuery" isnt properly displaying all the records that should display. Is there any way to improve this feature?
SELECT [Document Catalog].[Document ID], [Document Catalog].Facility, [Document Catalog].[Type of Document], ([Contract Number 1] & ("[WO#"+[Work Order Number 1]+"]")) & ("("+([Project Number] & (" Task "+[Task Number]))+")") AS [Contract Number], [Document Catalog].Consultant, [Document Catalog].[Title of Document], [Document Catalog].[Title of Document 2], [Document Catalog].[Shelf/Drawer Number], [Document Catalog].[Box/Tube Number]
FROM [Document Catalog]
WHERE ((([Document Catalog].Facility) Like "*" & [Forms]![SearchForm]![FacilitySearch] & "*") AND (([Document Catalog].[Type of Document]) Like "*" & [Forms]![SearchForm]![TypeOfDocumentSearch] & "*") AND ((([Contract Number 1] & ("[WO#"+[Work Order Number 1]+"]")) & ("("+([Project Number] & (" Task "+[Task Number]))+")")) Like "*" & [Forms]![SearchForm]![ContractNumberSearch] & "*") AND (([Document Catalog].Consultant) Like "*" & [Forms]![SearchForm]![ConsultantSearch] & "*") AND (([Title of Document] & " " & [Title of Document 2] & " " & [Notes]) Like "*" & [Forms]![SearchForm]![DescriptionSearch] & "*") AND (([Shelf/Drawer Number] & " " & [Box/Tube Number]) Like "*" & [Forms]![SearchForm]![LocationSearch] & "*"))
ORDER BY [Document Catalog].[Document ID];
Thanks in advance!