Access 2016 Search Button on Form

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 942 Views - Last Post: 08 July 2019 - 02:18 PM Rate Topic: -----

#1 duffyt60   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-July 19

Access 2016 Search Button on Form

Posted 08 July 2019 - 05:50 AM

I am using a Search Button on a MS-Access 2016 Form. However, it searches for all information in the selected field. I am wanting to have it search for any part of data in the selected search field.

Private Sub cmdSearch_Click()
  Dim myTxtSearch As String
  myTxtSearch = "[Full_Name]= %1" & Me.TxtSearch & "%1"
  myTxtSearch = Replace(myTxtSearch, "%1", Chr(34))

   If IsNull(TxtSearch) = False Then
    Me.Recordset.FindFirst myTxtSearch
    Me.TxtSearch = Null
    If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me.TxtSearch = Null
    End If
End If
 
End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: Access 2016 Search Button on Form

#2 maceysoftware   User is offline

  • Member Title
  • member icon

Reputation: 379
  • View blog
  • Posts: 1,615
  • Joined: 07-September 13

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 06:24 AM

You can use the like operator?

https://support.offi...29-eef18358e931
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,894
  • Joined: 12-June 08

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 06:51 AM

'LIKE' key word and surround with '%'.
Was This Post Helpful? 0
  • +
  • -

#4 duffyt60   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-July 19

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 10:49 AM

It looks like the "Like" code is for SQL and I'm using VBA. Also, with the "Like" code can I just use a wildcard to search for any text rather than specific text?

View Postmaceysoftware, on 08 July 2019 - 06:24 AM, said:

You can use the like operator?

https://support.offi...29-eef18358e931

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,894
  • Joined: 12-June 08

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 10:52 AM

Access is a pretty solid representation of SQL. Querying data from it should be more SQL like than not.
Was This Post Helpful? 0
  • +
  • -

#6 duffyt60   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-July 19

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 11:04 AM

So where in my code would I add the "LIKE" operator?

Private Sub cmdSearch_Click()
  Dim myTxtSearch As String
  myTxtSearch = "[Full_Name]= %1" & Me.TxtSearch & "%1"
  myTxtSearch = Replace(myTxtSearch, "%1", Chr(34))

   If IsNull(TxtSearch) = False Then
    Me.Recordset.FindFirst myTxtSearch
    Me.TxtSearch = Null
    If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me.TxtSearch = Null
    End If
End If
 
End Sub



View Postduffyt60, on 08 July 2019 - 10:49 AM, said:

It looks like the "Like" code is for SQL and I'm using VBA. Also, with the "Like" code can I just use a wildcard to search for any text rather than specific text?

View Postmaceysoftware, on 08 July 2019 - 06:24 AM, said:

You can use the like operator?

https://support.offi...29-eef18358e931

Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,894
  • Joined: 12-June 08

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 11:10 AM

Typically I use something along the lines of:

WHERE myColumn LIKE "%blahblahtext%"


Like operator's wild cards..
https://bettersoluti...ba/sql/like.htm



Also seen here:
https://bytes.com/to...ccess-2016-form
Was This Post Helpful? 0
  • +
  • -

#8 duffyt60   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-July 19

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 12:31 PM

If I use the "LIKE" operator what do I do with the rest of the current myTxtSearch = Replace(myTxtSearch, "%1", Chr(34)) code and below, just remove it?

mod: removed giant quote

This post has been edited by modi123_1: 08 July 2019 - 12:36 PM
Reason for edit:: Removed giant quote

Was This Post Helpful? 0
  • +
  • -

#9 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,894
  • Joined: 12-June 08

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 12:37 PM

You do not need to blindly quote posts. I removed the giant quote.

Where is this VBA code located?
Was This Post Helpful? 0
  • +
  • -

#10 duffyt60   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-July 19

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 01:05 PM

View Postmodi123_1, on 08 July 2019 - 12:37 PM, said:

You do not need to blindly quote posts. I removed the giant quote.

Where is this VBA code located?


I have placed the code in the On Click button on a Form in Access 2016. I have also redone the code to look like this now.

Private Sub cmdSearch_Click()
Dim strsearch As String
Dim strText As String
strText = Me.TxtSearch.Value
strsearch = "SELECT * from Users where ((Full_Name like ""*" & strText & "*""))"
Me.RecordSource = strsearch
        
End Sub



For the txtSearch field I also added the "After Update" code of:


Private Sub txtSearch_AfterUpdate()
Call cmdSearch_Click
End Sub



This is now working. However, it leaves me at the record it found. What code would I add to have it return to the first record and if possible, give an error message if it doesn't find a match?

I had tried this code for the error message, but it doesn't work.
[code]
If IsNull(TxtSearch) = False Then
Me.Recordset.FindFirst myTxtSearch
Me.TxtSearch = Null
If Me.Recordset.NoMatch Then
MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
Me.TxtSearch = Null
End If
End If
Was This Post Helpful? 0
  • +
  • -

#11 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,894
  • Joined: 12-June 08

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 01:07 PM

I would think updating what you set 'RecordSource' to would be the best bet.
Was This Post Helpful? 0
  • +
  • -

#12 duffyt60   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-July 19

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 01:25 PM

View Postmodi123_1, on 08 July 2019 - 01:07 PM, said:

I would think updating what you set 'RecordSource' to would be the best bet.


So change the Me.RecordSource = strsearch to Me.RecordSource = [Full_Name] where [Full_Name] is the field name the search is working from?
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,894
  • Joined: 12-June 08

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 01:26 PM

What happens when you try it out?
Was This Post Helpful? 0
  • +
  • -

#14 duffyt60   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 08-July 19

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 01:32 PM

View Postduffyt60, on 08 July 2019 - 01:25 PM, said:

View Postmodi123_1, on 08 July 2019 - 01:07 PM, said:

I would think updating what you set 'RecordSource' to would be the best bet.


So change the Me.RecordSource = strsearch to Me.RecordSource = [Full_Name] where [Full_Name] is the field name the search is working from?


It goes to the Users table records but doesn't let me search any longer for a user in the table.
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 15215
  • View blog
  • Posts: 60,894
  • Joined: 12-June 08

Re: Access 2016 Search Button on Form

Posted 08 July 2019 - 01:33 PM

What doesn't let you search? Your code? Again - not quite enough information here to get a bead on what is really happening.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2