Close form on a certain date and time.

[scopes456]

Hi, i saw many post on how to automatically close a form after a certain time, ( example idle for 10 mins). But i would like to close a form on a certain DATE and TIME.

For example users can open form called FRM_CHECKLIST but when it is FEB 20 at 4:00 pm or a later date and time they cannot open that form.


Thank You

 

[plog]

On the OnOpen event of the form, see if the current date is greater than the one you want and if so, do a DoCmd.Close action.

Of course, you probably put should a message box in there so the user knows whats up and doesn't keep trying to open the form only to have it not do so.

 

[scopes456]

plog thank you for your reply. How would i go about coding that to check to see if the date and time is greater? i am a new at vba

 

[plog]

Try this code:

Private Sub Form_Open(Cancel As Integer)
  ' will not allow form to be opened if the CloseDate has passed

Dim CloseDate As Date: CloseDate = CDate("2/5/2016 9:48 PM")
  ' Date that form will automatically close

Dim CloseForm As String: CloseForm = "YourFormNameHere"
  ' Form name to close
  
If (CloseDate < Now()) Then
   ' if Close Date has occured, notifies user and closes form
    MsgBox ("The timeframe for this form has expired and cannot be opened")
    DoCmd.Close acForm, CloseForm
End If

End Sub

Be sure to put your date/time in the top and your form name for 'YourFormNameHere'

 

[scopes456]

Plog - thank you very much. Learning something new is a good way to start a day

 

[saranbl]

Hi,

The above code works awesome.. But what if we can update only a form till 5:00 PM everyday? I don't need the date part but only checks the current time of today. Everyday I can access it till 5:00 PM but after that it need to show a message like "Entry Restricted"?

 

[pbaldy]

You can use the Time() function to get the current time and compare it to

#5:00 PM#

 

[saranbl]

I didn't get you Bro... Is it that I need to include Time() function to the above code or a new one? I am new to VBA

 

[pbaldy]

The test above becomes

If Time() > #5:00 PM# Then

 

[moke123]

just to make you aware if you didnt think of it, a user can always reset his or her system date and time and open the form anyway.

 

[saranbl]

Thank you pbaldy... Thank you very much It Worked..

@moke123: System Date and Time cannot be changed due to security reason...

 

[pbaldy]

Happy to help!