UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> From Refresh During Queries, Access 2013    
 
   
ry94080
post Jul 12 2019, 10:05 AM
Post#1



Posts: 1,005
Joined: 27-July 05



Hi all,

I have a form that is basically acting as a display board that refreshes every few minutes.

Two of my form fields are kind of "calculated". One field displays a certain color based on the numeric value, the other has an IIF statement to display one field if the other is empty.



My issue is that during the refresh, when the background queries are running to download refreshed data, the two "calculated" fields blank out waiting for the queries to complete.

Is there a way to keep this form static until the queries are done?

I've tried:
form.echo false - which completely blanks the entire form out

Any ideas are welcome! I've been trying to deal with this issue for awhile and would love some help.
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2019, 10:09 AM
Post#2


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


How long does it take to run these queries?

Maybe the better way to do this--for the purposes you describe--is NOT to bind the form to those queries directly. Maybe it would be more effective to use a temp table or tables. Update THOSE tables from the queries at the required interval, and only refresh the form after that has been done, pulling in the updated values from the temp table(s).

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ry94080
post Jul 12 2019, 10:15 AM
Post#3



Posts: 1,005
Joined: 27-July 05



Queries can take up to 10 minutes as a lot of data from different sources needs to be pulled down to accomplish what we want to display. I'm hoping the data on the board can be static and viewed while queries are running in the background, then do a refresh last.


EXACTLY what I tried to do!


However, after trying that, I found I couldn't sort the form the way I wanted to. Doing some research, I found that to sort a form, you want to use a query to do the sort for the form. Is that correct?


So, I'm in a bind. Use queries, refresh issue. Use table, can't sort.


Any ideas? smile.gif
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2019, 10:17 AM
Post#4


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


Another thought is that you could do this with an unbound form.

Update the unbound controls in it (including the two calculated controls) and then let it sit that way. I'm assuming here that this is a "read-only" form in the first place.

When the updates have run, add another line to your VBA that refreshes the unbound controls on the form with those new values.

I guess what I'm suggesting is, in a more general way, uncoupling the actual display of the data from the manipulation of the data for this kind of purpose.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2019, 10:18 AM
Post#5


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


Okay, Use a query based on the temp table, not on the table itself. Would that work?

Again, the basic idea is to uncouple the display from the manipulation of the data.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ry94080
post Jul 12 2019, 10:38 AM
Post#6



Posts: 1,005
Joined: 27-July 05



Tried that too. Still blank out.

Maybe your first idea might work? How would I update the unbound controls with data from a table?
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2019, 10:49 AM
Post#7


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


Okay, so it sounds like the recalculation is occurring in the controls in the form itself, and that's where the problem is.

How about this instead (might be easier than unbound controls so let's try it first).

Can you add fields to the query that supplies records to the form that contain these calculated values, rather than calculating them live in the form?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ry94080
post Jul 12 2019, 10:57 AM
Post#8



Posts: 1,005
Joined: 27-July 05



So one field is conditional formatting. I'm not sure how to handle that.


The other field, I have placed the actual value of the IIF statement in the temp table.


so I'm not sure why it still blanking out on me confused.gif
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2019, 11:09 AM
Post#9


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


How long does the blank out last? Is it for the entire time the query update is happening ?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ry94080
post Jul 12 2019, 11:13 AM
Post#10



Posts: 1,005
Joined: 27-July 05



Yes, it appears while queries are running.

When I "Kill" the process with Ctrl Break. The fields pop back up.
This post has been edited by ry94080: Jul 12 2019, 11:14 AM
Go to the top of the page
 
GroverParkGeorge
post Jul 12 2019, 11:46 AM
Post#11


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


In other words, "something" in that form is directly responding to the queries. Do you have a timer event in that form, btw?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ry94080
post Jul 12 2019, 11:53 AM
Post#12



Posts: 1,005
Joined: 27-July 05



You are correct. I was able to find that I was assigning the IIF statement as a control source in VBA. I changed that to an actual IF statement and it is much better.


So my last thing is the color column. I assign a number and it changes color based on the number. I don't know if there is a way to fix that?



Thanks for your help so far!
Go to the top of the page
 
ry94080
post Jul 12 2019, 11:59 AM
Post#13



Posts: 1,005
Joined: 27-July 05



Maybe instead of using conditional formatting, I can use VBA to change the fields colors and such. I wonder if that will work.
Go to the top of the page
 
ry94080
post Jul 12 2019, 12:47 PM
Post#14



Posts: 1,005
Joined: 27-July 05



Yes there is a timer on the form.
Go to the top of the page
 
GroverParkGeorge
post Jul 13 2019, 07:21 AM
Post#15


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


"Yes there is a timer on the form."

Okay, walk me through the process.

You have queries that periodically update data in a local temp table, right?

And those updates are triggered by that timer in that form?

And there is nothing else happening in the form except when the timer runs the queries?

So, if this is accurate, I'd say the next thing I would do would be to create a second form to support the timer-driven updates to the local temp table, and NOT let it touch this display form until after the updates are complete.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ry94080
post Yesterday, 11:08 AM
Post#16



Posts: 1,005
Joined: 27-July 05



Woah, I didn't even think of that.

The process is basically, the form timer is set to 1 minute, it runs all the necessary queries to update the dataset, then refreshes the form to display the updated data.

Those updates are triggered by the form. The one piece that happens on the form is the conditional formatting of the color field.



I'll give that a try and report results back.


Thanks!~!!
Go to the top of the page
 
ry94080
post Yesterday, 04:39 PM
Post#17



Posts: 1,005
Joined: 27-July 05



Hmm.. That didn't seem to work.


Please confirm I tested correctly.

I removed the timer from the form and actually ran the code manually from the VBA module Immediate window and I see the conditionally formatted field blank out.
Go to the top of the page
 
GroverParkGeorge
post Today, 08:29 AM
Post#18


UA Admin
Posts: 35,302
Joined: 20-June 02
From: Newcastle, WA


Sorry, but I'm thinking that's not going to work if this form is bound to a query that is also directly bound to the tables being updated.

My original thought was to create a temp table and Update that table. Then, after that's done, update the form which is bound to that temp table. That way, there will be a slight delay, of course, but it should not be for the entire time the underlying data is updated.

I realize that this means the display form can't be put on a timer itself, and it can't be refreshed until after each time your updates to the temp table are completed. More steps (which I usually prefer to minimize), but maybe it'll help.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
gemmathehusky
post Today, 09:35 AM
Post#19


UtterAccess VIP
Posts: 4,722
Joined: 5-June 07
From: UK


I think a query is atomic - ie the whole query is a single task

you can show a msgbox "wait for this query to complete" before it starts
it will show it's progress in the status bar

but I don't think you can interrupt a "bulk" query.


If you are desperate, you would have to iterate a query (a different query) a row at a time, and put a dovents within eachloop
Then you could interact with the form while the iteration is taking place.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th July 2019 - 05:30 PM