Hi... Updateable Query Error with a Subquery

[imnewtoaccess]

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

[cyberdwarf]

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

Dim MaxVal As int
MaxVal = DMax("Batch", "ac_online_scanned_tkts_1")

You can now use this value instead of the sub-query

Dim Sql as String
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

[imnewtoaccess]

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

Thanks again.