Strange behaviour of Access (1 Viewer)

jaryszek

Registered User.
Local time
Today, 12:38
Joined
Aug 25, 2016
Messages
756
Hi,

i have sql:

Code:
SELECT *
FROM tblImages
WHERE (((tblImages.IMAGEIDPK) Not In (select IMAGEIDPK from qryIm_DeleteImagesSelect)));

and this is returning:



why i am seeing this "New" record, i would like to turn off this and get entirely empty query result like here:




please help,
Jacek
 

Attachments

  • Screenshot_3.png
    Screenshot_3.png
    68.3 KB · Views: 140
  • Screenshot_4.png
    Screenshot_4.png
    71 KB · Views: 137

theDBguy

I’m here to help
Staff member
Local time
Today, 12:38
Joined
Oct 29, 2018
Messages
21,358
Hi. Not in front of a computer now but try going into the properties window of the query and set the recordset type to snapshot.
 

jaryszek

Registered User.
Local time
Today, 12:38
Joined
Aug 25, 2016
Messages
756
oo nice works!

Best,
Jacek
 

isladogs

MVP / VIP
Local time
Today, 19:38
Joined
Jan 14, 2017
Messages
18,186
You could also use Unique Values = Yes (SELECT DISTINCT)

Do bear in mind that using snapshot or unique values makes a query read only.
However, in this case that wouldn't appear to be an issue

If you want a query to remain editable whilst removing the New record placeholder, use a form
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 19:38
Joined
Feb 19, 2013
Messages
16,553
faster that using a subquery would be to use a left join query

Code:
SELECT tblImages .*
FROM tblImages LEFT JOIN qryIm_DeleteImagesSelect ON tblImages.IMAGEIDPK = qryIm_DeleteImagesSelect.IMAGEIDPK
WHERE qryIm_DeleteImagesSelect.IMAGEIDPK is Null
 

jaryszek

Registered User.
Local time
Today, 12:38
Joined
Aug 25, 2016
Messages
756
hi CJ_London,

Code:
SELECT tblImages .*
FROM tblImages LEFT JOIN qryIm_DeleteImagesSelect ON tblImages.IMAGEIDPK = qryIm_DeleteImagesSelect.IMAGEIDPK
WHERE qryIm_DeleteImagesSelect.IMAGEIDPK is Null

i trid to use this but i have error:

"Could not delete from specified tables".

Why is that?

My query is:
Code:
DELETE tblCustomerTopologyServerImages.*
FROM tblCustomerTopologyServerImages LEFT JOIN qryApp_CustomerTopologyServer2 ON (tblCustomerTopologyServerImages.ImageIDFK = qryApp_CustomerTopologyServer2.ImageIDPK) AND (tblCustomerTopologyServerImages.CustomerTopologyServerIDFK = qryApp_CustomerTopologyServer2.CustomerTopologyServerID)
WHERE (((qryApp_CustomerTopologyServer2.ImageIDPK) Is Null) AND ((qryApp_CustomerTopologyServer2.CustomerTopologyServerID) Is Null));

Best,
Jacek
 

isladogs

MVP / VIP
Local time
Today, 19:38
Joined
Jan 14, 2017
Messages
18,186
PMFJI - Remember you need to use DELETE DISTINCTROW ...
 

jaryszek

Registered User.
Local time
Today, 12:38
Joined
Aug 25, 2016
Messages
756
thank you Colin,

this is my junction table with PK so here i do not have to use disctintrow.
 

isladogs

MVP / VIP
Local time
Today, 19:38
Joined
Jan 14, 2017
Messages
18,186
Yes you do as your delete query contains a join on a query
You need to use DISTINCTROW to get unique records for deletion
 
Last edited:

Users who are viewing this thread

Top Bottom