Forms and Timed Events

[JohnParker72]

Hi there, I currently have a form that shows the Delivery Status of Orders ( kinda similar to an airport departure board). My issue is that at 8pm this form needs close and open a different form showing different information.

What is the best way to some how have a schedule for example.

05:00 - 20:00 Order Status Information form
20:00 - 05:00 Night Shift Handover Data form

I just cant seem to figure out the best way of doing this within access.

Thanks in advance
regards
JP

 

[apr pillai]

After reading your Post I have done some experiments on the solution and created the following simple Code that works with Timer. It goes something like the following:

There are 86400 seconds in a day (24 x 60 x 60) and 20:00:00 hrs is at 72000 seconds position and 05:00:00 hrs is at 18000 seconds position.

Time() * 86400 = gives current time in seconds (or you can find your target time with the expression: timevalue("20:00:00") * 86400, if different) this can be compared with the target time in seconds (72000 & 18000 as per your requirement) and the second Form opens automatically.

While loading the second form the first Form is closed through the Form_Load() Event Procedure of the second form.

1. Create a Label with the Name: LBL01 on the header of both Forms (for experimenting purposes only), if you want to watch the time changes, during trial runs with 5 or 10 minutes time difference on both forms.
2. Copy and Paste the following Code into the first form's VBA Module:
   
   
   

   Private Sub Form_Load()
      DoCmd.Close acForm, "Night Shift Handover Data form"
      Me.TimerInterval = 1000
      DoEvents
   End Sub
   
   Private Sub Form_Timer()
   Dim T As Long
   Me.LBL01.Caption = Time()
   DoEvents
   T = Time() * 86400
   'change over at 20:00:00 hrs.
   If T = 72000 Then
      DoCmd.OpenForm "Night Shift Handover Data form", acNormal
   ElseIf Me.TimerInterval = 1000 And T Mod 60 = 0 Then
      Me.TimerInterval = 60000
   End If
   
   End Sub

3. If you don't want to do trial runs to test the programs reliability and watch the time change etc. then disable the statement: Me.LBL01.Caption = Time() or remove it.
4. Copy the above code into the second form's VBA Module and give your first form's name (Order Status Information form) in the Open & Close statements.
5. Change the target time 72000 to 18000.

NB: The timer is initially set to run in one second interval and when it hits at one full minute’s time the frequency changes to one minute interval.

Hope it servers your purpose, if so give a feedback.

 

[nanscombe]

I have attached an example of how to swap out a Subform based on time.

You could have a single main form, with a timer function, which swaps out a subform based on whatever you tell it.

It is currently set to swap to 1 of 3 subforms every 20 seconds.

Private Sub Form_Load()
  Me.TimerInterval = 1000 ' Set form timer to tick every second (1000 milliseconds) 
End Sub

Private Sub Form_Timer()
Dim varTemp As Variant, strSeconds As String
  varTemp = Now()
  strSeconds = Format(varTemp, "hh:nn:ss")

  If Me.txtClock & vbNullString <> strSeconds Then Me.txtClock = strSeconds

  strSeconds = Format(varTemp, "ss")
  Select Case strSeconds
  Case "00" To "19"
    If Me.sF01.SourceObject & vbNullString <> "sFrm01" Then Me.sF01.SourceObject = "sFrm01"
  Case "20" To "39"
    If Me.sF01.SourceObject & vbNullString <> "sFrm02" Then Me.sF01.SourceObject = "sFrm02"
  Case Else
    If Me.sF01.SourceObject & vbNullString <> "sFrm03" Then Me.sF01.SourceObject = "sFrm03"
  End Select
End Sub

There is also an example, in the code, of how it could be used to display one subform between "08:00:00" and "19:59:59" and a second subform at all other times.

  strSeconds = Format(varTemp, "hh:nn:ss")
  Select Case strSeconds
  Case "08:00:00" To "19:59:59"
    If Me.sF01.SourceObject & vbNullString <> "sFrm01" Then Me.sF01.SourceObject = "sFrm01"
  Case Else
    If Me.sF01.SourceObject & vbNullString <> "sFrm02" Then Me.sF01.SourceObject = "sFrm02"
  End Select

I hope this is useful.