Search Form Issues (1 Viewer)

A

ahmad_rmh

Member
Local time
Today, 22:26
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.
 
K

KitaYama

Well-known member
Local time
Tomorrow, 04:26
Joined
Jan 6, 2022
Messages
1,541
You're missing your field name before like :
Code: 
sql = "SELECT DocumentNumber, TranType FROM  qryTransactionsExtended WHERE YourFieldname LIKE '" & Me.txtSearch.Value & "*'"
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
thanks,
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
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
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:26
Joined
May 21, 2018
Messages
8,529
Your second else if references cbosearch same as first else if. I assume you have a different combo.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:26
Joined
May 21, 2018
Messages
8,529
If thens short circuit. So your second else if never evaluates.
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
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: 64
  • Untitled.png
    Untitled.png
    11.8 KB · Views: 73
  • Untitled1.png
    Untitled1.png
    8.5 KB · Views: 73
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:26
Joined
May 21, 2018
Messages
8,529
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.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:26
Joined
May 21, 2018
Messages
8,529
Simply change the second else if to searchlistbox1. Not cbosearch
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
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
 
bastanu

bastanu

AWF VIP
Local time
Today, 12:26
Joined
Apr 13, 2010
Messages
1,402
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,
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:26
Joined
Feb 19, 2002
Messages
43,280
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 =
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
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
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
Thanks Pat, got your idea,

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

Or suggest the alternative way please.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:26
Joined
May 21, 2018
Messages
8,529
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)
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
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.
 
A

ahmad_rmh

Member
Local time
Today, 22:26
Joined
Jun 26, 2022
Messages
243
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: 59
You must log in or register to reply here.

Similar threads

S
Solved Combo Box Issue Selecting Item versus Typing Item
Replies
14
Views
575
arnelgp
arnelgp
F
Solved Combobox search as you type issue
2
Replies
20
Views
1,467
moke123
moke123
E
Solved Listbox search not working with aggregated query
Replies
7
Views
273
elannesse
E
G
Combobox on continuous form only showing first record
Replies
10
Views
517
Pat Hartman
P
oxicottin
Solved Export search form data to excel
Replies
11
Views
604
oxicottin
oxicottin

Users who are viewing this thread

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