Check for #deleted in a certain field. (1 Viewer)

FrostByte

Registered User.
Local time
Today, 11:34
Joined
Jan 15, 2015
Messages
56
Hi,

I have a form that certain fields are returned from a temporary table (that the contents are firstly deleted and then refreshed every 5 minutes).

this causes the 3 fields to show #Deleted until a timer then refreshes the forms query.

The form does work really well and is 10x quicker than my old form using subforms rather than temp tables.

It does however cause this #Deleted issue (until the re-query kicks in).

Im wanting to increase the checking timer dramatically, but in order to do this, I only want the re-query to happen if it finds a #Deleted (rather than blindly re-querying when not needed).


Any help would be greatly appreciated.
 

vba_php

Forum Troll
Local time
Today, 06:34
Joined
Oct 6, 2019
Messages
2,884
why don't you use vba to check the temp table's (or the query's) recordset object for dcount() = 0?? wouldn't that work? or are some records actually kept in the temp table and some deleted?
 

FrostByte

Registered User.
Local time
Today, 11:34
Joined
Jan 15, 2015
Messages
56
why don't you use vba to check the temp table's (or the query's) recordset object for dcount() = 0?? wouldn't that work? or are some records actually kept in the temp table and some deleted?


Hi Adam,

I delete the full data set and refresh with new every 5minutes.

Its made a form that previously had a 3-4 second delay when navigating through records (due to subform lag), now instant.
 

vba_php

Forum Troll
Local time
Today, 06:34
Joined
Oct 6, 2019
Messages
2,884
i don't think I get it. if you delete a source dataset from a form's recordsource property, the change is instant as soon as you navigate to a new record. at that moment you will see "#deleted". what exactly do you want to see in these 3 form fields when your code deletes the table's records? nothing? blank records? a form with no controls on it? a message box? give some details.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:34
Joined
Feb 19, 2013
Messages
16,553
just before you run the delete code, turn off screen updating, then turn it on again after you have done your updating/requerying

think the command is

application.echo =false

then

application.echo=true

note you need to manage the situation if there is an error between turning off echo and turning it back on again to ensure it is turned back on
 

vba_php

Forum Troll
Local time
Today, 06:34
Joined
Oct 6, 2019
Messages
2,884
think the command is

application.echo =false

then

application.echo=true
that command is in my 2016 access version, and if does in fact do the same thing as its predecessor then I was unaware. I believe before, it was:[coode]application.screenUpdating (true/false)[/code]
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:34
Joined
Feb 19, 2013
Messages
16,553
screen.updating is what you would use in excel
 

Mark_

Longboard on the internet
Local time
Today, 04:34
Joined
Sep 12, 2017
Messages
2,111
FrostByte,

Is the temp table shared between users OR is it local to each users FE?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:34
Joined
Feb 28, 2001
Messages
26,996
Technically, you CAN'T check for a deleted record - because it no longer exists. It is an artifact of the visual part of the interface that you see #Deleted# because the visual I/F knows that the record formerly referenced there is now DE-referenced. But no query will tell you that because you can't ask the question with a query. It would have to be a VBA question if it works at all.

MAYBE you could try to reference the affected text box or whatever other control it is and look at the .TEXT property - which means the control would have to be in focus at the time, 'cause you normally can't see the .TEXT otherwise. But to be honest, I have NO idea what you would see in that case. And if you ask for .VALUE, again you might have issues 'cause there IS no value. It is not even NULL. You might get a "property does not exist" in that case, which PERHAPS would tell you that at least something was wrong.
 

Users who are viewing this thread

Top Bottom