Multi field search-box (1 Viewer)

smtazulislam

smtazulislam

Member
Local time
Tomorrow, 01:07
Joined
Mar 27, 2020
Messages
806
Hey there,
I trying to multi field search box or Group Mark selection into search-Box. attached file (office 2019) have been work, but when reopen my database its not work.
"What the reasons, can you checking my code have any problems or any others reasons.

note : [List Name = List3] ------ [table name = Data_all] ------ [searchbox name = txtSearchBox ]
[Module= Global StrRowsource as string ------ Variable = StrRowsource ]

Code Tags Added By UG
Code: 
Private Sub List3_AfterUpdate()
    Me.txtSearchbox.SetFocus
End Sub

Private Sub txtSearchBox_Change()
    If Frame1 = 1 Then 'Track Numbers
        StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
        "WHERE TrackNumbers LIKE '*" & Me.txtSearchbox.Text & "*' "
  
    ElseIf Frame1 = 2 Then 'Date
        StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
        "WHERE Date LIKE '*" & Me.txtSearchbox.Text & "*' "
  
    ElseIf Frame1 = 3 Then 'ShipperName
        StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
        "WHERE ShipperName LIKE '*" & Me.txtSearchbox.Text & "*' "
  
    ElseIf Frame1 = 4 Then 'RecipientName
        StrRowsouce = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
        "WHERE RecipientName LIKE '*" & Me.txtSearchbox.Text & "*' "
  
    ElseIf Frame1 = 5 Then 'Shpper Company
        StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
        "WHERE Company LIKE '*" & Me.txtSearchbox.Text & "*' "
      
    ElseIf Frame1 = 6 Then 'Recipient Company
        StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
        "WHERE CompanyR LIKE '*" & Me.txtSearchbox.Text & "*' "
  
    ElseIf Frame1 = 7 Then 'Shipper Mobile
        StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
        "WHERE Mobile LIKE '*" & Me.txtSearchbox.Text & "*' "
  
    ElseIf Frame1 = 8 Then 'Recipient Mobile
        StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents " & "FROM Data_All " & _
        "WHERE MobileR LIKE '*" & Me.txtSearchbox.Text & "*' "
    Else
    End If
    List3.RowSource = StrRowsource
End Sub

Private Sub txtSearchBox_Click()
    StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents " & "FROM Data_All "
    List3.RowSource = StrRowsource
End Sub
 
Last edited:
welcome to the forum - in future please surround vba code with the code tags to preserve formatting (see dropdown on 3 dot option next to face). code without formatting is difficult to read. You also need to explain what 'not work' means - you get an error? wrong result? nothing happens? something else?

Can tell you Date is a reserved word (it means today), using it as a field name is a bad idea. If you have to use it surround with square brackets

You can also simply your code

SQL: 
Private Sub txtSearchBox_Change()
dim StrRowsource as string

    StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode,         Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR,                     CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All WHERE "

    Select Case Frame1

        Case 1
            StrRowsource=StrRowsource & "TrackNumbers LIKE '*" & Me.txtSearchbox.Text & "*'"
        Case 2
            StrRowsource=StrRowsource & "[Date] LIKE '*" & Me.txtSearchbox.Text & "*' "
        etc

    end select
    etc

you also cannot search dates they way you have it
 
not trying to be a jerk, but there is NO way you will get a response by posting all of that non-sensical code, especially if it is not in CODE tags.

in terms of searching MORE than one field for any given value, there are many ways to do such a thing. if i were to take one piece of code that you posted, like this one:
Code: 
StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName,
Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone,
TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR,
PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents " & "FROM Data_All " & _
"WHERE MobileR LIKE '*" & Me.txtSearchbox.Text & "*' "
Else
End If
List3.RowSource = StrRowsource
End Sub
you are simply assigning a query statement to a listbox. all of your code is very redundant. you can seriously reduce the number of lines of code you are using by doing this in ONE BLOCK:
Code: 
StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName,
Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone,
TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR,
PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All

WHERE MobileR LIKE '*' & '" & Me.txtSearchbox & "'*'"
and if you want to use multiple criteria in the search, you do this:
Code: 
WHERE MobileR = '" & Me.txtSearchbox & "' & OR " & _
[field2] = '" & me.txtSearch & "'"
 
as usual you have gone off on a complete tangent to the original question. The OP says it doesn't work and if you looked at the OP's data and form you will see that your suggestion for multi field searches won't work.

not trying to be a jerk,
Click to expand...
yes - you are
 
If your [date] field is really a date then you cannot do this
Code: 
  StrRowsource=StrRowsource & "[Date] LIKE '*" & Me.txtSearchbox.Text & "*'

In sql you have to delimit properly
SomeTextField = "SomeText"
someNumericField = 123
SomeDateFeidl = #1/1/2020#

However if you want to do the above add a calculated field in your query
....Format([Date],"mm/dd/yyyy") AS FormatDate,.....

Then change the code to
Code: 
strRowsource=StrRowsource & "FormatDate LIKE '*" & Me.txtSearchbox.Text & "*'

@vba_php
I do not understand how you can post
but there is NO way you will get a response by posting all of that non-sensical code, especially if it is not in CODE tags.
Click to expand...
When CJ_London had pretty quickly already answered the OP's post. "No one will answer, but you already have answer?." You need to start writing Trump Corona virus updates briefs.
 
MajP said:
@vba_php
I do not understand how you can post
Click to expand...
I was simply saying that all of the code posted without code tags was unintelligible. Do you understand me now? It's incredibly difficult to decipher lots of code without tags at the minimum
 
@vba_php
As you can see the OP joined today and that was their first post ever. Not everyone posts to forums often and are aware of the formatting features. So you probably could be just a little more diplomatic. That was not even my point. The point was you said no one would answer, after a good answer was already made.
 
I trying again like that:

Code: 
Option Compare Database
Option Explicit

Dim StrRowsource As String

Private Sub txtSearchBox_Change()
    StrRowsource = "SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All " & _
    "WHERE TrackNumbers = '" & Me.txtSearchbox & "' OR Mobile = '" & Me.txtSearchbox & "' "
    
End Sub
But its not response filter. Kindly dont anger anyone, VBA code I am totally new.
 
That looks right but I would add

debug.print strRowsource 'now you can look at it or even drop it into a query window to see if it is correct.
List3.RowSource = StrRowsource
 
Once thing that can really help avoid problems is to make a simple query and save it. Lets call it qryAllDataFilter.

"SELECT ID, TrackNumbers, Date, ShipmentType, Department, ShipperName, Company, Address, City, Province, PostBox, ZIPCode, Country, Mobile, TelPhone, TelPhoneExtention, EmailAddress, RecipientName, CompanyR, AddressR, CityR, ProvinceR, PostBoxR, ZIPCodeR, CountryR, MobileR, TelPhoneR, TelPhoneExtentionR, EmailAddressR, Contents FROM Data_All order by ...."

Then your code is a lot simpler and you do not have to worry about a spelling error in a name field.


Private Sub txtSearchBox_Change()
StrRowsource = "SELECT * from qryAllDataFilter WHERE TrackNumbers = '" & Me.txtSearchbox & "' OR Mobile = '" & Me.txtSearchbox & "' "
debug.print strRowsource 'now you can look at it or even drop it into a query window to see if it is correct.
List3.RowSource = StrRowsource
End Sub
Click to expand...

FYI, sometimes people will argue that they do not want to save queries because they bloat the DB. A query adds very little to a DB. One image on a form probably takes more room than all your queries combined.
 
@smtazulislam - re post#10, the code you are quoting is VBA's which is way off topic from what you are asking. Please look at one of the other suggestions made, they are all valid with the understanding we still don't know what 'not work' means
 
FYI I moved this thread to a more appropriate forum.
 
@smtazulislam,
If you are still having problems, post a sample database. You can remove unnecessary, forms and tables. You will need to zip it first.
 
Last edited:
Looks like he's trying to search multiple fields. Radio buttons are not the way to go to do that. You need a form with all the fields you wish to search, and the user can put search criteria into multiple fields. Similar to what is described in this video: Search Form
 
Here is the solution

HOWEVER, there is something in here I had no idea about. Either on purpose or accident your database is in ANSI mode. So the filter is not

Like '*Text*'
but
Alike '%Text%'

You need to determine if that is what you really want. This may be a regional setting thing or compatability with SQL server.


Code: 
Private Sub txtSearchBox_Change()
'Find as you type
  If Not Trim(Me.txtSearchbox.Text & " ") = "" Then
    FilterList
  Else
    List3.RowSource = "qryAlldata"
  End If
End Sub
Private Sub FilterList()
  Dim strRowSource As String
  Dim strVal As String
  Dim strWhere As String
  strRowSource = "Select * from qryAllDataFilter WHERE "
  'If you do not want ANSI 92 mode then use below and change settings
  'strVal = "LIKE '*" & Me.txtSearchbox.Text & "*'"
  'This is using ANSI 92 mode
  strVal = "ALIKE '%" & Me.txtSearchbox.Text & "%'"
  'Debug.Print strVal & " val"
  Select Case Me.frameFilter
    Case 1 'TrackNumbers
      strWhere = "TrackNumbers " & strVal
     Case 2 'Date StrDate
      strWhere = "strDate " & strVal
    Case 3  'ShipperName
      strWhere = "ShipperName " & strVal
    Case 4  'RecipientName
      strWhere = "RecipientName " & strVal
    Case 5  'Company
      strWhere = "Company " & strVal
    Case 6  'CompanyR
      strWhere = "CompanyR " & strVal
    Case 7  'Mobile
      strWhere = "Mobile " & strVal
    Case 8  'MobileR
      strWhere = "MobileR " & strVal
  End Select
  Debug.Print strRowSource & strWhere
  Me.List3.RowSource = strRowSource & strWhere
End Sub

Private Sub Form_Load()
    DoCmd.Maximize
End Sub

Private Sub Form_Timer()
    Me.txtTime.Value = Format(Time, "HH:mm:ss AM/PM")
End Sub

Private Sub List3_AfterUpdate()
    Me.txtSearchbox.SetFocus
End Sub

See discussion.

Access wildcard character reference - Microsoft Support

support.microsoft.com support.microsoft.com
 

Attachments

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

Users who are viewing this thread

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