Results 1 to 7 of 7
  1. #1
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146

    VBA for entering a variable in an UPDATE Query


    This works:

    Code:
    DoCmd.RunSQL "UPDATE tblEquipment SET tblEquipment.EquipPic =  ""D:\ACCESS\EQUIPMENT\321SHARK_2021-08-21_PIC.jpg""" & _"WHERE (((tblEquipment.EquipID)=1));" & ""
    but I want to replace ""D:\ACCESS\EQUIPMENT\321SHARK_2021-08-21_PIC.jpg""" with a variable. I've had enough of trial and error. Thanks.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm surprised that works as you should have a space before WHERE


    This is a simpler version of your existing code:
    Code:
    DoCmd.RunSQL "UPDATE tblEquipment SET tblEquipment.EquipPic = 'D:\ACCESS\EQUIPMENT\321SHARK_2021-08-21_PIC.jpg'" & _
    " WHERE tblEquipment.EquipID=1;"
    Or better still:
    Code:
    CurrentDb.Execute "UPDATE tblEquipment SET tblEquipment.EquipPic = 'D:\ACCESS\EQUIPMENT\321SHARK_2021-08-21_PIC.jpg'" & _
    " WHERE tblEquipment.EquipID=1;", dbFailOnError
    If your string variable is called strText then you would use


    Code:
    CurrentDb.Execute "UPDATE tblEquipment SET tblEquipment.EquipPic = '" & strText & "'" & _
    " WHERE tblEquipment.EquipID=1;", dbFailOnError
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Code:
    Dim strSQL AS String
    strSQL =  "UPDATE tblEquipment SET tblEquipment.EquipPic = '" & MyVariable & "' WHERE tblEquipment.EquipID=1" 
    Debug.Print strSQL
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    You made my day. Thanks.

    I chose to use this:

    Code:
            gstrFilePath = gfncPickFile()
         
            DoCmd.SetWarnings False
            DoCmd.RunSQL "UPDATE tblEquipment SET tblEquipment.EquipPic =  '" & gstrFilePath & "'" & _
            "WHERE (((tblEquipment.EquipID)=1));" & ""
            DoCmd.SetWarnings True
    That way I don't get a message that the record is going to change.

    Can you explain and/or point me to a resource that explains ' " & gstrFilePath & " ' "

    Thanks so much!

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Using CurrentDb.Execute means there is no need to set warnings false as no warning messages occur. Adding dbFailOnError ensures error messages occur if the code fails for any reason.

    You still need to add a space before WHERE and the final & "" is unnecessary
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    mainerain is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Location
    Maine, USA
    Posts
    146
    Quote Originally Posted by isladogs View Post
    Using CurrentDb.Execute means there is no need to set warnings false as no warning messages occur. Adding dbFailOnError ensures error messages occur if the code fails for any reason.

    You still need to add a space before WHERE and the final & "" is unnecessary
    Thanks for the follow through. It works with or without the space you speak of.

    Code:
            gstrFilePath = gfncPickFile()
    
            CurrentDb.Execute "UPDATE tblEquipment SET tblEquipment.EquipPic =  '" & gstrFilePath & "'" & _
            "WHERE (((tblEquipment.EquipID)=1));", dbFailOnError
    Success just feels good

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'll just chime in here and add that I almost always build a SQL string into a separate variable then use Debug.Print to examine it when it goes wrong.
    Although it's slightly more typing it will speed up your understanding of how the strings get concatenated. As an example
    Code:
      Dim strSQL As String
        
        gstrFilePath = gfncPickFile()
    
    
        strSQL = "UPDATE tblEquipment SET tblEquipment.EquipPic =  '" & gstrFilePath & "'"
        strSQL = strSQL & " WHERE tblEquipment.EquipID = 1;"
        
        Debug.Print strSQL
        
        CurrentDb.Execute strSQL, dbFailOnError
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 4
    Last Post: 06-11-2017, 11:48 AM
  2. Replies: 5
    Last Post: 02-16-2014, 02:50 PM
  3. Entering Data to update all tables.
    By mike02 in forum Forms
    Replies: 19
    Last Post: 06-06-2013, 09:06 AM
  4. Update Combo Box after entering new info
    By justgeig in forum Programming
    Replies: 13
    Last Post: 06-07-2012, 04:24 PM
  5. Passing a value from a variable to an update query
    By MUKUDU99 in forum Programming
    Replies: 0
    Last Post: 08-24-2008, 11:14 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