Run VBA Function every 30 seconds (from startup)(Access)

Question

So i have my code that should be executed when i start my access DB (it imports files from a folder)

Function import_files
'all the code (works without errors)
end function

My idea was a Macro that runs on startup then opens the function and uses

Application.OnTime Now + TimeValue("00:00:30"), "import_files"

that of corse did not work because its not compatible with access... so you can use it like this:

Excel.Application.OnTime Now + TimeValue("00:00:30"), "import_files"

just that u cant run anything from then on...

is there any simple short solution to this

someting basic like this

Function Import_files
do every 30 seconds 
run code
loop
end function

Thanks for any help! (Im not that good in VBA :) )

Possible duplicate of [VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds](https://stackoverflow.com/questions/2319683/vba-macro-on-timer-style-to-run-code-every-set-number-of-seconds-i-e-120-secon) — Nelson Vides, Apr 04 '18 at 13:53
@NelsonVides Not a dupe, that's Excel-specific code and won't work in Access — Erik A, Apr 04 '18 at 13:56

sunsetsurf · Accepted Answer · 2018-04-05T12:55:07.803

You could consider using the Form's TimerInterval property. You could open a form (hidden) and have the timer trigger the code.

Example
The following example shows how to create a flashing button on a form by displaying and hiding an icon on the button. The form's Load event procedure sets the form's TimerInterval property to 1000 so the icon display is toggled once every second.

Sub Form_Load() 
    Me.TimerInterval = 1000 
End Sub 

Sub Form_Timer() 
    Static intShowPicture As Integer 
    If intShowPicture Then 
        ' Show icon. 
        Me!btnPicture.Picture = "C:\Icons\Flash.ico" 
    Else 
        ' Don't show icon. 
        Me!btnPicture.Picture = "" 
    End If 
    intShowPicture = Not intShowPicture 
End Sub

To use this sample code:
Note: Your screen will look different based on version you are using.
You can Google: access me.timerinterval examples.

Enable macros by clicking options | enable this content | OK.
Create new blank form and view in design view.
Add command button | cancel wizard | delete caption | name it btnPicture.
Add form event procedures.
A. Deselect button and select form properties.
B. Click event loader for form in the property sheet.
C. Double click Code Builder.
D. Select all and then paste code from here.

FYI:
For the code mmehta did; you would put it in a module.
He showed you how to extend on your thought process.
You suggested:

Function Import_files
    do every 30 seconds 
    run code
    if needed reset any variables

        myVariableInteger = 0
        myVariableString = ""
        myVariableString= vbNullString
        myVariableInteger = Null
        Set myVariableObject = Nothing

    loop
end function

He elaborated:

Function Import_files
  Do
    Pause(30)
    run code (You're code here or better yet put your code in a sub routine and call the routine.)
  Loop
End Function

Put this code in a module

Option Compare Database
Option Explicit

Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Error_GoTo

Dim PauseTime As Variant
Dim Start As Variant
Dim Elapsed As Variant

PauseTime = NumberOfSeconds
Start = Timer
Elapsed = 0
Do While Timer < Start + PauseTime
    Elapsed = Elapsed + 1
    If Timer = 0 Then
        ' Crossing midnight
        PauseTime = PauseTime - Elapsed
        Start = 0
        Elapsed = 0
    End If
    DoEvents
Loop

Exit_GoTo:
    On Error GoTo 0
    Exit Function
Error_GoTo:
    Debug.Print Err.Number, Err.Description, Erl
    GoTo Exit_GoTo
End Function

Sub msgUser()
    Dim x As Integer
    x = MsgBox("Click 'OK' to continue.", vbOKOnly, "Ready?")
End Sub

Place your cursor in the routine Pause and press play.
Watch the msgBox pop up every 30 seconds.

How can i use this for kind of a loop then? as im trying to let the function run every 30 seconds from the beginning on... — kint, Apr 04 '18 at 14:20
The code "Me.TimerInterval = 1000" is the loop. 1000=1sec. Thus, 30000=30seconds. "Me.TimerInterval = 30000" — sunsetsurf, Apr 04 '18 at 14:34
oh okay so I can then use the sub From_timer and put my code in there but how does it then run it and start over again? (as I sayed im not that good at VBA sorry) — kint, Apr 04 '18 at 14:36
hi @sunsetsurf thanks for this awesome instruction! the Pause function part works perfect! i do then get an error tho from the module after the caled function has endet... i get this errormessage: Subscript out of range (Error 9) any idea how i can fix this last part? :) — kint, Apr 05 '18 at 06:18
i kind of fixed the error but i found out that it does not loop "infinitif" the function just ends... i tryed it with an goto above the pause(time) and eaven I tryed to call the makro again what also didn't work... :( any idea? — kint, Apr 05 '18 at 10:07
Did you alter the code, or include it with other code? "Subscript out of range" is an index of an array or collection doesn't exist; ex: array index range is 0-9, but 1-10 was called. — sunsetsurf, Apr 05 '18 at 10:10
i just goth it runing <3 XD now its onl my code that has to reset all the variables after the loop :) do you know how i do that?^^ thanks for everithing man!! — kint, Apr 05 '18 at 10:28

mmehta · Answer 2 · 2018-04-04T14:48:12.727

Try the below function for timer.

Function Import_files
      Do
            Pause(30)
            run code
      Loop
end function

Public Function Pause(NumberOfSeconds As Variant)
    On Error GoTo Error_GoTo

    Dim PauseTime As Variant
    Dim Start As Variant
    Dim Elapsed As Variant

    PauseTime = NumberOfSeconds
    Start = Timer
    Elapsed = 0
    Do While Timer < Start + PauseTime
        Elapsed = Elapsed + 1
        If Timer = 0 Then
            ' Crossing midnight
            PauseTime = PauseTime - Elapsed
            Start = 0
            Elapsed = 0
        End If
        DoEvents
    Loop

Exit_GoTo:
    On Error GoTo 0
    Exit Function
Error_GoTo:
    Debug.Print Err.Number, Err.Description, Erl
    GoTo Exit_GoTo
End Function

Run VBA Function every 30 seconds (from startup)(Access)

2 Answers2