1

Trying to run multiple SELECT statement inside a VALUES statement of INSERT INTO. SELECT gets me the required record from the other tables but gives me "Query input must contain at least one table or query" error.

I did some research and found that I can use either use SELECT or VALUES with the INSERT INTO statement. I am looking to use SELECT subquery inside the VALUES statement because I have multiple fields in my table that takes in values from different tables and have conditions just like below in my code where it looks for an ID. Below is a part of the query that I wrote that I have limited to only 3 fields. Actual table has 9 fields. I am testing it out and trying to keep it simple here.

extPMBActID_SQL = "SELECT * FROM PMB;"
Set rsDB = CurrentDb.OpenRecordset(extPMBActID_SQL, dbOpenDynaset)

instRptGen_SQL = "INSERT INTO testTable (ID, arbName, testName) VALUES ('" & _
    Left(rsDB.Fields("ActivityID"), 2) & "', '" & rsDB.Fields("ActivityName") & _
    "', (SELECT AreaName FROM Area WHERE AreaID = " & _
    Left(rsDB.Fields("ActivityID"), 2) & "))"

db.Execute instRptGen_SQL, dbFailOnError

The first parameter in the VALUES statement fetches the correct record and also the one inside the SELECT subquery. I am not sure why it is not finding the table.

I looked around on how to use multiple SELECT inside VALUES but found nothing and having this subquery can help me reduce a major chunk of code into just a line and also save me from using tons of variables.

braX
  • 11,506
  • 5
  • 20
  • 33
PiBoye
  • 33
  • 1
  • 6
  • https://stackoverflow.com/questions/35021840/access-insert-with-nested-select Its your nested SELECT statement causing the issue. Try replacing the nested SELECT with `DFirst("AreaName","Area","AreaID = " &LEFT(rsDB.Fields("ActivityID"),2))` – Mike Nov 05 '19 at 16:08
  • @Mike It gives me 'rsDB.Fields' undefined function error.. I had t change some quotations in your expression. Below is what I tried: ` instRptGen_SQL = "INSERT INTO testTable (ID, arbName, testName) VALUES ('" & Left(rsDB.Fields("ActivityID"), 2) & "', '" & rsDB.Fields("ActivityName") & "', DFirst('AreaName','Area','AreaID = ' &LEFT(rsDB.Fields('ActivityID'),2)))" ` – PiBoye Nov 05 '19 at 16:29
  • @Mike I think I am making some quotation errors in my expression. – PiBoye Nov 05 '19 at 16:36
  • Missing the open double quotes for the "where" critiera in the DFirst and some closing quotes. Try ` instRptGen_SQL = "INSERT INTO testTable (ID, arbName, testName) VALUES ('" & Left(rsDB.Fields("ActivityID"), 2) & "', '" & rsDB.Fields("ActivityName") & "', DFirst('AreaName','Area','AreaID = '" & Left(rsDB.Fields("ActivityID"), 2) & "'))"` – Mike Nov 05 '19 at 18:25
  • @Mike Got Syntax error missing operator but managed to fixed that. When I run it says Error 3464 with message saying Unknown. Does it mean that there is data type mismatch? Is there anyway of debugging this? – PiBoye Nov 05 '19 at 20:07
  • instRptGen_SQL = "INSERT INTO testTable (ID, arbName, testName) VALUES ('" & Left(rsDB.Fields("ActivityID"), 2) & "', '" & rsDB.Fields("ActivityName") & "', DFirst('AreaName','Area','AreaID = " & Left(rsDB.Fields("ActivityID"), 2) & "'))" This I think gets the Where condition in an integer form and it needs to in String. – PiBoye Nov 05 '19 at 20:11
  • In your edited script you removed the single quote from the 'AreaID = `'`". Those are vital to the script functioning. As far as debugging, not in a good way. Usually I break it out and test each piece individually but working with strings and building querys with quotes has always been a huge hassle. What you CAN do is step through to the line it is building the query string and once there, try different options in the immediate window using `? yourstring with quotes and all ` to see what the actual output is. Once you find the correct formatting you'll be golden. – Mike Nov 05 '19 at 20:31

0 Answers0