Record not updatable

Topic

New Reply

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 ?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote