Update Query containing Select statement

  • Thread starter Thread starter W Dean
  • Start date Start date
W

W Dean

Hi,

Can anyone tell in why this won't work or what the correct syntax should be?

UPDATE Calender
SET
Calender.Approved = ([aUserName] IN (SELECT Users.UserName FROM Users WHERE
(Users.UserName=[aUserName]) AND (Users.AccessLevel = 1))) AND
([aApproved]);

Basically, I want to set the value of Approved, based on whether or not the
supplied username is in the administrators group, if not approved is set to
false, if yes its set to the boolean value contained in [aApproved]

I get the error message 'query not updateable'

W Dean
 
The following MIGHT work.

UPDATE Calender
SET
Calender.Approved =
Exists(Select Users.UserName
FROM Users
WHERE Users.UserName=[aUserName]
And Users.AccessLevel =1)
AND aApproved = True

What is aApproved? Is it a field in Calendar table? Or is it a parameter you
want to input? Or a variable or a reference to a control? If one of the latter
two then this will not work as this is not the way to reference a control or a
variable.
 
Hi,

Approved is a boolean field in the calender table. [aApproved] is the
updated value for it.

w dean


John Spencer (MVP) said:
The following MIGHT work.

UPDATE Calender
SET
Calender.Approved =
Exists(Select Users.UserName
FROM Users
WHERE Users.UserName=[aUserName]
And Users.AccessLevel =1)
AND aApproved = True

What is aApproved? Is it a field in Calendar table? Or is it a parameter you
want to input? Or a variable or a reference to a control? If one of the latter
two then this will not work as this is not the way to reference a control or a
variable.


W said:
Hi,

Can anyone tell in why this won't work or what the correct syntax should be?

UPDATE Calender
SET
Calender.Approved = ([aUserName] IN (SELECT Users.UserName FROM Users WHERE
(Users.UserName=[aUserName]) AND (Users.AccessLevel = 1))) AND
([aApproved]);

Basically, I want to set the value of Approved, based on whether or not the
supplied username is in the administrators group, if not approved is set to
false, if yes its set to the boolean value contained in [aApproved]

I get the error message 'query not updateable'

W Dean
Click to expand...
Click to expand...
 
Hi,

I tried that but it came up with the same error message.

Approved is a boolean field in the calender table. [aApproved] is a
parameter containing the new value for approved. The Users table contains
editing rights for each user.
I'm trying to get the update to work so that: if the user entered in the
parameter [aUsername] is in the admin group then set the field to whatever
boolean value is in [aApproved] otherwise set approved to false.

w dean

John Spencer (MVP) said:
The following MIGHT work.

UPDATE Calender
SET
Calender.Approved =
Exists(Select Users.UserName
FROM Users
WHERE Users.UserName=[aUserName]
And Users.AccessLevel =1)
AND aApproved = True

What is aApproved? Is it a field in Calendar table? Or is it a parameter you
want to input? Or a variable or a reference to a control? If one of the latter
two then this will not work as this is not the way to reference a control or a
variable.


W said:
Hi,

Can anyone tell in why this won't work or what the correct syntax should be?

UPDATE Calender
SET
Calender.Approved = ([aUserName] IN (SELECT Users.UserName FROM Users WHERE
(Users.UserName=[aUserName]) AND (Users.AccessLevel = 1))) AND
([aApproved]);

Basically, I want to set the value of Approved, based on whether or not the
supplied username is in the administrators group, if not approved is set to
false, if yes its set to the boolean value contained in [aApproved]

I get the error message 'query not updateable'

W Dean
Click to expand...
Click to expand...
 
Hi,

I think access thinks that the SELECT after the 'SET field =' is an
aggregate query or something and hence marks it as not updateable. So I
tried using

UPATE Calender, (SELECT Users.AccessLevel WHERE Users.UserName =
[aUserName]) AS U1
SET
Calender.Approved = [aApproved] AND (U1.AccessLevel = 1)

which worked but Access kept 'optimizing' it causing a syntax error if I
reopened it.

And Finally, I've managed to come up with

UPDATE Calender, Users
SET
Calender.Approved = [aApproved] AND (Users.AccessLevel = 1)
WHERE
(Users.UserName = [aUserName]) AND .... other criteria

which sort of looks right.

It only took me about 1 day ... :-(

w dean

John Spencer (MVP) said:
Sorry, I'm stuck at this point. I seem to remember another thread where you
posed this question and I believe you found a solution that works.

W said:
Hi,

I tried that but it came up with the same error message.

Approved is a boolean field in the calender table. [aApproved] is a
parameter containing the new value for approved. The Users table contains
editing rights for each user.
I'm trying to get the update to work so that: if the user entered in the
parameter [aUsername] is in the admin group then set the field to whatever
boolean value is in [aApproved] otherwise set approved to false.

w dean

John Spencer (MVP) said:
The following MIGHT work.

UPDATE Calender
SET
Calender.Approved =
Exists(Select Users.UserName
FROM Users
WHERE Users.UserName=[aUserName]
And Users.AccessLevel =1)
AND aApproved = True

What is aApproved? Is it a field in Calendar table? Or is it a
Click to expand...
parameter
you
want to input? Or a variable or a reference to a control? If one of
Click to expand...
the
latter
two then this will not work as this is not the way to reference a
Click to expand...
control
or a
variable.


W Dean wrote:

Hi,

Can anyone tell in why this won't work or what the correct syntax
Click to expand...
should
be?
UPDATE Calender
SET
Calender.Approved = ([aUserName] IN (SELECT Users.UserName FROM
Click to expand...
Users
WHERE
(Users.UserName=[aUserName]) AND (Users.AccessLevel = 1))) AND
([aApproved]);

Basically, I want to set the value of Approved, based on whether or
Click to expand...
not
the
supplied username is in the administrators group, if not approved is
Click to expand...
set
to
false, if yes its set to the boolean value contained in [aApproved]

I get the error message 'query not updateable'

W Dean
Click to expand...
Click to expand...
Click to expand...
 
Back
Top