Home > database >  Time format passing a date to function instead of Time in VBA
Time format passing a date to function instead of Time in VBA

Time:09-06

The On Open function of the form sets the value of my variable, then passes it to the Form Timer. Stepping through it formats the time correctly in the on open, but once it's passed to the form timer function it changes to a date and doesn't display correctly.

Dim timeRemaining As Date

 
Private Sub Form_Open(Cancel As Integer)
    timeRemaining = Format(900, "Short Time")
    
    
End Sub
 
Private Sub Form_Timer()
    timeRemaining = timeRemaining - 1
    lblTimer.Caption = "You will be logged out in " & vbCrLf & timeRemaining & " second"
    If timeRemaining > 1 Then
        lblTimer.Caption = lblTimer.Caption & "s."
    End If
    DoEvents
    If timeRemaining = 0 Then
        'shut down
        Do While Forms.Count > 0
            DoCmd.Close acForm, Forms(0).Name, acSaveYes
        Loop
        
        Do While Reports.Count > 0
            DoCmd.Close acReport, Forms(0).Name, acSaveYes
        Loop

    End If
End Sub

CodePudding user response:

First, set the TimerInterval property to 1000. That is milliseconds, thus 1 second.

Next, modify like this:

Private Sub Form_Open(Cancel As Integer)
    
    timeRemaining = TimeSerial(0, 0, 900)    
    
End Sub

 
Private Sub Form_Timer()

    timeRemaining = DateAdd("s", -1, timeRemaining)

    lblTimer.Caption = "You will be logged out in " & vbCrLf & timeRemaining & " second"
    If timeRemaining > TimeSerial(0, 0, 1) Then
        lblTimer.Caption = lblTimer.Caption & "s."
    End If
    
    DoEvents
    
    If timeRemaining <= 0 Then
        'shut down
        Do While Forms.Count > 0
            DoCmd.Close acForm, Forms(0).Name, acSaveYes
        Loop
        
        Do While Reports.Count > 0
            DoCmd.Close acReport, Forms(0).Name, acSaveYes
        Loop
    End If

End Sub

CodePudding user response:

I assume the TimerInterval is set to 1000 ms for your form because do not mention that at all. Anyway, based on your code you have to set it to 1000ms otherwise you have to change the line timeRemaining = timeRemaining - 1 accordingly.

My code would look like that

Option Compare Database
Option Explicit


Dim timeRemaining As Long

 
Private Sub Form_Open(Cancel As Integer)
    timeRemaining = 900
End Sub
 
Private Sub Form_Timer()

    timeRemaining = timeRemaining - 1        
    lblTimer.Caption = "You will be logged out in " & vbCrLf & _
                    Int(timeRemaining / 60) & " minuntes " & timeRemaining - Int(timeRemaining / 60) * 60 & " seconds"
    If timeRemaining > 1 Then
        lblTimer.Caption = lblTimer.Caption & "s."
    End If
    DoEvents
    If timeRemaining = 0 Then
        'shut down
        Do While Forms.Count > 0
            DoCmd.Close acForm, Forms(0).Name, acSaveYes
        Loop
        
        Do While Reports.Count > 0
            DoCmd.Close acReport, Forms(0).Name, acSaveYes
        Loop

    End If
End Sub

Improved code would do the calculations in an extra line

  • Related