Solved

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

Posted on 2012-03-20
7
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 200 total points
ID: 37746959
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
ID: 37746985
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
ID: 37748025
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 18

Expert Comment

by:p912s
ID: 37748071
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
ID: 37750479
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
ID: 37817435
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
ID: 37817444
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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