By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,330 Members | 1,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,330 IT Pros & Developers. It's quick & easy.

Hi... Updateable Query Error with a Subquery

P: 19
Hello everybody,

I am completely new to access and am facing a problem.

I am trying to run the following query from the query designer ( i am using access 2000 and the table that i am trying to update is created by me ) and I am getting the error 'Operation must use an updateable query'

UPDATE ac_online_scanned_tkts_1 SET ac_online_scanned_tkts_1.Batch = ( Select Max (temp.batch) as t1 from ac_online_scanned_tkts_1 As temp )
WHERE ac_online_scanned_tkts_1.batch = 0;

What i want to do is update the field Batch with the current max value of Batch in the same table plus 1.

Any help and response is appreciated !!!
Thanks in advance
Jun 6 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 218
Hello everybody,

I am completely new to access and am facing a problem.

I am trying to run the following query from the query designer ( i am using access 2000 and the table that i am trying to update is created by me ) and I am getting the error 'Operation must use an updateable query'

UPDATE ac_online_scanned_tkts_1 SET ac_online_scanned_tkts_1.Batch = ( Select Max (temp.batch) as t1 from ac_online_scanned_tkts_1 As temp )
WHERE ac_online_scanned_tkts_1.batch = 0;

What i want to do is update the field Batch with the current max value of Batch in the same table plus 1.

Any help and response is appreciated !!!
Thanks in advance
You could try using the DMAX function to grab the max value first, eg
Expand|Select|Wrap|Line Numbers
  1. Dim MaxVal As int
  2. MaxVal = DMax("Batch", "ac_online_scanned_tkts_1")
You can now use this value instead of the sub-query
Expand|Select|Wrap|Line Numbers
  1. Dim Sql as String
  2. Sql = "UPDATE ac_online_scanned_tkts_1 SET ac_online_scanned_tkts_1.Batch = " & MaxVal & " WHERE ac_online_scanned_tkts_1.batch = 0"
Don't know if this gets you any nearer...

Steve
Jun 6 '07 #2

P: 19
Thanks for the reply. I did use the DMax function, but directly in the query, and it worked fine.

Thanks again.
Jun 14 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.