Thread: Pop-up Form based on time
05-05-14, 13:31 #1Registered User
- Join Date
- Jul 2012
Unanswered: Pop-up Form based on time
I spent the last 10 days searching the internet and our forum for specific (detailed) answer for my question with no luck to have it...
I will simplify the situation as I can , I have one table in my DB with the following fields :
ID , APP_DATE , APP_TIME , DETAILS , APP_STS
I want to have a pop-up form 5 minutes before the appointment time !!! (That is it)
Please keep in mind the following :
- I know it's better to manage the appointments within outlook.
- I do not want to connect my DB with outlook appointments.
- I want the reminder form to have a "snooze for 5 minutes" button and "Done" Button to change the appointment status (APP_STS) to done.
- I think we are going to use on timer event (which is totally new for me)
Your help is really appreciated ...
05-06-14, 02:28 #2Registered User
- Join Date
- Nov 2011
Here is an Example:
Private Sub cmdStart_Click()
Dim startTime As Date
Dim interval As Long
' Convert the value in the txtStartTime control
' to a Date/TIME value
startTime = CDate(Me.txtStartTime)
' Add ten minutes
startTime = DateAdd("n", 10, startTime)
' subtract the current time
interval = DateDiff("n", Now(), startTime)
' If that time has already passed, complain
If intrval < 0 Then
MsgBox ("That time has already passed!")
' Set the time for the correct interval in milliseconds
Me.TimerInterval = interval * 60 * 1000
And, you need an event procedure for the timer:
Private Sub Form_Timer()
' Set the length of the next interval to 10 minutes
' (in milliseconds)
Me.TimerInterval = 10 * 60 * 1000
' Do whatever you want to do at this time
05-06-14, 04:21 #3Moderator
Provided Answers: 14
- Join Date
- Mar 2009
Here's what I would do:
1. One unbound form (SF_Appointments) set to Datasheet or Continuous view. The controls on it will be used to display what you want for every appointment. i.e. you have one or several Textbox controls on the form, each control having the name of a column in the appointments table as its ControlSource property.
e.g. If you want to display the Time of each appointment, set the ControlSource property of a text box to APP_TIME.
In the module of this form, use the following code:
Option Compare Database Option Explicit Private Const c_lngInterval As Long = 30000 Private Sub ChangeRecordSource() Dim strSQL As String strSQL = "SELECT * FROM Tbl_Appointments WHERE APP_TIME <= #" & Format(DateAdd("n", -5, Now), "hh:nn:ss") & "#;" Me.RecordSource = strSQL If Me.Recordset.EOF = False Then Me.Parent.Visible = True End Sub Private Sub Form_Open(Cancel As Integer) Me.TimerInterval = c_lngInterval ChangeRecordSource End Sub Private Sub Form_Timer() Me.TimerInterval = 0 ChangeRecordSource Me.TimerInterval = c_lngInterval End Sub
2. A form (Frm_Appointments) that uses SF_Appointments as subform and that also has a command button (CommandOK).
In the module of this form, we have:
Option Compare Database Option Explicit Private Sub CommandOK_Click() Me.Visible = False End Sub Private Sub Form_Current() Me.Visible = False End Sub
- Depending on what you intend to do with the column APP_STS, you'll perhaps need to change the RecordSource of SF_Appointments.
- You'll probaby want to perform an action when CommandOK is clicked rather than simply hiding the form.
- If you want only keep "active" the appointments within a determined time frame, you'll also need to adapt the RecordSource of SF_Appointments.Have a nice day!