Results 1 to 6 of 6
  1. #1
    joycesolomon is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    11

    vba code help - Currentdb.execute

    hi all,



    I need some help. I am not sure why or what is wrong with my code but, the second Currentdb.execute only workes if i click on the button twice. Can someone help me to point out what i am doing wrong or how to consolidate all these to one currentdb.execute?

    Code:
    CurrentDb.Execute "UPDATE Project " _
                        & "SET [Project Status] = 'O&M Review' " _
                        & "Where [Project Number] = '" & Me.Project_Number & "';"
      
      CurrentDb.Execute "UPDATE Approve_Close " _
                        & "SET [PR-PL] = '" & Me.PRPL & "' ,[PR-PL_DATE] = NOW()  " _
                        & "Where [Project Number] = '" & Me.Project_Number & "';"
    
        Me.Requery
    
      CurrentDb.Execute "INSERT INTO Estimates ([Project #]) VALUES ('" & Me.Project_Number & "')"
      
    

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    execute a query, not the database...
    docmd.runsql "update...

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Looks like an issue with memory. I would start with declaring some variables...

    Dim db as DAO.Database
    Dim strSQL1 as string
    Dim strSQL2 as string
    Dim strSQL3 as string

    Then instantiate
    set db = currentdb

    initialize your string variables with your SQL strings

    Then, a final step may be to call the WHERE clauses of your SQL's in a separate query and assign it to Named Saved Query Objects via QueryDefs. You could then use the query object names in your action queries.

  4. #4
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    "SET [PR-PL] = '" & Me.PRPL & "' ,[PR-PL_DATE] = NOW() "
    You must surround the Date/time fields with the # delimeter.
    CurrentDb.Execute "UPDATE Project " _
    & "SET [Project Status] = 'O&M Review' , [PR-PL] = '" & Me.PRPL & "' ,[PR-PL_DATE] =#" & NOW() & "#" _
    & "Where [Project Number] = '" & Me.Project_Number & "';"

  5. #5
    joycesolomon is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    11
    can you figure out why i am getting Syntax error

    Code:
    DoCmd.RunSQL "UPDATE [Project_Test]" _
                        & "SET [Project Status] = 'O&M Review ' , SET [PR-PL] = '" & Me.PRPL & "' ,[PR-PL_DATE] = #" & Now() & "#  " _
                        & "Where [Project Number] = '" & Me.Project_Number & "';"

  6. #6
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    DoCmd.RunSQL "UPDATE [Project_Test]" _
    & "SET [Project Status] = 'O&M Review ' , SET [PR-PL] = '" & Me.PRPL & "' ,[PR-PL_DATE] = #" & Now() & "# " _
    & "Where [Project Number] = '" & Me.Project_Number & "';"
    You need to use SET only once and separate the fields to be updated by a comma. Further, & is an access reserved word and should not be used in field names.
    Code:
          
    DoCmd.RunSQL "UPDATE [Project_Test]  SET [Project Status] = 'O&M Review' , [PR-PL] = '" & Me.PRPL & "' ,[PR-PL_DATE] = #" & Now() & "#  " _
                        & "Where [Project Number] = '" & Me.Project_Number & "';"
    Pay attention to spaces in between too.

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

Similar Threads

  1. Replies: 12
    Last Post: 01-24-2014, 02:18 PM
  2. Problems with CurrentDb.Execute "UPDATE
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 04-06-2013, 03:21 PM
  3. Update sql using currentdb.execute
    By laavista in forum Access
    Replies: 13
    Last Post: 08-15-2011, 03:51 PM
  4. How to execute Line of Code
    By jo15765 in forum Programming
    Replies: 4
    Last Post: 06-22-2011, 05:37 PM
  5. Can't get any of my code to execute!
    By blacksaibot in forum Programming
    Replies: 4
    Last Post: 03-16-2010, 08:08 AM

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