Building a VBA Global Keyword Search Box.

[DigitalAdrenaline]

Hi, No doubt this question would've been asked a million times before, and sorry for asking it again. I'd like to build a global keyword search box from VBA, into my form relating to numerous field records. Just wondering if someone might point me to an excellent tutorial for creating this. Thanks.

[CJ_London]

just use multiple OR's

me.filter="Fld1='" & strSearch & "' OR Fld2='" & strSearch & "' OR......

[DigitalAdrenaline]

Hi Guys, have tried to give this VBA a test run but I'm clearly out of my depth for the moment. I'm attempting to copy from a video I'm watching but am missing some important steps. Just wondering if you guys might run an eye over my code and see if anything glaringly stand out. Thanks.

Private Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT AssetNameFK"
"WHERE (AssetNameFK) LIKE '*"& Me.txtKeywords &"*'"
End Sub

[CJ_London]

personally I leave * out, use Like but instruct the user how to use asterisks (or other wildcards) when required. Most users know the beginning of something and using an initial * prevents the use of indexing so the query will be slow. Use the * suffix if you want.

me.filter="Fld1 Like '" & strSearch & "*' OR Fld2 Like '" & strSearch & "*' OR...…

[DigitalAdrenaline]

Thanks Ajax, does the me.filter sit beneath SQL = "SELECT AssetNameFK" ?

The VBA I'm using is in the cmdSearch Button which is meant to read a Search textbox.

[orange]

You may get some ideas from this Modified Universal Search.

[DigitalAdrenaline]

Thanks Orange, I'm close to getting this except for a yellow highlighted error in my code that's popping up. I'll get it right soon enough. It seems looking at different videos, people appear to be using slightly different syntax which is what's losing me. I've got an Access 2010 bible. I'll think I'll just read it tonight. The answer may jump out there. This project I'm doing is exiting, but man, it's a real labour of love. Cheers, orange.

[DigitalAdrenaline]

Ok, Here's what I ran, which wiped all my records. That was a neat trick.

Private Sub btnSearch_Click()
Dim SQL As String

SQL = "Me.Filter = AssetNameFK where AssetNameFK LIKE '*"& Me.txtSearch &"*'"
me.filterOn= True

End Sub

[orange]

You do your testing in a test environment with a test database.

How exactly did it wipe your records?
As requested previously, can you post a copy of you database with only a few records in tables. You will get more focused responses when readers can have hands on to test/resolve issues.

You have a lot of posts on various subjects -can you tell us more about your project?

[CJ_London]

all you need is

Private Sub btnSearch_Click()

    Me.Filter = "AssetNameFK LIKE '*"& Me.txtSearch &"*'"
    me.filterOn= True

End Sub

or if you are modifying your form recordsource

Private Sub btnSearch_Click()

    Me.RecordSource = "SELECT AssetNameFK FROM sometableorother WHERE AssetNameFK LIKE '*"& Me.txtSearch &"*'"

End Sub

[DigitalAdrenaline]

All 800+ record rows and data vanished. When I typed in a new record, the RecrodID started at 891. So, I deleted the RecordID row, created a replacement one, and took me back to a Record 1 position. I have a copy of all my records in Excel anyway to import back into Access and all my FK tables are still In tact with their data.

[orange]

Any info on the request to post a copy of the database with only a few records ineach table?

[DigitalAdrenaline]

Thanks Ajax, I'm extremely new to VBA conventions and logic and am madly reading the Excel Bible, VBA chapter. I can grasp the logic of flowing tasks one after the other. I'm just in the dark with what commands and properties do what, and relate to what db objects. That's just going to be an ongoing learning process. But VBA is something I want to be competent with as it's really the game-changer in mastering Access.

I'm coming across all these great database tweaks I'm wishing to incorporate into my project but are ahead of my learning curve at the moment.

Orange, did I see in one of your previous posts on how to bundle up a db for posting?

[DigitalAdrenaline]

Hi Ajax, I tested your code above but with no result.

Me.Filter = "AssetNameFK LIKE '*"& Me.txtSearch &"*'"
me.filterOn= True

End Sub.

My AssetName Textbox recordsource is linked to AssetNameFK which has two fields:
1: unique Autonumber
2: AssetName

In my frmTradeEntry. AssetNameFK, I've selected to show column 2 to display the AssetName. Could this be affecting why I'm seeing no result? Screenshot below.

[CJ_London]

I don't understand what you are saying, textboxes do not have recordsources. from your description, you should be seeing something if the assetname field is populated. I'm assuming the recordsource to the form is whatever table the assetnameFK field is in.

However I suspect what is happening you are entering something like 'IBM' or 'Apple' in txtSearch - but then trying to apply it to the assetnameFK which is presumably a number.