No Current Record

[Kenln]

Help. This has been working but now... I get "No Current Record" which I am not even what it is.

Update tbl_Job_Access 
Set tbl_Job_Access.bDelete_Job = 1 
Where (
tbl_Job_Access.Job_No= (
Select tbl_Job_Access.Job_No 
From tbl_Job_Access 
LEFT JOIN CMSqry_JCPDSC 
ON 
tbl_Job_Access.Job_No = CMSqry_JCPDSC.Job_No 
Where ( 
(CMSqry_JCPDSC.Job_No) Is Null )
)
)

Just so you know

Select tbl_Job_Access.Job_No 
From tbl_Job_Access 
LEFT JOIN CMSqry_JCPDSC 
ON tbl_Job_Access.Job_No = CMSqry_JCPDSC.Job_No 
Where ( 
(CMSqry_JCPDSC.Job_No) Is Null )
)

Works fine.

I got this solution a couple of months back from this forum and it has been working fine now it stops.

Any help would be appreciated.

Thanks,

 

[georgedwilkinson]

Your data has changed and become ambiguous to the statement.

You should replace the "=" in the where clause with "in" and see how that works (on a backup copy, please).

 

[Kenln]

We have a winner....

But Why!!

It has been working, how did it get ambiguous? What does ambiguous, in relation to Access even mean?

 

[georgedwilkinson]

In relationship to any and all relational databases. A query will try to give you exactly the answer you ask for. When you use a sub-query, if it can do it, it will. But if that sub-query returns more than 1 row, you cannot use an operator that will only work with a single value.

For instance:

if 1 = 1

only compares a single value to a single value.

But:

if 1 = (5, 7, 2, 1, 9)

Ooops, you're now comparing a single value to a bunch of different values...the "=" operator won't do this.

So in SQL, we have the operator "in" for this purpose. "In" essentially means "see if this value is located anywhere in this set of values".

Does that help?