Form Property Event (1 Viewer)

PatAccess

Registered User.
Local time
Today, 06:31
Joined
May 24, 2017
Messages
284
Good day,
I found the following code:

Code:
Private Sub Form_Timer()
    Me.TextTimer.Value = Format(Time, "HH:mm:ss AM/PM")
    Static iCount As Integer
    iCount = iCount + 1
    If iCount = 60 Then
        Me.TimerInterval = 0
        Call GenerateCertEmail("SELECT * FROM [Qry_EngineerLic-UpCertEmail]")
        If Me.TimerInterval = 0 Then
        Me.TimerInterval = 125
        End If
        Exit Sub
    End If
End Sub

I would like this code to run every 1 or 2 hours for as long as the form is open (right now I have to reopen the form if I want to run). Which property event on the form should I place this code into?

Can anyone help me please?

Thank you,
 
Last edited by a moderator:

Ranman256

Well-known member
Local time
Today, 06:31
Joined
Apr 9, 2015
Messages
4,337
The timer event is correct, and if the form is open it runs.
BUT, timerinteval =1000 is 1 second. (its in milliseconds)

set the inerval = 3,600,000 (1 hour)
then the code will run.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 03:31
Joined
Aug 30, 2003
Messages
36,118
The timer interval determines how often the event is triggered. It's in milliseconds, so 30 seconds is 30,000.
 

PatAccess

Registered User.
Local time
Today, 06:31
Joined
May 24, 2017
Messages
284
Ok here is the deal. This code is called when a new certificate is loaded into the system. Everything works however when my main form is open (which is where I have the time set up) it is not sending the email. I have to go to design view and come back to form view for the system to generate the email. I want to be able to keep my main form open and whenever a user is working in it and uploads some, it checks every hour and then sends the notification.

I am also having some trouble as to where to change my time interval because right now I have it in the form properties and within the form as you can see (125). I am looking at the clock moving and waiting for it to generate that information but nothing.

Here is the code I have in the module to generate the email. This is the code that's being called in my Form_Timer Event

Code:
Function GenerateCertEmail(MySQL As String)
On Error GoTo Exit_Function:
Dim oCertOutlook As Outlook.Application
Dim oCertEmailItem As MailItem

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(MySQL)
If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
    If IsNull(rs!UpCert) Then
        rs.MoveNext
    Else
        If oCertOutlook Is Nothing Then
        Set oCertOutlook = New Outlook.Application
        End If
        Set oCertEmailItem = oCertOutlook.CreateItem(olMailItem)
        With oCertEmailItem
            .To = "abc@gmail.com"
            .Subject = "Notification: New license Uploaded for " & rs!FName
            .Body = "A New license has been uploaded in the system for " & rs!FName & vbCr & _
                    "Check Tbl_EngineerLic - Record " & rs!ID & " to update the Cert Field"
            .Display
            .Send
            rs.Edit
            rs!UpCertEmailSent = Date
                'rs.OpenNotify = Date
            rs.Update
        End With
        Set oCertEmailItem = Nothing
        Set oCertOutlook = Nothing
        rs.MoveNext
    End If
    Loop
    Else
        'Do Nothing
    End If
    rs.Close
Exit_Function:
    Exit Function
End Function
 
Last edited by a moderator:

Solo712

Registered User.
Local time
Today, 06:31
Joined
Oct 19, 2012
Messages
828
Good day,
:
I would like this code to run every 1 or 2 hours for as long as the form is open (right now I have to reopen the form if I want to run). Which property event on the form should I place this code into?

Can anyone help me please?

Thank you,

Pat,
you have some problematic coding in the function. First off, not all the things that the timer loop does have to be done every time it is engaged. Second, not all the sub events need to be regulated by the TimerInterval property.
From what I see I would change the function and the TimerInterval as follows:
Code:
' set the timer to execute every second in the FormLoad event
TimerInterval = 1000
'
Private Sub Form_Timer()
Static iCount As Long
'the clock will refresh every second 
Me.TextTimer.Value = Format(Time, "HH:mm:ss AM/PM")
'
iCount = iCount + 1
'the email generating portion executes every hour
If iCount = 3600 Then
     Call GenerateCertEmail("SELECT * FROM [Qry_EngineerLic-
UpCertEmail]")
     iCount = 0  ' restart the counter
End If
End Sub

Hope that is what you want to accomplish.

Best,
Jiri
 

Mark_

Longboard on the internet
Local time
Today, 03:31
Joined
Sep 12, 2017
Messages
2,111
Along with Jiri's comment, I'd take a look at where your DIM statement is for iCount and I'd also make sure I'm setting it to zero when I begin. If it is being implicitly declared that could cause some issues.
 

MarkK

bit cruncher
Local time
Today, 03:31
Joined
Mar 17, 2004
Messages
8,178
It doesn't make sense to me to run code every second in order to the count the 3600 seconds until an hour has elapsed. Consider how much simpler this is...
Code:
Me.TimerInterval = 60 * 60 * 1000  [COLOR="Green"]'60mins x 60secs x 1000ms = 1 hour[/COLOR]
Me.tbWillSendMailTime = "Will send mail at " & Format(Now()+1/24, "hh:nn a/p")
...and look how it simplifies the event handler...
Code:
Private Sub Form_Timer()
    GenerateCertEmail "SELECT * FROM [Qry_EngineerLic-UpCertEmail]"
End Sub
hth
Mark
 

Mark_

Longboard on the internet
Local time
Today, 03:31
Joined
Sep 12, 2017
Messages
2,111
Mark,

The line in there for
Code:
Me.TextTimer.Value = Format(Time, "HH:mm:ss AM/PM")
makes me think he's got a textbox on the screen he's using to show the current time. At least I'm HOPING that's what he's using it for...
 

MarkK

bit cruncher
Local time
Today, 03:31
Joined
Mar 17, 2004
Messages
8,178
Yes, that shows the current time, and it updates every second. This is my point, that there is no need for that.

What I would do is show the time the next email will be sent, and then handle the timer event once--at that time--and send the email.

I don't insist, and it's not wrong to do it other ways, but running code every second to count 3600 seconds in order to run a process once per hour, well, there are alternatives to doing it that way that are worthy of mention.

Mark
 

Mark_

Longboard on the internet
Local time
Today, 03:31
Joined
Sep 12, 2017
Messages
2,111
Mark,

The oddity is that most users will have the date/time in the lower right corner of their screen anyway.
 

PatAccess

Registered User.
Local time
Today, 06:31
Joined
May 24, 2017
Messages
284
Hello Mark,

Thank you for the response, but I am a little confuse. Where would "Me.TimerInterval = 60 * 60 * 1000" and "Me.tbWillSendMailTime = "Will send mail at " & Format(Now()+1/24, "hh:nn a/p")" go? Would they be under the Form_Load event?

Also where does this "Me.tbWillSendMailTime" come from...Sorry I am fairly new at VBA so I try to understand the statement as I write them.

Thank you,

It doesn't make sense to me to run code every second in order to the count the 3600 seconds until an hour has elapsed. Consider how much simpler this is...
Code:
Me.TimerInterval = 60 * 60 * 1000  [COLOR="Green"]'60mins x 60secs x 1000ms = 1 hour[/COLOR]
Me.tbWillSendMailTime = "Will send mail at " & Format(Now()+1/24, "hh:nn a/p")
...and look how it simplifies the event handler...
Code:
Private Sub Form_Timer()
    GenerateCertEmail "SELECT * FROM [Qry_EngineerLic-UpCertEmail]"
End Sub
hth
Mark
 

PatAccess

Registered User.
Local time
Today, 06:31
Joined
May 24, 2017
Messages
284
Thank you so much! This one works :D

Pat,
you have some problematic coding in the function. First off, not all the things that the timer loop does have to be done every time it is engaged. Second, not all the sub events need to be regulated by the TimerInterval property.
From what I see I would change the function and the TimerInterval as follows:
Code:
' set the timer to execute every second in the FormLoad event
TimerInterval = 1000
'
Private Sub Form_Timer()
Static iCount As Long
'the clock will refresh every second 
Me.TextTimer.Value = Format(Time, "HH:mm:ss AM/PM")
'
iCount = iCount + 1
'the email generating portion executes every hour
If iCount = 3600 Then
     Call GenerateCertEmail("SELECT * FROM [Qry_EngineerLic-
UpCertEmail]")
     iCount = 0  ' restart the counter
End If
End Sub

Hope that is what you want to accomplish.

Best,
Jiri
 

Mark_

Longboard on the internet
Local time
Today, 03:31
Joined
Sep 12, 2017
Messages
2,111
Pat,

You would either set your timer on the form's PROPERTIES in designer OR in the On_Load event.

The code you want executed is in the right spot, the Form_Timer event.
 

Users who are viewing this thread

Top Bottom