Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Record not updatable

    Hi all,

    I am trying to execute the following :

    mySQL = "UPDATE Cams_Piin_Data1 " & _
    "SET Cams_Piin_Data1.GOVT_EXEC_DATE = #" & vGovExeDate & "#" & _
    "WHERE Cams_piin_Data1.piin_code = '" & vpiin_combo & "'"

    DoCmd.RunSQL mySQL

    the execution of this stems from an After Update event on a form where a date is entered into the field "GOVT_EXEC_DATE " and I want that date to populate a coresponding field in a different table also called "GOVT_EXEC_DATE "... the "vGovExeDate" is just a variable I used to capture the date entered on the form and the "vpiin_combo " is a variable I used to capture the field that relates the records in both tables ... The form is based on a table called Cams_Procur_Data1 and the record I am trying to modify based on the SQL is in Cams_Piin_Data1

    I get an error where a box pops up looking for a parameter value for Cams_Piin_Data1.Govt_Exec_Date and then if I enter a date or leave blank and click "OK" I get an error that says the record is not updateable

    Can anyone see anything I am overlooking ?
    Last edited by mcneilkm; 2014-12-04 at 08:45.
    Kevin

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The error about the record not being updateable is typically because Access thinks you don't have a primary key on the table when you are running an update against a single table. Is your table stored in an Access back-end, or is it a linked table stored in another database such as SQL Server, Oracle or mySQL? When I hit problems like this I usually try to debug the query using the designer, as I never trust my SQL coding skills, and once I have it working, then I put it into VBA with constants and test it, and once that works then I plug in the parameters. Another challenge with this sort of thing is getting the quotes correct - it appears you are using double quotes and single quotes in some cases. Another trick is to run the query in debug mode and see what you are getting by stepping through the procedure to the point where the SQL has been constructed, printing the SQL in the immediate mode, and then pasting it into the SQL Designer and see what you are getting. Hope one of more of these ideas help.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    The usual reason for a parameter box popping up is that you've misspelled a table or a field name.
    Best thing to do is to use "debug.print mySQL" before you run the query to show exactly the contents of the sql string. Sometimes pasting that into a query and running it will give you a more detailed explanation of problems if they occur.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you both... learning a lot, especially how to spell... turns out after reading your replies I went back to the table where where the update is supposed to occur and I realized that I left the "C" off of the "EXEC" part of the name when I created the table, which actually explains several other issues I was having as well... Again thank you, this lounge is truly a great community
    Kevin

Posting Permissions

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