Search Form Issues

A

ahmad_rmh

Member
Local time
Tomorrow, 02:31
Joined
Jun 26, 2022
Messages
243
I am making search form as under

1. added text box named @ txtSearch
2. added find button named @ btnSearch
3. added list box named @Searchlistbox1

I am using the following code to show record in the list box as per the text box.

Code: 
Private Sub btnSearch_Click()
    Dim sql As String
    sql = "SELECT DocumentNumber, TranType FROM  qryTransactionsExtended WHERE LIKE '" & Me.txtSearch.Value & "*'"

    If Me.txtSearch <> "" Then
        Me.Searchlistbox1.RowSource = sql
        Me.Searchlistbox1.ColumnCount = 2
        Me.Searchlistbox1.ColumnWidths = "75px;110px"
    Else
        MsgBox "Cannot search blank", vbOKOnly
        Me.txtSearch.SetFocus
    End If
End Sub

When I am applying where condition it's showing blank but without where condition it's searching the records.

kindly suggest.
 
You're missing your field name before like :
Code: 
sql = "SELECT DocumentNumber, TranType FROM  qryTransactionsExtended WHERE YourFieldname LIKE '" & Me.txtSearch.Value & "*'"
 
thanks,
 
I have added the following in my search form


1. added text box named @ txtSearch
2. added find button named @ btnSearch
3. added combobox named @ cboSearch
4. added list box named @ Searchlistbox1

Code: 
Private Sub btnSearch_Click()
    Dim sql As String
    Dim str As String
    Dim str1 As String
    
    sql = "SELECT DocumentNumber, TranType FROM  tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK WHERE DocumentNumber LIKE '*" & Me.txtSearch.Value & "*'"

    str = "SELECT DocumentNumber, TranType FROM  tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK WHERE TranType LIKE '*" & Me.cboSearch.Value & "*'"

    str1 = "SELECT DocumentNumber, CompanyName, CompanyNameArabic FROM tblVendors INNER JOIN tblTransactions ON tblVendors.VendorsPK = tblTransactions.VendorsFK WHERE CompanyName LIKE '*" & Me.cboSearch.Value & "*'"

    If Me.txtSearch <> "" Then
        Me.Searchlistbox1.RowSource = sql
        Me.Searchlistbox1.ColumnCount = 2
        Me.Searchlistbox1.ColumnWidths = "75px;110px"

    ElseIf Me.cboSearch <> "" Then
        Me.Searchlistbox1.RowSource = str
        Me.Searchlistbox1.ColumnCount = 2
        Me.Searchlistbox1.ColumnWidths = "75px;110px"

    ElseIf Me.cboSearch <> "" Then
        Me.Searchlistbox1.RowSource = str1
        Me.Searchlistbox1.ColumnCount = 3
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px"

    Else
        MsgBox "Cannot search blank", vbOKOnly
        Me.txtSearch.SetFocus

    End If

End Sub

the 2nd Elseif condition is not showing records in Searchlistbox1 where as I have made testing individually, It's working.

suggest the solution, thanks
 
Your second else if references cbosearch same as first else if. I assume you have a different combo.
 
If thens short circuit. So your second else if never evaluates.
 
the combo box values are coming through option group selection

Code: 
Private Sub SearchFrame_AfterUpdate()
    If Me.SearchFrame.Value = 1 Then
    cboSearch.RowSource = "SELECT TranType from tblTranTypes;"
    ElseIf Me.SearchFrame.Value = 2 Then
    cboSearch.RowSource = "SELECT CompanyName from tblVendors;"
    End If
    Me.SearchFrame.Requery
End Sub

attached screenshots
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    23.6 KB · Views: 92
  • Untitled.png
    Untitled.png
    11.8 KB · Views: 97
  • Untitled1.png
    Untitled1.png
    8.5 KB · Views: 102
Last edited:
Code: 
ElseIf Me.cboSearch <> "" Then
        Me.Searchlistbox1.RowSource = str
        Me.Searchlistbox1.ColumnCount = 2
        Me.Searchlistbox1.ColumnWidths = "75px;110px"

    ElseIf Me.cboSearch <> "" Then
        Me.Searchlistbox1.RowSource = str1
        Me.Searchlistbox1.ColumnCount = 3
        Me.Searchlistbox1.ColumnWidths = "75px;
Second "else if' never evaluated.
 
Simply change the second else if to searchlistbox1. Not cbosearch
 
Search Form Date Criteria:

how to give date criteria, I have added the following code

Code: 
Private Sub btnSearch_Click()
Dim strDate As String
strDate = "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

If Me.StartDate <> "" And Me.EndDate <> "" Then
        Me.Searchlistbox1.RowSource = strDate
        Me.Searchlistbox1.ColumnCount = 4
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px"
End If

End Sub

Kindly suggest
 
The date variables need to be enclosed in #:
Code: 
Private Sub btnSearch_Click()
Dim strDate As String
strDate = "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 & "#"

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate)  Then ' you should clear the search boxes by making them Null not "" Zero Length String
        Me.Searchlistbox1.RowSource = strDate
        Me.Searchlistbox1.ColumnCount = 4
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px"
End If

End Sub
Cheers,
 
LIKE is a string operation. It may not give correct results when the data field is a number or a date (which is actually a double precision number, NOT a string)

Also, LIKE, as you are using it will always force a full table scan. It can never use an index. That means that as the row count of the table grows, the search will slow down because there will be more rows to examine. Which doesn't mean you shouldn't use LIKE but it does mean that you shouldn't misuse it because it is an expensive operation. When you are searching for a FK, you would always have the full numeric value so you would never use LIKE, you would always use =
 
bastanu said:
The date variables need to be enclosed in #:
Code: 
Private Sub btnSearch_Click()
Dim strDate As String
strDate = "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 & "#"

If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate)  Then ' you should clear the search boxes by making them Null not "" Zero Length String
        Me.Searchlistbox1.RowSource = strDate
        Me.Searchlistbox1.ColumnCount = 4
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px"
End If

End Sub
Cheers,
Click to expand...

Thanks Vlad
 
Thanks Pat, got your idea,

It's mean that we have to assign exact match condition criteria,

Or suggest the alternative way please.
 
To make your life easier you can use the CSQL function

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
Not only do dates need to get enclosed in #, but most of the time they need to be in a non-ambiguous format regardless of your regional settings (MM/DD/YYYY or YYYYMMDD)

Code: 
Between " & CSQL(Me.StartDate) & " And " & CSQL(Me.EndDate)
 
MajP said:
To make your life easier you can use the CSQL function

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
Not only do dates need to get enclosed in #, but most of the time they need to be in a non-ambiguous format regardless of your regional settings (MM/DD/YYYY or YYYYMMDD)

Code: 
Between " & CSQL(Me.StartDate) & " And " & CSQL(Me.EndDate)
Click to expand...

thanks Maj.
 
Multiple criteria in Search Form:

My search form is working normal but now I want to implement multiple criteria. The code which I am using in each field is as under and the screenshot is also attached herewith.

The detail of requirement is as under

criteria 1 : Invoice Number
criteria 2: Tran Type
criteria 3: Date From and Date To

if all of the criteria have been selected then search should have to be correlate to each other.

Code: 
Private Sub btnSearch_Click()
    Dim sql As String
    Dim str As String
    Dim str1 As String
    Dim str2 As String
    Dim strDate As String

    sql = "SELECT DocumentNumber, TranType, TranDate FROM  tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK WHERE DocumentNumber='" & Me.txtSearch & "'" 'LIKE '*" & Me.txtSearch.Value & "*'"
    str = "SELECT DocumentNumber, TranType, TranTypeFK, TranDate FROM  tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK WHERE TranTypeFK=" & Me.cboSearch 'LIKE '*" & Me.cboSearch.Value & "*'"
    str1 = "SELECT DocumentNumber, TranDate, VendorsFK, CompanyName, CompanyNameArabic FROM tblVendors INNER JOIN tblTransactions ON tblVendors.VendorsPK = tblTransactions.VendorsFK WHERE VendorsFK=" & Me.cboSearch 'LIKE '*" & Me.cboSearch.Value & "*'"
    strDate = "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 & "#"
    str2 = "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
        Me.Searchlistbox1.RowSource = sql
        Me.Searchlistbox1.ColumnCount = 3
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px"
        
    ElseIf Me.SearchFrame.Value = 1 And Not IsNull(Me.cboSearch) Then
        Me.Searchlistbox1.RowSource = str
        Me.Searchlistbox1.ColumnCount = 4
        Me.Searchlistbox1.ColumnWidths = "75px;110px;0px;110px"
        
    ElseIf Me.SearchFrame.Value = 2 And Not IsNull(Me.cboSearch) Then
        Me.Searchlistbox1.RowSource = str1
        Me.Searchlistbox1.ColumnCount = 5
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px;110px"
        
    ElseIf Not IsNull(Me.StartDate And Me.EndDate) Then
        Me.Searchlistbox1.RowSource = strDate
        Me.Searchlistbox1.ColumnCount = 4
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px"
        
    ElseIf Me.SearchFrame.Value = 3 And Not IsNull(Me.cboSearch) Then
        Me.Searchlistbox1.RowSource = str2
        Me.Searchlistbox1.ColumnCount = 6
        Me.Searchlistbox1.ColumnWidths = "75px;110px;110px;110px;110px;110px"
        
    Else
        MsgBox "Cannot search blank", vbOKOnly
        Me.txtSearch.SetFocus
    End If
End Sub

Private Sub Form_Load()
    Me.SearchFrame.DefaultValue = False
End Sub

Private Sub SearchFrame_AfterUpdate()

    If SearchFrame.Value = 1 Then
    cboSearch.RowSource = "SELECT DISTINCT TranTypeFK, TranType FROM  tblTranTypes INNER JOIN tblTransactions ON tblTranTypes.TranTypePK = tblTransactions.TranTypeFK;"
    cboSearch.ColumnCount = 2
    cboSearch.ColumnWidths = "0;1cm"
    
    ElseIf Me.SearchFrame.Value = 2 Then
    cboSearch.RowSource = "SELECT DISTINCT VendorsFK, CompanyName, CompanyNameArabic FROM tblVendors INNER JOIN tblTransactions ON tblVendors.VendorsPK = tblTransactions.VendorsFK;"
    cboSearch.ColumnCount = 3
    cboSearch.ColumnWidths = "0;1cm;0"
    
    ElseIf Me.SearchFrame.Value = 3 Then
    cboSearch.RowSource = "SELECT DISTINCT EntityFK, EntityName, EntityNameArabic FROM tblEntities INNER JOIN tblTransactions ON tblEntities.EntityPK = tblTransactions.EntityFK;"
    cboSearch.ColumnCount = 3
    cboSearch.ColumnWidths = "0;1cm;0"
    End If
    
    Me.SearchFrame.Requery
    
End Sub

kindly suggest, thanks
 

Attachments

  • Capture.JPG
    Capture.JPG
    26.1 KB · Views: 82
You must log in or register to reply here.

Similar threads

K
Solved Search comboBox on a bounded form
Replies
2
Views
347
Pat Hartman
P
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
860
Pat Hartman
P
asteropi
Solved How to set up unique customer codes based on customer types
2
Replies
31
Views
1,108
jdraw
jdraw
S
Solved Combo Box Issue Selecting Item versus Typing Item
Replies
14
Views
1,031
arnelgp
arnelgp
F
Solved Combobox search as you type issue
2
Replies
20
Views
2,243
moke123
moke123

Users who are viewing this thread

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