Hello,
In order to learn more about macros I’m trying to execute an SQL statement from a button.
I stripped down a database to the absolute minimum to explain my problem.
In the form frm_prefs I created a button to update the field InvoiceYear. The last line in the macro seems to be wrong and I’ve been looking around for a solution without luck.
Maybe someone here can point me in the right direction?
Thanks.
Hello,
There are multiple problems.
You are trying to update a record which doesn’t exist. Preferences table where ID = 1
There is no error checking in the code to help detect problems. See answer in this post for direction → CalcBASIC+Non-registred HSQLDB : How to deal with .odb.lck ?
In your SQL statement:
""PefID"" = 1
should be:
""PrefID"" = 1
When using a variable within the SQL it is not within the string but joined to it. Also helpful to construct the string separate from the execution. Some strings can get quite long.
Original:
oStatement.execute("UPDATE ""Preferences"" SET ""InvoiceYear"" = 'InputVal' WHERE ""PefID"" = 1 ")
Repaired:
sSQL = "UPDATE ""Preferences"" SET ""InvoiceYear"" = '" & InputVal & "'WHERE ""PrefID"" = 1 "
oStatement.execute(sSQL)
Tested as OK with these changes made.