I don't understand your obsession with reusing combos.
You have two options for variable criteria.
1. build a custom WHERE clause that includes only the search fields that contain values.
2. If there are only a few options, you can use static SQL formatted as follows:
WHERE (fld1 = Forms!yourform!fld1 OR Forms!yourform!fld1 Is Null) AND (fld2 = Forms!yourform!fld2 OR Forms!yourform!fld2 Is Null) AND (fld3 = Forms!yourform!fld3 OR Forms!yourform!fld3 Is Null) AND (fld4 = Forms!yourform!fld4 OR Forms!yourform!fld4 Is Null)
Pay attention to the way the parentheses are used to enclose each OR condition.
2. About combo, the idea behind is that as the user selects an option from option group, the combo box will get data from the related table and will show in the combo box as per the selection and after that the user could select from the combo box to search the specific related results.
For example, if the user selects option vendor and then he could see all the vendors in the combo box and could search the specific vendor related transactions,
and so on for other options.
For the search combinations, still i am a little confused, Is it possible another way,
Access controls are objects so they already have reusability. You are overloading that. So, you are making a class for something that is already a class but you can't use the properties of the underlying class so you always have to change them plus two instances cannot exist at the same time. Bottom line, what are you saving?
Here is a picture of a complex search form. Notice that there are multiple combos. There is no reuse. The app has a similar complex search for Vendor information. There was no point to reusing the Client form to do the vendor search even though there is a lot of overlap of fields. The Client search and the Vendor search have nothing to do with each other. Reusing a single form to provide both search actions would simply make a pathological connection between the two searches. That would leave me in jeopart of breaking one search when I made a change to another. More likely, it wouldn't be me who broke the search though. It would probably have been my successor who might not recognize the reuse of the form.
I could have reused some of the code. I could have broken the BuildSQL into three parts. BuildSQLCommon for the common fields of the two forms. Then BuildSQLCustomer and BuildSQLVendor to do the different fields.
Here is the code from the ViewList button which builds the SQL and opens a form to display the results in a list view.
Code:
Private Sub cmdViewResults_Click()
On Error GoTo Err_Proc
Call BuildSQL
DoCmd.OpenForm "frmSearchResults", , , , , , Me.name
Me.Visible = False
Exit_Proc:
On Error GoTo 0
Exit Sub
Err_Proc:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdViewResults_Click of VBA Document Form_frmClientSearch"
End Select
End Sub
Public Sub BuildSQL()
Dim strSQL As String
Dim strSelect As String
Dim strSelectForExport As String
Dim strWHERE As String
Dim strCondition As String
Dim strOrderBy As String
strSelect = "Select * from qClientList "
strSelectForExport = "Select * from qClientListExport "
strOrderBy = " ORDER BY FullName"
strWHERE = ""
If Me.txtFirstName & "" = "" Then
Else
strWHERE = "FirstName Like " & QUOTE & Me.txtFirstName & "*" & QUOTE
End If
If Me.txtLastName & "" = "" Then
Else
strCondition = "LastName Like " & QUOTE & Me.txtLastName & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtcity & "" = "" Then
Else
strCondition = "City Like " & QUOTE & Me.txtcity & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtaddress & "" = "" Then
Else
strCondition = "Address Like " & QUOTE & Me.txtaddress & "*" & QUOTE
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkBUP = True Then
strCondition = "BUP = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboCareMgrID & "" = "" Then
Else
strCondition = "CareMgrID = " & Me.cboCareMgrID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboGenderID & "" = "" Then
Else
strCondition = "GenderID = " & Me.cboGenderID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtZip & "" = "" Then
Else
strCondition = "Zip = '" & Me.txtZip & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboRaceID & "" = "" Then
Else
strCondition = "RaceID = " & Me.cboRaceID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboDiagnosis & "" = "" Then
Else
strCondition = "Diagnosis = '" & Me.cboDiagnosis & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtpid & "" <> "" Then
strCondition = "PID = '" & Me.txtpid & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtClientID & "" <> "" Then
strCondition = "ClientID = " & Me.txtClientID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtems & "" <> "" Then
strCondition = "EMS = '" & Me.txtems & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.txtPhone & "" <> "" Then
strCondition = "(Phone = '" & Me.txtPhone & "' OR CellPhone = '" & Me.txtPhone & "')"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboCounty & "" <> "" Then
strCondition = "County = '" & Me.cboCounty & "'"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboLevelID & "" <> "" Then
strCondition = "LevelID = " & Me.cboLevelID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboTier & "" <> "" Then
strCondition = "PCATier = " & Me.cboTier
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.cboProgramID & "" = "" Then
Else
strCondition = "ProgramID = " & Me.cboProgramID
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
'Funding
If Me.chkT19 = True Then
strCondition = "T19 = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkSelf = True Then
strCondition = "Self = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkMFP = True Then
strCondition = "MFP = True"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTT19 = True Then
strCondition = "Not(T19 = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTSelf = True Then
strCondition = "Not (Self = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Me.chkNOTMFP = True Then
strCondition = "Not(MFP = True)"
If strWHERE = "" Then
strWHERE = strCondition
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
Select Case Me.fraStatus
Case 1
strCondition = "StatusID = 1318" 'Open
Case 2
strCondition = "StatusID = 1319" 'Closed
Case 4
strCondition = "StatusID = 1427" 'Pending
Case Else
strCondition = ""
End Select
If strWHERE = "" Then
If strCondition = "" Then
Else
strWHERE = strCondition
End If
Else
If strCondition = "" Then
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
'query for form/report
If strWHERE = "" Then
strSQL = strSelect
Else
strSQL = strSelect & " WHERE " & strWHERE
End If
strSQL = strSQL & strOrderBy
Me.txtQuery = strSQL
'query for Excel
If strWHERE = "" Then
strSQL = strSelectForExport
Else
strSQL = strSelectForExport & " WHERE " & strWHERE
End If
strSQL = strSQL & strOrderBy
Me.txtQueryExport = strSQL
End Sub
I do not think this code goes in button search. Call it instead from all of the following events
TxtSearch after update
SearchFrame after update
Startdate after update
Enddate after update.
I renamed the strings for clarity.
Code:
Private Sub SetListbox()
Dim sqlInvoice As String
Dim sqlTranType As String
Dim sqlVendor As String
Dim sqlEntity As String
Dim sqlDate As String
sqlInvoice = "SELECT DocumentNumber, TranType, TranDate FROM tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK WHERE DocumentNumber='" & Me.txtSearch & "'" 'LIKE '*" & Me.txtSearch.Value & "*'"
sqlTranType = "SELECT DocumentNumber, TranType, TranTypeFK, TranDate FROM tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK WHERE TranTypeFK=" & Me.cboSearch 'LIKE '*" & Me.cboSearch.Value & "*'"
sqlVendor = "SELECT DocumentNumber, TranDate, VendorsFK, CompanyName, CompanyNameArabic FROM tblVendors INNER JOIN tblTransactions ON tblVendors.VendorsPK = tblTransactions.VendorsFK WHERE VendorsFK=" & Me.cboSearch 'LIKE '*" & Me.cboSearch.Value & "*'"
sqlDate = "SELECT DocumentNumber, TranDate, TranType, EntityName FROM tblEntities RIGHT JOIN (tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK) ON tblEntities.EntityPK = tblTransactions.EntityFK WHERE TranDate Between #" & Me.StartDate & "# And #" & Me.EndDate & "#"
sqlEntity = "SELECT tblTransactions.DocumentNumber, tblTransactions.TranDate, tblEntities.EntityName, tblEntities.EntityNameArabic, tblTranTypes.TranType, tblTranTypes.TranTypeArabic FROM tblTranTypes INNER JOIN (tblEntities RIGHT JOIN tblTransactions ON tblEntities.EntityPK = tblTransactions.EntityFK) ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK;"
If Not IsNull(Me.txtSearch) Then
'condition 1
Me.Searchlistbox1.RowSource = sqlIvoice
Me.Searchlistbox1.ColumnCount = 3
Me.Searchlistbox1.ColumnWidths = "75px;110px;110px"
ElseIf Me.SearchFrame.Value = 1 And Not IsNull(Me.cboSearch) Then
'condition 2
Me.Searchlistbox1.RowSource = sqlTranType
Me.Searchlistbox1.ColumnCount = 4
Me.Searchlistbox1.ColumnWidths = "75px;110px;0px;110px"
ElseIf Me.SearchFrame.Value = 2 And Not IsNull(Me.cboSearch) Then
'Codition 3
Me.Searchlistbox1.RowSource = sqlVendor
Me.Searchlistbox1.ColumnCount = 5
Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px;110px"
ElseIf Not IsNull(Me.StartDate And Me.EndDate) Then
'Condition 4
Me.Searchlistbox1.RowSource = sqlDate
Me.Searchlistbox1.ColumnCount = 4
Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px"
ElseIf Me.SearchFrame.Value = 3 And Not IsNull(Me.cboSearch) Then
'condition 5
Me.Searchlistbox1.RowSource = sqlEntity
Me.Searchlistbox1.ColumnCount = 6
Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px;110px;110px"
Else
End Sub
However, this is not going to work as designed, if you plan to be able to put values in more than one control (txtSearch, CmboSearch, the dates)
Example
If the user puts a value in txtSearch the SQL for the listbox is limited to the following
Code:
sqlInvoice = "SELECT DocumentNumber, TranType, TranDate FROM tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK WHERE DocumentNumber='" & Me.txtSearch & "'" 'LIKE '*" & Me.txtSearch.Value & "*'"
However if they also then put a value in the cmbo and the frame is 3 (Condition 5) you want to search where the DocumentNumber is like txtSearch and the EntityPK = cmboSearch. Also you would like to see entity information in the listbox.
The problem is you cannot because if there is a value in txtSearch then the sql does not include any Entity information, and there is only three columns in the listbox.
Obviously the easiest fix is one conditions with SQL and columns that includes all possible fields.
DocumentNumber, TranType, TranDate,VendorPK, EntityFK, CompanyName, CompanyNameArabic...
This same problem happens if you put in StartDate, EndDate and then pick something from txtSearch. Or dates and the cmbo.
If you want to narrow down the fields returned you could maybe reorganize. So first check if Condition 5 (it requires the most fields), then condition 3 etc.
To combine lots of filters from different controls I use this Function
Code:
Public Enum CombineFilterType
ct_And = 0
ct_OR = 1
End Enum
Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
Dim FilterCombiner As String
Dim i As Integer
Dim strOut As String
If And_Or = ct_And Then
FilterCombiner = " AND "
Else
FilterCombiner = " OR "
End If
For i = 0 To UBound(Filters)
If Filters(i) <> "" Then
If strOut = "" Then
strOut = Filters(i)
Else
strOut = strOut & FilterCombiner & Filters(i)
End If
End If
Next i
CombineFilters = strOut
End Function
To use it I make a variable for each possible control. The variable will be an empty string if the control is null, but the function then does not include it.
Code:
Public Function GetFilter(AndOr as CombineFilterType) As String
'need variable for each partial filter
Dim strDocumentFltr As String
Dim strDateFltr As String
Dim strVendorFltr As String
Dim strEntityFltr as string As String
Dim strTransTypeFltr As String
if (me.TxtSearch & "") <> "" then
strDocumentFltr = "DocumentID Like '*" me.txtSearch.value & "*'"
end if
if not isnull(me.startDate) and not isnull(me.endDate) then
strDateFltr = "TransactionDate Between " & me.startDate & " AND " & me.EndDate
end if
if not isNull(me.cboSearch) then
select Case me.FrameSearch
case 1
strTransTypeFltr = "TransactionID = " & me.cboSearch
case 2
strVendorFltr = "VendorIDFK = " & me.cboSearch
case 3
strEntityFltr = "EntityIDPK = " & me.cboSearch
end select
end if
GetFilter = CombineFilters(AndOr, StrDocument,strDateFltr,strVendorFltr,strEntityFltr,strTransTypeFltr)
End Function
If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...
To combine lots of filters from different controls I use this Function
Code:
Public Enum CombineFilterType
ct_And = 0
ct_OR = 1
End Enum
Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
Dim FilterCombiner As String
Dim i As Integer
Dim strOut As String
If And_Or = ct_And Then
FilterCombiner = " AND "
Else
FilterCombiner = " OR "
End If
For i = 0 To UBound(Filters)
If Filters(i) <> "" Then
If strOut = "" Then
strOut = Filters(i)
Else
strOut = strOut & FilterCombiner & Filters(i)
End If
End If
Next i
CombineFilters = strOut
End Function
To use it I make a variable for each possible control. The variable will be an empty string if the control is null, but the function then does not include it.
Code:
Public Function GetFilter(AndOr as CombineFilterType) As String
'need variable for each partial filter
Dim strDocumentFltr As String
Dim strDateFltr As String
Dim strVendorFltr As String
Dim strEntityFltr as string As String
Dim strTransTypeFltr As String
if (me.TxtSearch & "") <> "" then
strDocumentFltr = "DocumentID Like '*" me.txtSearch.value & "*'"
end if
if not isnull(me.startDate) and not isnull(me.endDate) then
strDateFltr = "TransactionDate Between " & me.startDate & " AND " & me.EndDate
end if
if not isNull(me.cboSearch) then
select Case me.FrameSearch
case 1
strTransTypeFltr = "TransactionID = " & me.cboSearch
case 2
strVendorFltr = "VendorIDFK = " & me.cboSearch
case 3
strEntityFltr = "EntityIDPK = " & me.cboSearch
end select
end if
GetFilter = CombineFilters(AndOr, StrDocument,strDateFltr,strVendorFltr,strEntityFltr,strTransTypeFltr)
End Function
If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...
Public Sub Buildsql()
Dim sqlqry As String
Dim sqlInvoice As String
Dim sqlTranType As String
Dim sqlVendor As String
Dim sqlEntity As String
Dim sqlDate As String
Dim strCondition As String
Dim strWHERE As String
sqlqry = "SELECT DocumentNumber, TranDate, TranType, tblEntities.EntityName, CompanyName, TransactionsPK, TranTypeFK, EntityFK, VendorsFK FROM qryTransactions"
sqlInvoice = "DocumentNumber='" & Me.txtSearch & "'"
sqlTranType = "TranTypeFK=" & Me.cboTranType
sqlVendor = "VendorsFK=" & Me.cboVendor
sqlEntity = "EntityFK=" & Me.cboWarehouse
sqlDate = "TranDate Between #" & Me.StartDate & "# And #" & Me.EndDate & "#"
If Me.txtSearch & "" = "" Then
Else
'condition 1
strCondition = sqlqry & " WHERE " & sqlInvoice
If strWHERE = "" Then
strWHERE = strCondition
Listbox.RowSource = strWHERE
Listbox.ColumnCount = 9
Listbox.ColumnWidths = "110px;110px;110px;110px;110px;0px;0px;0px;0px"
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Not IsNull(Me.cboTranType) Then
'condition 2
strCondition = sqlqry & " WHERE " & sqlTranType
If strWHERE = "" Then
strWHERE = strCondition
Listbox.RowSource = strWHERE
Listbox.ColumnCount = 9
Listbox.ColumnWidths = "110px;110px;110px;110px;110px;0px;0px;0px;0px"
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Not IsNull(Me.cboVendor) Then
'Codition 3
strCondition = sqlqry & " WHERE " & sqlVendor
If strWHERE = "" Then
strWHERE = strCondition
Listbox.RowSource = strWHERE
Listbox.ColumnCount = 9
Listbox.ColumnWidths = "110px;110px;110px;110px;110px;0px;0px;0px;0px"
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Not IsNull(Me.cboWarehouse) Then
'condition 4
strCondition = sqlqry & " WHERE " & sqlEntity
If strWHERE = "" Then
strWHERE = strCondition
Listbox.RowSource = strWHERE
Listbox.ColumnCount = 9
Listbox.ColumnWidths = "110px;110px;110px;110px;110px;0px;0px;0px;0px"
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
If Not IsNull(Me.StartDate And Me.EndDate) Then
'Condition 5
strCondition = sqlqry & " WHERE " & sqlDate
If strWHERE = "" Then
strWHERE = strCondition
Listbox.RowSource = strWHERE
Listbox.ColumnCount = 4
Listbox.ColumnWidths = "75px;110px;110px;110px"
Else
strWHERE = strWHERE & " AND " & strCondition
End If
End If
End Sub
Private Sub btnSearch_Click()
Call Buildsql
End Sub
Private Sub Form_Load()
cboTranType.RowSource = "SELECT TranTypePK, TranType FROM tblTranTypes"
cboTranType.ColumnCount = 2
cboTranType.ColumnWidths = "0;1cm"
cboVendor.RowSource = "SELECT VendorsPK, CompanyName, CompanyNameArabic FROM tblVendors"
cboVendor.ColumnCount = 3
cboVendor.ColumnWidths = "0;1cm;0"
cboWarehouse.RowSource = "SELECT EntityPK, EntityName, EntityNameArabic FROM tblEntities"
cboWarehouse.ColumnCount = 3
cboWarehouse.ColumnWidths = "0;1cm;0"
End Sub
But still it's not correlated with the fields, and I also want that if there are no results then Listbox should be blank.
I have tried to apply @MajP, the code suggested by him, the way he has defined is very good but I have not worked before on modules and public functions so therefore could not implement it properly.
I have also attached the database for review, thanks
