'Query must use an updateable query'

[Guest]

Hi,

I'm trying to not create temporary tables and nest a query as the value of a
update, sql as below.

UPDATE [Vessel Records] SET [Vessel Records].RecNum = ( SELECT max( [VESSEL
RECORDS].RecNum) FROM [VESSEL RECORDS] )
WHERE ([Vessel Records].RecNum Is Null);

I don't want to use a AutoNumber field and have another 5 update queries to
do something very similar.

Can anyone helps?
Matt

 

[Guest]

To answer my own question, see solution:

UPDATE [Vessel Records] SET [Vessel Records].RecNum = DMax("[vessel
records].RecNum","vessel records","[vessel records].recnum is not null")+1
WHERE [vessel records].recnum is null;

 

[OfficeDev18 via AccessMonster.com]

Rule of thumb: any query or sub-query that uses any aggregate function - such
as the Max() function you're using - renders that query, or any higher-tier
query, as non-updateable. As such, it cannot be used in Update or Delete
queries.

Sam

To answer my own question, see solution:

UPDATE [Vessel Records] SET [Vessel Records].RecNum = DMax("[vessel
records].RecNum","vessel records","[vessel records].recnum is not null")+1
WHERE [vessel records].recnum is null;