Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Question Unanswered: Pop-up Form based on time

    Hi everybody

    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 ...
    Regards ,,,

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    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!")
    Exit Sub
    End If
    '
    ' Set the time for the correct interval in milliseconds
    '
    Me.TimerInterval = interval * 60 * 1000
    End Sub
    __________________________________________
    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
    '
    End Sub
    HTH

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    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
    Note: you can chage the frequency of the appointment checktime by changing the value of the constant c_lngInterval.

    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:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub CommandOK_Click()
    
        Me.Visible = False
        
    End Sub
    
    Private Sub Form_Current()
    
        Me.Visible = False
        
    End Sub
    Notes:
    - 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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •