Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    15

    Angry Unanswered: Syntax Error in From clause...

    I made a form where if you enter the name of a parts list in the textbox (parts_list_box) and then click a button then this will run a macro which opens a query and hopefully checks the parts list against all components in stock in a table named SF1. Then it would return to you a datasheet that shows what parts exist in the table SF1, ordered by part number. In the query i have:

    SELECT Forms![parts_form]![parts_list_box].[PART],
    Forms![parts_form]![parts_list_box].[DESCRIPTION]
    FROM Forms![parts_form]![parts_list_box]
    WHERE (((Forms![parts_form]![parts_list_box].PART) IN (SELECT PART FROM SF1)))
    ORDER BY Forms![parts_form]![parts_list_box].[PART];


    I'm trying to get information from a form textbox to be used as an argument in my from clause and further be used as a tablename so i can reference the PART field for comparison against SF1's PART field.

    When I run the above I get "Syntax Error in FROM clause error". Could someone please help me figure out how to do this? I'm going crazy! Thank you.
    -The enemy of good is better...

  2. #2
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    I doubt you can use a Form field in the FROM clause of a select statement.

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  3. #3
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: Syntax Error in From clause...

    Originally posted by supernewb
    I made a form where if you enter the name of a parts list in the textbox (parts_list_box) and then click a button then this will run a macro which opens a query and hopefully checks the parts list against all components in stock in a table named SF1. Then it would return to you a datasheet that shows what parts exist in the table SF1, ordered by part number. In the query i have:

    SELECT Forms![parts_form]![parts_list_box].[PART],
    Forms![parts_form]![parts_list_box].[DESCRIPTION]
    FROM Forms![parts_form]![parts_list_box]
    WHERE (((Forms![parts_form]![parts_list_box].PART) IN (SELECT PART FROM SF1)))
    ORDER BY Forms![parts_form]![parts_list_box].[PART];


    I'm trying to get information from a form textbox to be used as an argument in my from clause and further be used as a tablename so i can reference the PART field for comparison against SF1's PART field.

    When I run the above I get "Syntax Error in FROM clause error". Could someone please help me figure out how to do this? I'm going crazy! Thank you.
    You can use the value from the text box if you store it in a variable and then use the variable in the FROM clause of the query. It also looks like you need to restructure your query so that you return the records from your table rather than the form once you've provided input in the form.

    If you need to provide a complete list of parts to select from in the text box, you should create a separate query for this. I'm assuming that a different list of parts (the complete list) is different from the list of parts in stock.
    Regards,
    Terry

  4. #4
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: Syntax Error in From clause...

    Originally posted by supernewb
    I made a form where if you enter the name of a parts list in the textbox (parts_list_box) and then click a button then this will run a macro which opens a query and hopefully checks the parts list against all components in stock in a table named SF1. Then it would return to you a datasheet that shows what parts exist in the table SF1, ordered by part number. In the query i have:

    SELECT Forms![parts_form]![parts_list_box].[PART],
    Forms![parts_form]![parts_list_box].[DESCRIPTION]
    FROM Forms![parts_form]![parts_list_box]
    WHERE (((Forms![parts_form]![parts_list_box].PART) IN (SELECT PART FROM SF1)))
    ORDER BY Forms![parts_form]![parts_list_box].[PART];


    I'm trying to get information from a form textbox to be used as an argument in my from clause and further be used as a tablename so i can reference the PART field for comparison against SF1's PART field.

    When I run the above I get "Syntax Error in FROM clause error". Could someone please help me figure out how to do this? I'm going crazy! Thank you.
    Sorry, just read your other post and the reply. Use the recommendation from my previous post, but replace the XXX that your other post-response suggested with the variable name. You'll need to construct the query as a string in VBA to be able to use it. At run time, the variable contents will be substituted in for the variable name. It should look something like this:

    Dim XXX as String
    Dim strSQL as String

    XXX = Forms![parts_form]![parts_list_box].[PART]

    strSQL = "SELECT [" & XXX & "].[PART], " & _
    "[" & XXX & "].[DESCRIPTION] " & _
    "FROM [" & XXX & "] " & _
    "WHERE ((([" & XXX & "].[PART]) IN (SELECT PART FROM SF1))) " & _
    "ORDER BY "[" & XXX & "].[PART]";


    You can then use the string as an argument to a command object to return the values you're looking for.
    Regards,
    Terry

Posting Permissions

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