+ Reply to Thread
Results 1 to 5 of 5

Error 3141 keyword search DB

  1. #1
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    998

    Error 3141 keyword search DB

    Hi,
    I've built a db to do a keyword search on a table of data.
    All working well until I run the line Me.Recordsource = strSearch
    The error is 'The Select statement includes a reserved word or an argument that is misspelled or missing.

    I've had a good look through but I can't get past this line?
    I've used this video as a guideline & that works OK, but not for me??
    https://www.youtube.com/watch?v=YwGYPqUGJMQ

    Please Login or Register  to view this content.
    Remember you are unique, like everyone else

  2. #2
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    231

    Re: Error 3141 keyword search DB

    I'm guessing:
    + this is VBA behind a Form
    + btnsearch is a Button control on the Form
    + txt_search is a TextBox control on the Form
    + MyTable is a table with fields called: ID, name, class, car

    Notes:
    + you have an extra comma: "a.car,FROM"; with the comma there it's expecting another field name to be specified
    + 'name' is a reserved word in MS-Access, and when you build a table with this as a field you should have received a warning dialog stating: "If you use a reserved word, you may receive an error when referring to this field."
    + typically using the square brackets MyTable.[name] around field names can force them to be parsed as a field name instead of a reserved word
    + you've set the variable strtext to the TextBox's Value, but then instead of using this variable you call out to the TextBox again for it's value instead: " & Me.txt_search & "
    + the line marked with the comment "SQL runs OK," doesn't run SQL it just assigns the SQL's command text to a String variable
    + the line marked with the comment "Error appears here," is where the SQL code is actually executed
    + building the query this way leaves you open for SQL injection (probably not a big deal in this pocket-example, but you really should get in the practice of using a parameterized query instead)

    A basic solution: remove the extra comma
    Please Login or Register  to view this content.
    A better solution: parameterize the query
    + in the form design view:
    ++ open the Property Sheet(side view)
    ++ click on the Data(tab)
    ++ go to the Record Source(selection box)
    ++ click on the ...(button)
    + build the query here for the MyTable
    ++ in the criteria use the special parameters table [TempVars]![SearchCar] under the car field
    ++ click on the smaller X(button) to return from the query builder to the form designer
    ++ note, the bigger X(button) closes MS-Access, you don't want to do that
    ++ if you can't find the smaller X(button) then the keyboard short cut combo ctrl + w also works

    Please Login or Register  to view this content.
    also for this simple example; ditch the textbox & button, use a ComboBox instead where Row Source: SELECT DISTINCT car FROM MyTable ORDER BY car;
    If it'd make you feel better using my answer because of my street cred, then you can go ahead and click Add Reputation below to improve it.

  3. #3
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    998

    Re: Error 3141 keyword search DB

    Thanks Gregor y,
    I could have looked at that for hours before I noticed the comma.
    Always handy to have a second set of eyes...

  4. #4
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Version 2211 (Build 15813.20002) Win 11 Home 64 Bit
    Posts
    22,085

    Re: Error 3141 keyword search DB

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    998

    Re: Error 3141 keyword search DB

    Thanks,
    I've given an add rep.
    Why can't the page have a simple 'Mark this Post as Solved"?
    Just sayin ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 11-02-2017, 11:02 AM
  2. Search cells C1:C3000 for keyword and if exists place a keyword in A1:A3000
    By GregQuick in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-24-2015, 07:38 PM
  3. Keyword search and copy row to new sheet: search term problem
    By completenovice in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2014, 11:32 AM
  4. Replies: 6
    Last Post: 03-18-2014, 11:16 AM
  5. Replies: 1
    Last Post: 12-27-2010, 08:53 AM
  6. Keyword search
    By 4sharkfins in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-22-2010, 09:17 PM
  7. keyword search
    By Doman in forum Excel General
    Replies: 2
    Last Post: 07-20-2006, 08:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1