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 :) )

sunsetsurf · Accepted Answer

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.

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

Answers (2)

Related Questions