SQL Not Updateable

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 & ";")
 
D

Dirk Goldgar

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?
 
D

Dirk Goldgar

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?
 
D

DS

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
 
D

Dirk Goldgar

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.
 
S

Stefan Hoffmann

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 <--
 
M

Marshall Barton

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.
 
D

DS

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
 
D

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
 
D

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
 
S

Stefan Hoffmann

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 <--
 
D

DS

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
 
D

Dirk Goldgar

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.
 
D

DS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DCount Syntax 6
SQL Not Updating 2
SQL UPDATE Problem 1
SQL Date/Time Update 2
If Statement Problem 1
SQL UPDATE on the fly 5
SQL Syntax Error 3
Operation must use an updateable query 3

Top