Search Form Issues

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.
 
Last edited:
Thanks Pat,
Thanks Maj,

The information and guidelines, you both have provided me much more useful and the code would be changed as per the suggestions,

Thanks
 
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

See some other tricks here.

Get Filter from Controls, CSQL, and Combine Multiple Filters

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...
www.access-programmers.co.uk
 
MajP said:
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

See some other tricks here.

Get Filter from Controls, CSQL, and Combine Multiple Filters

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...
www.access-programmers.co.uk
Click to expand...

Thanks a lot Maj for giving me your precious time.
 
I have revised the code as per the suggestions

Code: 
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
 

Attachments

Last edited:
You must log in or register to reply here.

Similar threads

B
Combo Box Search Issue
2
Replies
32
Views
2,293
MajP
MajP
K
A dream: What I do miss about Access.
2
Replies
20
Views
2,085
isladogs
isladogs
I
Solved Error about assigning Null value when clicking away from deleted form field
Replies
16
Views
816
BlueSpruce
BlueSpruce
Uncle Gizmo
How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation
2
Replies
24
Views
1,892
MarkK
MarkK
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
1,214
ricardocpa
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom