Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-29-2019, 01:23 AM   #1
FrostByte
Newly Registered User
 
Join Date: Jan 2015
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
FrostByte is on a distinguished road
Check for #deleted in a certain field.

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.

FrostByte is offline   Reply With Quote
Old 11-29-2019, 01:54 AM   #2
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,318
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Check for #deleted in a certain field.

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?
__________________
-Adam

(my solutions are not guaranteed to work. They are just recommendations sourced from problem solving abilities. Consult an MS Access expert on the forum to clarify my solutions if you're unsure they will work for you)
vba_php is offline   Reply With Quote
Old 11-29-2019, 03:37 AM   #3
FrostByte
Newly Registered User
 
Join Date: Jan 2015
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
FrostByte is on a distinguished road
Re: Check for #deleted in a certain field.

Quote:
Originally Posted by vba_php View Post
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.

FrostByte is offline   Reply With Quote
Old 11-29-2019, 03:44 AM   #4
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,318
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Check for #deleted in a certain field.

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.
__________________
-Adam

(my solutions are not guaranteed to work. They are just recommendations sourced from problem solving abilities. Consult an MS Access expert on the forum to clarify my solutions if you're unsure they will work for you)
vba_php is offline   Reply With Quote
Old 11-29-2019, 06:24 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,400
Thanks: 40
Thanked 3,689 Times in 3,553 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Check for #deleted in a certain field.

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 11-29-2019, 06:44 AM   #6
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 1,318
Thanks: 127
Thanked 313 Times in 300 Posts
vba_php is an unknown quantity at this point
Re: Check for #deleted in a certain field.

Quote:
Originally Posted by CJ_London View Post
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]
__________________
-Adam

(my solutions are not guaranteed to work. They are just recommendations sourced from problem solving abilities. Consult an MS Access expert on the forum to clarify my solutions if you're unsure they will work for you)
vba_php is offline   Reply With Quote
Old 11-29-2019, 02:33 PM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,400
Thanks: 40
Thanked 3,689 Times in 3,553 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Check for #deleted in a certain field.

screen.updating is what you would use in excel

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 11-29-2019, 04:06 PM   #8
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 405 Times in 396 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Check for #deleted in a certain field.

FrostByte,

Is the temp table shared between users OR is it local to each users FE?
Mark_ is offline   Reply With Quote
Old 11-29-2019, 04:41 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 15,103
Thanks: 103
Thanked 1,897 Times in 1,731 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Check for #deleted in a certain field.

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 12-12-2019, 03:07 AM   #10
FrostByte
Newly Registered User
 
Join Date: Jan 2015
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
FrostByte is on a distinguished road
Re: Check for #deleted in a certain field.

Quote:
Originally Posted by Mark_ View Post
FrostByte,

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

The temp table is shared.

FrostByte is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access ODBC connection to mySQL database - Deleted records - just show #Deleted peskywinnets General 7 09-28-2019 08:41 AM
Check to See if CurrentRecord has been Deleted in Bound Form hk1 Forms 1 01-14-2011 07:33 PM
Attachment field not properly deleted if parent row is deleted redMouse Tables 2 06-24-2009 12:12 AM
Auto-Populate field with data deleted in another field jbisson Forms 0 05-12-2009 11:07 AM
Can't remove "#deleted" from a field - Access complains 'Record is Deleted' RSIboy Tables 2 08-20-2004 07:45 AM




All times are GMT -8. The time now is 07:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World