How can I get a macro to run ... automatically ... at specific times? 6

[garymgordon]

(I need some big help.)

I have a macro I created in Access.

I am trying to figure out how I can have it run either:

Every half hour throughout the day between the hours of 8am and 6pm.

or

At specific times during the day.

Does anyone know how I can do this??

I would GREATLY appreciate some advice.

Thanks,
Gary
Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/

 

[pieeater]

I think the following code does something like you require. It suspends between 9pm and 6am and during the day it 'sleeps' and 'wakes up' every 5 minutes to do something.

Create a form which starts automatically when the database opens and put the code below in the Form_Timer section.
You can adjust the start and end times for suspending. You can amend the form's 'Timer interval' property value and 'Sleepcounter' value to vary the length of time it 'sleeps' between processsing.

Dim current_hour As Integer
Dim bSuspend As Boolean
Dim bSleeping As Boolean
'===================================================
'Suspend all Synchronisation between 21:00 hrs and 06:00 hrs the next morning
'----------------------------------------------------
current_hour = CInt(Format(Time(), "hh&quot)
If (current_hour >= 0 And current_hour < 6)
or(current_hour >= 21 ) Then
bSuspend = True 'Suspend Processing
End If

If not bSuspend then
'In the SLEEP Cycle
If bSleeping Then
iSleepCounter = iSleepCounter + 1
'Check if 5 minutes has passed. Note - the form
'has a timer interval property of 10000
If iSleepCounter = 30 Then
bSleeping = False
End If
Else
'Do The processing here, and reset the counters
bSleeping = True
iSleepCounter = 0
End IF
End IF

It looks good if you add a text box to the form which contains a description of what is currently happening (eg 'I'm suspended, I'm sleeping, I'm processing), and maybe a button to stop the processing

 

[garymgordon]

pieeater,

This is absolutely great ... but how do I create a form which starts automatically when the database opens and put the code below in the Form_Timer section.

If you don't mind ... could you give me more specific info or a good example of how to do this? Just something basic and I guess I can take it from there.

I would greatly appreciate it.

But again ... THANKS!
Gary


Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/

 

[Megahurtz]

Tools,
Startup,
Display Forms/Page
select your form

Access help is pretty decent and will help you code I looked this up:

Timer Event


The Timer event occurs for a form at regular intervals as specified by the form's TimerInterval property.

Remarks

To run a macro or event procedure when this event occurs, set the OnTimer property to the name of the macro or to [Event Procedure].

By running a macro or event procedure when a Timer event occurs, you can control what Microsoft Access does at every timer interval. For example, you might want to requery underlying records or repaint the screen at specified intervals.

The TimerInterval property setting of the form specifies the interval, in milliseconds, between Timer events. The interval can be between 0 and 65,535 milliseconds. Setting the TimerInterval property to 0 prevents the Timer event from occurring.

 

[Megahurtz]

Tools,
Startup,
Display Forms/Page
select your form

Access help is pretty decent and will help you code I looked this up:

Timer Event


The Timer event occurs for a form at regular intervals as specified by the form's TimerInterval property.

Remarks

To run a macro or event procedure when this event occurs, set the OnTimer property to the name of the macro or to [Event Procedure].

By running a macro or event procedure when a Timer event occurs, you can control what Microsoft Access does at every timer interval. For example, you might want to requery underlying records or repaint the screen at specified intervals.

The TimerInterval property setting of the form specifies the interval, in milliseconds, between Timer events. The interval can be between 0 and 65,535 milliseconds. Setting the TimerInterval property to 0 prevents the Timer event from occurring.

 

[garymgordon]

I did this.

Now ... to clarify a little more.

I set the interval to 1000.

I change the:

If (current_hour >= 0 And current_hour < 6)
or(current_hour >= 21 ) Then

to

If (current_hour >= 0 And current_hour < 18)
or(current_hour >= 21 ) Then


So, ... how and when will this run and update itself?


You are great. Thanks.
Gary
Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/

 

[garymgordon]

I am just confused as to how to get it to run.

PS: I changed the 18 back go 6.
hahaha

Anyhow .. once I put the code you gave me into the form as you described, ... what exactly is it going to do and how do I get it to update stuff.



Sorry for my ignorance on this.

Gary
Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/

 

[Rohdem]

The forms 'on timer' event runs at a given interval whenever the form is open. The interval is in the properties window beneath the 'on timer' event. The interval is in milliseconds, so if you set the interval to 1000, that means the code will run every second. In pieeater's code, every ten seconds a counter is incremented by one until it the counter reaches 30 (5 minutes). Then your code runs. The form must remain open all the time, otherwise the code will not run and nothing will happen. You don't need to have anything on the form, just have it open (but as pieeater suggested a status box is a good idea).

The 'on timer' is a very powerful event that I use quite often. The key is to understand how it works. Mike Rohde
&quot;I don't have a god complex, god has a me complex!&quot;

 

[garymgordon]

Rohdem,

I understand (... I think) haha

Now ...I have an excel (.xls) file that is linked to the database ... which is where the update of the data is made. But, ... when I keep the Access form OPEN (as you say it needs to be) .. I can't access the linked .xls file. I get an error that says ... &quot;Cannot access xyz.xls&quot;

Any thoughts?

Gary
Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/

 

[garymgordon]

One other thing ...

Based on pieeaters recommendation ...

&quot;It looks good if you add a text box to the form which contains a description of what is currently happening (eg 'I'm suspended, I'm sleeping, I'm processing), and maybe a button to stop the processing&quot;

How can I do this? I am just confused on exactly what to do?

Thanks,
Gary

Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/

 

[JoeMiller]

Use the task scheduler to open your db and run the macro at the specified times. See these threads for help:

Compact DB
thread181-90346

Automate Queries?
thread701-103615

Automatically Run Acces
thread181-80650 Joe Miller
joe.miller@flotech.net

 

[Rohdem]

No, I don't think you can open a spreadsheet when a database is linked to it. A possible solution to this would be when you do your processing, link to the spreadsheet using code, then when you are done, remove the link.

You can use the transferspreadsheet command to link to a spreadsheet like this:

DoCmd.TransferSpreadsheet acLink, 8, &quot;tablename&quot;,&quot;C:\file.xls&quot;, True

and to remove the link use:

DoCmd.DeleteObject acTable, tablename


The access help files are a good source of information on exactly how these commands work and what all the parameters mean.

As for the status box, you can simply create a text box on your form and change what is in it at the appropriate time in your code.

For example, after a line in your code that puts the form 'back to sleep', you could put the lines:

me.statusbox = &quot;Now Sleeping&quot;
me.refresh

This changes the text in the status box to &quot;Now Sleeping&quot; and refreshes the screen so the user sees the change to the status box.

Mike Rohde
&quot;I don't have a god complex, god has a me complex!&quot;

 

[garymgordon]

Rohdem,

THANKS!


1)

Where exactly in the code that was written by pieeater, would I put the additional text you mentioned such as:

me.statusbox = &quot;Now Sleeping&quot;
me.refresh


and the others as well.


2) I apologize. And please forgive all my questions.

But, regarding:

You can use the transferspreadsheet command to link to a spreadsheet like this:

DoCmd.TransferSpreadsheet acLink, 8, &quot;tablename&quot;,&quot;C:\file.xls&quot;, True

and to remove the link use:

DoCmd.DeleteObject acTable, tablename


Where do I put this ... exactly?
And, specifically ... when I make updates to the Excel spreadsheet ... will this automatically all the Access Database to get the info from within it when it goes to run the macro and do the update?? How will this be working.

Thanks,
Gary
Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/

 

[Rohdem]

current_hour = CInt(Format(Time(), &quot;hh&quot)
If (current_hour >= 0 And current_hour < 6)
or(current_hour >= 21 ) Then
bSuspend = True 'Suspend Processing
End If

If not bSuspend then
'In the SLEEP Cycle
If bSleeping Then
iSleepCounter = iSleepCounter + 1
'Check if 5 minutes has passed. Note - the form
'has a timer interval property of 10000
If iSleepCounter = 30 Then
bSleeping = False
me.statusbox = &quot;Processing&quot;
me.refresh
End If
Else
'Do The processing here, and reset the counters
DoCmd.TransferSpreadsheet acLink, , &quot;tablename&quot;,&quot;C:\file.xls&quot;, True
Docmd.RunMacro &quot;YourMacroName&quot;
DoCmd.DeleteObject acTable, &quot;tablename&quot;
bSleeping = True
me.statusbox = &quot;Sleeping&quot;
me.refresh
iSleepCounter = 0
End IF
End IF

Of course, substitute your table name for 'tablename', your macro name for 'YourMacroName', etc.

The spreadsheet will probably have to be closed in order to allow you to link to it. When Access links to it, it will get the latest info that you have saved in it. Mike Rohde
&quot;I don't have a god complex, god has a me complex!&quot;

 

[garymgordon]

Thank you so much!!!
Gary
Gary M. Gordon, LLC
webmaster@garymgordon.com
Certified Web Developer ::
Application Programmer

http://www.garymgordon.com/