Solved

Access 2003 correct method to open a form that needs to be refreshed on a timed interval

Posted on 2012-03-20
7
371 Views
Last Modified: 2012-04-06
1 - 5  working correctly, but is stopping me from doing a auto-refresh:

1. I have a Table (T1) in a working database (MDB1) that is having constant dao connections adding and/or deleting  records, accessing and updating records real time.

2. I have a query (qMKTBL2) that access's T1 for a data collection, it makes a table (T2) in the working MDE (MDE2).

3. I have display form (Df2) that displays the data the data source is T2.

4. I have a Splash form (SPLSH1) that is the Startup Form, and on the open event it runs the qMKTBL2 query then calls the Df2 form.

5. This form also has a command button that calls qMKTBL2 and opens Df2.

Up till now this has all worked, Open the  MDE, the StartUp Form is the SPLSH1,  in the  morning, allday long close the form and click the button on the SPLSH1 screen and the form re-opend with fresh data on it.  Not very sophisticated, but it worked, now I need to add  a Auto Refresh to the form on a timed interval.

What I need if this is all wrong is help with correct format and sequence of the events, so I can open it in the morning, and every X number of minuets it reloads the data, closes and reopens Df2 or refreshes Df2.

I can close the entire mde and reopen it, but even less sophisticated, I feel I have the foundation wrong.  The only thing I can not do is keep a query attached to the T1, It needs to get in then get out.

I hope this is not to confusing,

Thank you for your time experts!
0
Comment
Question by:mdlp
7 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 200 total points
Comment Utility
There are a couple of issues you must deal with.

1.  You don't want to run the query and overwrite T2 if someone is currently using df2.

2.  This is a syntax thing, but there is  a difference between refreshing and requerying a form.  If you refresh the form, the data that is already part of the recordset will be updated, but deleted records will not be removed and records that are added to the recordset will not be displayed.  In order to get rid of the Deleted and add the new records to your forms recordset, you must use the requery method.

So,  the way I would do this is to use the Splash forms timer interval and Timer event to control how often this gets done.  To do so, I would do something like:

1.  Set the Splash forms timerinterval to 0
2.  Then as soon as the splash form opens df2, I would set the timer interval to whatever timespan you want to use to do this requery.
3.  In the Timer event, I would first confirm that df2 is currently open.  If not, I would go ahead and run the query to update T2.  

If df2 is open, you have a challenge.  You could display a msgbox (which requires acknowledgement) advising the user that it is time to update T2, and give them the option of delaying the update or running it; but this requires acknowledgement.

Another method would be to create your own popup message form, which gives you more control.  If you use this method, you could popup the message, and if the user doesn't acknowledge after a specific time period, you would assume that the computer is unattended or the application is not actively in use.  You could then close the popup form and run the update.  The advantage of this method is that you control the popup form.  If you use a msgbox, there is no way for you to circumvent that if the user doesn't click one of the buttons on the form.
0
 
LVL 18

Assisted Solution

by:p912s
p912s earned 200 total points
Comment Utility
If opening and closing your form does what you need, why not do just that?

In the OnTimer event of the form place this code (update for your form name)
    DoCmd.Close acForm, "Form1"
    DoCmd.OpenForm "Form1"

Open in new window

And Set the Timer Interval to whatever you want for a refresh - 1000 = 1second, 2000 = 2seconds etc...
0
 
LVL 11

Assisted Solution

by:datAdrenaline
datAdrenaline earned 100 total points
Comment Utility
I personally think you are approaching this in the wrong fashion.  I would use one of two methods to accomplish your task, both of which remove the "Make Table" query object (qMKTBL2).

Technique #1 -- prefered technique (at least by me):
Consider this, you can get to the source data inorder to run your 'Make Table' query object, so why even transfer the data to another table?  Just use a SELECT query that reads directly from the source (MDB1.T1) as the RecordSource of your Form (Df2):

SELECT * FROM T1 IN 'C:\someFolder\somePath\MDB1.mdb'

{of course you can add a WHERE clause or ORDER BY or whatever you need in order to reflect the data

Your splash screen can still open Df2, but that is all the code it needs -- no need to anything else.  Then in Df2 you can use the Timer event (as has been mentioned) to either invoke the .Requery method, or you can simply set the .RecordSource property with itself.

Private Sub Form_Timer()
    Me.Requery
End Sub

Open in new window

Or ...
Private Sub Form_Timer()
    Me.RecordSource = Me.RecordSource
End Sub

Open in new window


There is a slight difference between the two -- setting the .Recordsource removes form filters and sorts.

By using this technique, you remove the effects of creating/dropping a table (ie: Bloating; increased 'Risk' of corruption), plus if this is a multi-user db, others will not be effected by a Drop/Re-Create of your local table (T2).  In addition, this method improves the simplicity of your app:

Slash Screen (with minimal code)  -> Df2 -> Timer to refresh the data bound to the form.

Second technique...
Df2 bound to T2 per your current configuration, Code in Splash Screen only opens Df2, then in the Timer event of Df2 DELETE all the records in T2, then APPEND the records from MDB1.T1 to T2, then use the .Requery / .Recordsource = .Recordsource technique to refresh the data bound to the form.

Private Sub Form_Timer()
    With CurrentDb
        .Execute "DELECT FROM T2", dbFailOnError
        .Execute "INSERT INTO T2 (f1, f2) FROM SELECT f1, f2 FROM T1 IN 'c:\folder\mdb1.mdb'", dbFailOnError
    End With
    Me.RecordSource = Me.RecordSource
End Sub

Open in new window


With technique #2, if this is a multi-user app and T2 is a shared table, then issues will arise.

Hope that helps!
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 18

Expert Comment

by:p912s
Comment Utility
Having reread your question I see the button you click calls a query and then opens the form.

Why not make the query the source for the form?
0
 
LVL 16

Expert Comment

by:kmslogic
Comment Utility
It's probably safe to assume that the query that makes the local table t2 that df2 uses is a slow one and not ideal to run the form itself.

I think the experts have given some good ideas about how to handle the problem--it's going to center around the on timer event one way or the other.  What might be useful is knowing "why" you need to provide this automatic data refreshing.   If it is simply so your users aren't looking at old data on a form that was (accidentally?) left open then you can use the on timer event to just close the data entry form after so-many-seconds of inactivity forcing them to use the splash screen button to refresh the data.
0
 

Author Closing Comment

by:mdlp
Comment Utility
I am sorry experts, but I had a emergency come up and had forgotten that I had this post,  My fault as I did not know on the new interface that you can make bold text, as this should have been bold.<br /><br />The only thing I can not do is keep a query attached to the T1, It needs to get in then get out ASAP.<br /><br />I had it running with the when the form was based on a query, no problem.  I can not stay attached, no if's, ands or buts, so to speak.  The developer of the software that I am creating this module for has that in the specifications. So I decides to use the make table, yes it is slower, it did avoid the attachment issue.<br /><br />datAdrenaline, I can not attach to the working data, as mentioned above. But I like your way of and thought on the refresh, maybe on the next project.<br /><br />kmslogic, It is a production screen that needs the data updated on an ongoing basis, much like the screens at the local fast food joints, that report the next orders up, but on a slower time frame. It sits in a production department of a factory displaying on a 60' screen.<br /><br />My problem, came when I tried to delete the table that is created during the make table, I assume somewhere in the MS logic, they keep it locked for a reason.  When I closed the form and tried to delete it, it would report locked, I see that this is an issue that can not be overcome that I could find, from the posts on the EE and many other boards.   <br /><br />What I have come up with is loosely based on a few parts of different posts, First;<br /><br />Fyed you posted this from a different perspective than I used, but you got me thinking; 3.  In the Timer event, I would first confirm that df2 is currently open.  If not, I would go ahead and run the query to update T2.  <br /><br /><br />I was not updating T2, I wanted to delete it and create it anew, but that just keep me in the locked loop from above.<br /><br />That together with p912s statement of : If opening and closing your form does what you need, why not do just that?  At first it seems too simple of an answer, but here is what I have working.<br /><br />Two forms that toggle open and closed based on the timer event and code attached to a set of got focus events on the fields on the splash screen.  I know it is crude but it works, I will have to wait to see if there are any big unknowns that I am not aware of.<br /><br />I do not like the got focus part but could not come up with any other way to start a new and different process, each time.  I am going to look at the value in the field tbCf# field and make the change that way. The got focus is working.<br /><br /><br />Splash form has 2 fields: tbCf1, tbCf2.<br />I have 2 tables;  T2a, T2b.<br />I have 2 display forms;  Df2a, Df2b <br />I have 2 Queries, Delete; qdT2a, qdT2b <br />I have 2 Queries, Append; qaT2a, qaT2b<br /><br />Instead of deleting the tables, I am deleting the data and appending new data , but It still locked the delete of the data if I tried to delete it from any event on the form that was closing it, or until I opened another form.  <br /><br />So I figured, if I can not beat MS, I would avoid their logic, I do not want to join their logic…. <br /><br />‘******Splash form; On Open Event <br />qdT2a (to make sure that the table is empty)<br />qaTa2  (fill the table)<br />set value tbCf1 to 2 (ready for next loop)<br />open Df2a <br /><br />‘******Splash[tbCf1] Got focus event;<br />Close Df2b<br />‘Check a bunch of status stuff and house keeping<br />qdT2a  (make sure that T2a is empty)<br />qaT2a  (append to empty T2a)<br />open Df2a<br /><br />‘******Splash[tbCf2] Got focus event;<br />Close Df2a<br />‘Check a bunch of status stuff and house keeping<br />qdT2b  (make sure that T2b is empty)<br />qaT2b  (append to empty T2b)<br />open Df2b<br /><br /><br />**** END OF SPLASH FORM ****************<br /><br />****** Df2a Timer Event:<br />Set focus splash<br />Set focus splash[tbCf2]<br />Set tbCf1 = 2 <br /><br />***** Df2a  Load<br />         Me.TimerInterval = 50000<br /><br />***** Df2b Timer Event:<br />Set focus splash<br />Set focus splash[tbCf1]<br />Set tbCf1 = 1 <br /><br /><br />***** Df2a  Load<br />         Me.TimerInterval = 50000<br /><br />***** Df2b  Load<br />         Me.TimerInterval = 50000<br /><br />Thank you again Experts for your input, I hate splitting points, as you all deserve more.
0
 

Author Comment

by:mdlp
Comment Utility
Sorry for the above post, I cut from a comment I started, all the formatting came over.

I am sorry experts, but I had a emergency come up and had forgotten that I had this post,  My fault as I did not know on the new interface that you can make bold text, as this should have been bold.

The only thing I can not do is keep a query attached to the T1, It needs to get in then get out ASAP.

I had it running with the when the form was based on a query, no problem.  I can not stay attached, no if's, ands or buts, so to speak.  The developer of the software that I am creating this module for has that in the specifications. So I decides to use the make table, yes it is slower, it did avoid the attachment issue.

datAdrenaline, I can not attach to the working data, as mentioned above. But I like your way of and thought on the refresh, maybe on the next project.

kmslogic, It is a production screen that needs the data updated on an ongoing basis, much like the screens at the local fast food joints, that report the next orders up, but on a slower time frame.

My problem, came when I tried to delete the table that is created during the make table, I assume somewhere in the MS logic, they keep it locked for a reason.  When I closed the form and tried to delete it, it would report locked, I see that this is an issue that can not be overcome that I could find, from the posts on the EE and many other boards.  

What I have come up with is loosely based on a few parts of different posts, First;

Fyed you posted this from a different perspective than I used, but you got me thinking; 3.  In the Timer event, I would first confirm that df2 is currently open.  If not, I would go ahead and run the query to update T2.  


I was not updating T2, I wanted to delete it and create it anew, but that just keep me in the locked loop from above.

That together with p912s statement of : If opening and closing your form does what you need, why not do just that?  At first it seems too simple of an answer, but here is what I have working.

Two forms that toggle open and closed based on the timer event and code attached to a set of got focus events on the fields on the splash screen.  I know it is crude but it works, I will have to wait to see if there are any big unknowns that I am not aware of.

I do not like the got focus part but could not come up with any other way to start a new and different process, each time.  I am going to look at the value in the field tbCf# field and make the change that way. The got focus is working.


Splash form has 2 fields: tbCf1, tbCf2.
I have 2 tables;  T2a, T2b.
I have 2 display forms;  Df2a, Df2b
I have 2 Queries, Delete; qdT2a, qdT2b
I have 2 Queries, Append; qaT2a, qaT2b

Instead of deleting the tables, I am deleting the data and appending new data , but It still locked the delete of the data if I tried to delete it from any event on the form that was closing it, or until I opened another form.  

So I figured, if I can not beat MS, I would avoid their logic, I do not want to join their logic….

‘******Splash form; On Open Event
qdT2a (to make sure that the table is empty)
qaTa2  (fill the table)
set value tbCf1 to 2 (ready for next loop)
open Df2a

‘******Splash[tbCf1] Got focus event;
Close Df2b
‘Check a bunch of status stuff and house keeping
qdT2a  (make sure that T2a is empty)
qaT2a  (append to empty T2a)
open Df2a

‘******Splash[tbCf2] Got focus event;
Close Df2a
‘Check a bunch of status stuff and house keeping
qdT2b  (make sure that T2b is empty)
qaT2b  (append to empty T2b)
open Df2b


**** END OF SPLASH FORM ****************

****** Df2a Timer Event:
Set focus splash
Set focus splash[tbCf2]
Set tbCf1 = 2

***** Df2a  Load
         Me.TimerInterval = 50000

***** Df2b Timer Event:
Set focus splash
Set focus splash[tbCf1]
Set tbCf1 = 1


***** Df2a  Load
         Me.TimerInterval = 50000

***** Df2b  Load
         Me.TimerInterval = 50000

Thank you again Experts for your input, I hate splitting points, as you all deserve more.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now