Help Building First Code as a Keyword Search

[101]

Hi, I've watched a video, trying to copy it in producing a keyword search for one of my fields and am getting a little lost with the syntax. Just wondering if someone may be able to help me with this learning curve.

My data field is called AssetNameFK
I've created a text search box and a Search button to activate it.
The string I've been watching looks roughly like this:

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

This is currently not working. I've also been given the following code, but am unsure where to place this or even what it means.

me.filter="AssetNameFK Like '" & strSearch & "*'

I'm hoping someone might be able to help me clean this up. Thanks.

[nuclear_nick]

When you say 'currently not working', there could be several reasons why. What error are you receiving? Is the code you posted, including the line breaks, exactly like the code in your database?

[101]

Hi Nuclear, I should probably start from scratch. It's amazing how scant the web is for quality tutorials on learning VBA. It's skittered, ad-hoc and utterly vague. So I ripped open the Access Bible last night and headed straight to the VBA chapter. So far, very well written.

With my own code, this is the set-up I've stumbled into,

form = frmTradeEntry
fieldID = TradeNumberID
field = AssettNameFK (there are others but not important to this code.
I've created a Search Box and a button in the header of my form.

The actual code I have so far is:

Private Sub btnSearch_Click()
Dim SQL As String

SQL = "SELECT TradeNumberID, AssetNameFK where AssetNameFK LIKE '*"& Me.txtSearch &"*'"

me.txtSearch = SQL

End Sub

Actually, thinking about this, AssetNameFK has only numbers which relate back to the tblAssaetName where there are two columns, the Unique ID (number) and the field name (text). Should the VBA in this form somehow be pointing back to the tblAssetName Column 2 which is referenced to frmTradeEntry?

[OBP]

nuclear_nick, sorry to jump in to this post.
101, The Syntax in SQL statements is critical to them working and as nick has implied it should include error trapping.
The second piece of code is a completely different technique and actully uses the Form's Filter, but it also has to include switching the filter on.
I would suggest that the filter method is probably the easier one for you to learn.

[101]

Thanks OBP, I've just updated/edited my post above. Will come back shortly. Cheers.

[OBP]

101, I am in the Uk, so I am off to bed now, I hope to help you tomorrow if nick doesn't fix it for you.

[101]

Cheers OBP. Thx. have a good night.

I've amended by code to:

Private Sub btnSearch_Click()
Dim SQL As String

SQL = "Me.Filter = AssetNameFKwhere AssetNameFKLIKE '*"& Me.txtSearch &"*'"
me.filterOn= True

End Sub

I tested this with no result.

I also looked up https://docs.microsoft.com/en-us/office/vba/api/Access.Form.Filter(property) and now understand that a Filter is a form based property. Now I'm starting to understand.

[OBP]

"now understand that a Filter is a form based property", that is why using SQL is not necessary.
I suggest that you take a look at this thread

http://www.vbaexpress.com/forum/showthread.php?63345-need-help-getting-a-subset-of-records-into-form

Have a look at the search database by Allen Browne that I attached at post #5.

[101]

OBP, Thank you. That's a great VBA example to slowly work through an understand with filtering. The green text comments are terrific for understanding the individual components, their context and process. That's great work. Cheers.

[nuclear_nick]

Reading code with comments is certainly a help.

Allow me to help more with a few 'coding tricks' I would use in your situation.

First, whenever I'm using SQL in code, I set it to a string variable first. If I have an issue, I can then 'debug.print' the variable to the immediate window, which will be my SQL string, which I can then copy/paste into a new query, and see if it gives me an error or not...


Private Sub btnSearch_Click()

Dim SQL As String

SQL = "SELECT AssetNameFK"
"WHERE AssetNameFK LIKE '*"& Me.txtKeywords &"*'"

debug.print SQL
stop

me.txtKeywords = SQL

End Sub

... like the above. (If everything works out, be sure to remove the 'stop's when you have completed the code.)

The reason I suggest this, and the point to one of my questions, was that it looks like you'd be missing a space in your SQL, between the SELECT and WHERE, which would cause an issue.

[101]

Nick, that's a very helpful tip. Thanks. Rather than me watching a few videos and trying to copy the VBA, I really need to stop and think about each line and what the syntax means, and possibly post each segment of what i'm writing here for cross-proofing until it makes sense in my head. I'm getting through several pages a night reading VBA from the Access Bible. They really break the coding down nicely. I also was also corrected from another forum that my code in this instance should read:

Private Sub btnSearch_Click()


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

End Sub

I also read why Filter is important to use because it's a Form property. These little bits of knowledge make a big difference.

[nuclear_nick]

I was in that spot once, then the company I work for brought someone in once a year for a two-day VBA workshop, so I was able to ask questions, get a little more 'real world' with it, along with hands-on... I guess I learned better that way, because after that, it was like angels called out.

As we 'run into' each other around here, if I come up with anything else... you'll read it here.

[101]

Cheers Nick. Appreciate that.