SQL Not Updateable

  • Thread starter Thread starter DS
  • Start date Start date
D

DS

I keep getting an error message that DayID and MenuID are not
Updateable. The statement works fine if I take Day and Menu out so I'm
thinking that it's my syntax. The fields are all number fields. Any
help appreciated.
Thanks
DS

DoCmd.RunSQL ("UPDATE HoldSection SET HoldSection.TerminalID = " &
Forms!MenuCreatorPasteSec!TxtPStation & ", " & _
"HoldSection.DayID = " & Forms!MenuCreatorPasteSec!TxtPDay & ", " & _
"HoldSection.MenuID = " & Forms!MenuCreatorPasteSec!TxtPMenu & ";")
 
DS said:
I keep getting an error message that DayID and MenuID are not
Updateable. The statement works fine if I take Day and Menu out so
I'm thinking that it's my syntax. The fields are all number fields.
Any help appreciated.
Thanks
DS

DoCmd.RunSQL ("UPDATE HoldSection SET HoldSection.TerminalID = " &
Forms!MenuCreatorPasteSec!TxtPStation & ", " & _
"HoldSection.DayID = " & Forms!MenuCreatorPasteSec!TxtPDay & ", " & _
"HoldSection.MenuID = " & Forms!MenuCreatorPasteSec!TxtPMenu & ";")
Click to expand...

Is HoldSection a table or a query? If a query, what is the SQL of the
query?
 
DS said:
HoldSection is a Table
Click to expand...

What are the definitions of DayID and MenuID in the table? Are they
part of a unique index, such as a primary key? I don't see anything
wrong with your syntax, but I do notice that there's nothing in the SQL
statement to limit it to any subset of records, so it's going to affect
all records in the table.

What is the exact text (and number, if available) of the error message
you get?
 
Dirk said:
What are the definitions of DayID and MenuID in the table? Are they
part of a unique index, such as a primary key? I don't see anything
wrong with your syntax, but I do notice that there's nothing in the SQL
statement to limit it to any subset of records, so it's going to affect
all records in the table.

What is the exact text (and number, if available) of the error message
you get?
Click to expand...
Dirk, Out of frustration I opened a new database and imported everything
into it except that form, typed the code in all over again and behold it
worked! This happens occasionaly and I use this method when all else
fails, wh it does this I don't know, why it works, I also don't know,
but it works. I'd like to thank you for your help.
DS
 
DS said:
Dirk, Out of frustration I opened a new database and imported
everything into it except that form, typed the code in all over again
and behold it worked! This happens occasionaly and I use this method
when all else fails, wh it does this I don't know, why it works, I
also don't know, but it works. I'd like to thank you for your help.
Click to expand...

Huh. That indicates either some form of corruption, or the database was
set to read-only, or (maybe) you had had a previous crash that left an
invalid .ldb file out there. Or, of course, something completely
different that I haven't thought of. I'm glad you were able to repair
it, and you're welcome for whatever help I may have been.

Oh, one thing -- if you are getting frequent problems of this sort, and
you aren't running your database across a network, it may be that the
Name Autocorrect feature is causing it. If you have that feature
enabled (Tools -> Options... -> General tab), try disabling it and see
if the problem stops recurring.
 
hi,
DoCmd.RunSQL ("UPDATE HoldSection SET HoldSection.TerminalID = " &
Forms!MenuCreatorPasteSec!TxtPStation & ", " & _
"HoldSection.DayID = " & Forms!MenuCreatorPasteSec!TxtPDay & ", " & _
"HoldSection.MenuID = " & Forms!MenuCreatorPasteSec!TxtPMenu & ";")
Click to expand...
Using CurrentDb.Execute SQL, dbFailOnError may provide more information
if an error occurs.


mfG
--> stefan <--
 
DS said:
Dirk, Out of frustration I opened a new database and imported everything
into it except that form, typed the code in all over again and behold it
worked! This happens occasionaly and I use this method when all else
fails, wh it does this I don't know, why it works, I also don't know,
but it works. I'd like to thank you for your help.
Click to expand...


In addition to Dirk's advice about NameAutoCorrect, another
potential cause of corruption in a form's module, is
modifying the code while the form is running. Make a
special effort to switch a form back to design view before
editing any of its VBA code. Along these lines, it's also a
good idea to set every form's AllowDesignChanges property to
Design View Only.
 
Dirk said:
Huh. That indicates either some form of corruption, or the database was
set to read-only, or (maybe) you had had a previous crash that left an
invalid .ldb file out there. Or, of course, something completely
different that I haven't thought of. I'm glad you were able to repair
it, and you're welcome for whatever help I may have been.

Oh, one thing -- if you are getting frequent problems of this sort, and
you aren't running your database across a network, it may be that the
Name Autocorrect feature is causing it. If you have that feature
enabled (Tools -> Options... -> General tab), try disabling it and see
if the problem stops recurring.
Click to expand...
Thanks Dirk,
It's on. So now I turned it off...just for good measure.
DS
 
Stefan said:
hi,


Using CurrentDb.Execute SQL, dbFailOnError may provide more information
if an error occurs.


mfG
--> stefan <--
Click to expand...
Thats Interesting...I'm using almost all SQL now with unbound forms and
it would be nice to know the difference between.
DoCmd.RunSQL()
CurrentDb.Execute SQL
and I think there is a 3rd one.
Thanks Stefan
DS
 
Marshall said:
In addition to Dirk's advice about NameAutoCorrect, another
potential cause of corruption in a form's module, is
modifying the code while the form is running. Make a
special effort to switch a form back to design view before
editing any of its VBA code. Along these lines, it's also a
good idea to set every form's AllowDesignChanges property to
Design View Only.
Click to expand...
Thanks Marsh, some more good advice!
DS
 
hi,
Thats Interesting...I'm using almost all SQL now with unbound forms and
it would be nice to know the difference between.
DoCmd.RunSQL()
CurrentDb.Execute SQL
Click to expand...
I had some raceconditions using DoCmd.RunSQL, cause DoCmd.RunSQL
returned without having written the updated date completly. So the data
was not available in the next statement.


mfG
--> stefan <--
 
Stefan said:
hi,


I had some raceconditions using DoCmd.RunSQL, cause DoCmd.RunSQL
returned without having written the updated date completly. So the data
was not available in the next statement.


mfG
--> stefan <--
Click to expand...
Thats a little scary, I'll have to watch out for that.
Thanks
DS
 
DS said:
it would be nice to know the difference between.
DoCmd.RunSQL()
CurrentDb.Execute SQL
Click to expand...

RunSQL will resolve control references in the SQL string, will prompt
for parameters, and will display warning messages if you have them
turned on. The .Execute method won't.

If you use the .Execute method, your code can get a count of records
affected by the action query. If you use RunSQL, it can't.
 
Dirk said:
RunSQL will resolve control references in the SQL string, will prompt
for parameters, and will display warning messages if you have them
turned on. The .Execute method won't.
If you use the .Execute method, your code can get a count of records
affected by the action query. If you use RunSQL, it can't.
Click to expand...
Thanks Dirk, makes things even clearer. Depends on what you want to do
I guess!
DS
 
Back
Top