Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2016
    Posts
    4

    Problem using DoCmd.RunSQL to use variable in an INSERT Statement

    Help! I'm a newbie to MS Access 2010 and have been running SQL script which successfully defines and sets a string variable, but then fails to use that variable in a DoCmd.RunSQL statement.



    I have created a table called PARAMS and within that Table a field called Account. The statement is as follows

    DoCmd.RunSQL "INSERT INTO PARAMS ([account]) values (myfilename ) "

    I am invited to enter a parameter value for myfilename despite that variable having been populated with the string "BAT" by previous code

    I have also tried to use the following statement

    DoCmd.RunSQL "INSERT INTO PARAMS ([account]) select (" & MyFilename & " ) "

    but again I am invited enter a parameter value

    Help!!!!!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't need vb to run this. Build a query then run it. The query won't get syntax wrong.

    String variables MUST have quotes around them...
    DoCmd.RunSQL "INSERT INTO PARAMS ([account]) select ('" & MyFilename & "' ) "

  3. #3
    Join Date
    Sep 2016
    Posts
    4
    thats fantastic! Thanks that works!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cleaner Code with DoCmd.RunSQL Insert into
    By mduplantis in forum Programming
    Replies: 33
    Last Post: 04-30-2015, 07:49 PM
  2. Replies: 3
    Last Post: 01-27-2015, 12:25 PM
  3. Using string variable in DoCmd.RunSQL
    By dcdimon in forum Programming
    Replies: 4
    Last Post: 05-08-2014, 09:40 AM
  4. Replies: 6
    Last Post: 02-24-2014, 09:26 AM
  5. problem with my DoCmd.RunSQL statement
    By cgclower in forum Access
    Replies: 1
    Last Post: 07-24-2011, 06:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums