Consulting

Results 1 to 13 of 13

Thread: Help Building First Code as a Keyword Search

  1. #1
    VBAX Regular
    Joined
    Aug 2018
    Posts
    7
    Location

    Help Building First Code as a Keyword Search

    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.

  2. #2
    VBAX Regular nuclear_nick's Avatar
    Joined
    Aug 2018
    Location
    Cowlumbus
    Posts
    7
    Location
    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?
    "Nuclear" Nick
    The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.

  3. #3
    VBAX Regular
    Joined
    Aug 2018
    Posts
    7
    Location
    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?

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  5. #5
    VBAX Regular
    Joined
    Aug 2018
    Posts
    7
    Location
    Thanks OBP, I've just updated/edited my post above. Will come back shortly. Cheers.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  7. #7
    VBAX Regular
    Joined
    Aug 2018
    Posts
    7
    Location
    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/off...lter(property) and now understand that a Filter is a form based property. Now I'm starting to understand.
    Last edited by 101; 08-20-2018 at 04:33 PM.

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    "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/show...ords-into-form

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

  9. #9
    VBAX Regular
    Joined
    Aug 2018
    Posts
    7
    Location
    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.

  10. #10
    VBAX Regular nuclear_nick's Avatar
    Joined
    Aug 2018
    Location
    Cowlumbus
    Posts
    7
    Location
    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.
    "Nuclear" Nick
    The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.

  11. #11
    VBAX Regular
    Joined
    Aug 2018
    Posts
    7
    Location
    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.

  12. #12
    VBAX Regular nuclear_nick's Avatar
    Joined
    Aug 2018
    Location
    Cowlumbus
    Posts
    7
    Location
    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.
    "Nuclear" Nick
    The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.

  13. #13
    VBAX Regular
    Joined
    Aug 2018
    Posts
    7
    Location
    Cheers Nick. Appreciate that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •