Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181

    Popup reminders in access

    Ok

    I have this code on form_load for my switchboard, basically a due date reminder, then a reminder 14 days later. The queries individually work but I cannot figure out how to sequence the code. Pleas help. Not sure how to split the DCount if that makes sense. When the form loads it checks to see if due date is within 14 days. If so, send emails to all parties concerned. Then I want to have a popup two weeks later to send another reminder.



    **Private Sub Form_Load() 'On Load of the switchboard check Jobs table for any uncompleted jobs


    Dim intStore As Integer
    Dim intStore1 As Integer


    'Count of uncomplete jobs that are past the Expected Completion Date
    'intStore = DCount("[PeopleID]", "[qryPGRDesks1FilterNextWeekPlusSeven]", "[VacateDesk]= True AND EmailAddress IS NOT NULL")
    'intStore = DCount("[PeopleID]", "[qryAllDeskVacatesIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND FirstEmailReminder IS NULL")
    intStore = DCount("[PeopleID]", "[qryAllDeskVacatesIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND FirstEmailReminder IS NULL")
    intStore1 = DCount("[PeopleID]", "[qryAllDeskVacatesSEcondReminderIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND SecondEmailReminder = Date()")
    'intStore1 = DCount("[PeopleID]", "[qryAllDeskVacatesSEcondReminderIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND SecondEmailReminder = Date()")
    'intStore = DCount("[PeopleID]", "[tblPeople]", "[VacateDesk] = Year([VacateDesk])*53+DatePart(""ww"",[VacateDesk])=Year(Date())*53+DatePart(""ww"",Date())+1")
    'intStore = DCount("[PeopleID]", "[tblPeople]", "[VacateDesk] =DateAdd(""ww""),[VacateDesk]")


    'If count of uncomplete jobs is zero display switchboard
    'Else display message box detailing amount of jobs
    'and give the user the option as to whether to view these or not.
    'If intStore = 0 Then
    'Exit Sub
    'Else
    'If MsgBox("There are " & intStore & " desks which need to be vacated in two weeks " & _
    vbCrLf & vbCrLf & "Would you like to see these now?", _
    vbYesNo, "Notify that desk needs vacating...") = vbYes Then
    'DoCmd.Minimize
    'DoCmd.OpenForm "frmVacateDeskReminder", acNormal
    'Else

    If intStore = 0 Then
    Exit Sub
    Else
    If MsgBox("There are " & intStore & " people " & _
    vbCrLf & vbCrLf & "Would you like to see these now?", _
    vbYesNo, "Notify that desk needs vacating...") = vbYes Then
    DoCmd.Minimize
    DoCmd.OpenForm "frmVacateDeskReminder", acNormal

    'Exit Sub


    ElseIf intStore1 = 0 Then
    Exit Sub
    Else

    If MsgBox("There are " & intStore1 & " where " & _
    vbCrLf & vbCrLf & "Would you like to see these now?", _
    vbYesNo, "Notify that desk needs vacating...") = vbYes Then
    DoCmd.Minimize
    DoCmd.OpenForm "frmVacateDeskReminder14", acNormal
    End If
    End If
    End If


    End Sub

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm not real sure what you are trying to do, but you could try this:
    Code:
    Private Sub Form_Load()    'On Load of the switchboard check Jobs table for any uncompleted jobs
        Dim intStore As Integer
        Dim intStore1 As Integer
        Dim msg As String
    
        'Count of uncomplete jobs that are past the Expected Completion Date
        intStore = DCount("[PeopleID]", "[qryAllDeskVacatesIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND FirstEmailReminder IS NULL")
        intStore1 = DCount("[PeopleID]", "[qryAllDeskVacatesSEcondReminderIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND SecondEmailReminder = Date()")
    
        'checks to see if due date is within 14 days
        If intStore = 0 Then
            MsgBox "No uncomplete jobs that are past the Expected Completion Date", vbOKOnly
            Exit Sub
        Else
            msg = "There are " & intStore & " people " & vbCrLf & vbCrLf & "Would you like to see these now?"
            If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
                DoCmd.Minimize
                DoCmd.OpenForm "frmVacateDeskReminder", acNormal
            End If
        End If
    
        'have a popup two weeks later to send another reminder
        If intStore1 = 0 Then
            MsgBox "No uncomplete jobs that require 2 week reminder", vbOKOnly
            Exit Sub
        Else
            msg = "There are " & intStore1 & " where " & vbCrLf & vbCrLf & "Would you like to see these now?"   '<<-- Not sure about this text. Seems incomplete...
            If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
                DoCmd.Minimize
                DoCmd.OpenForm "frmVacateDeskReminder14", acNormal
            End If
        End If
    
    End Sub
    Note:
    Forms "frmVacateDeskReminder" and "frmVacateDeskReminder14" would need to be Pop Up and Modal.

  3. #3
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by ssanfu View Post
    I'm not real sure what you are trying to do, but you could try this:
    Code:
    Private Sub Form_Load()    'On Load of the switchboard check Jobs table for any uncompleted jobs
        Dim intStore As Integer
        Dim intStore1 As Integer
        Dim msg As String
    
        'Count of uncomplete jobs that are past the Expected Completion Date
        intStore = DCount("[PeopleID]", "[qryAllDeskVacatesIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND FirstEmailReminder IS NULL")
        intStore1 = DCount("[PeopleID]", "[qryAllDeskVacatesSEcondReminderIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND SecondEmailReminder = Date()")
    
        'checks to see if due date is within 14 days
        If intStore = 0 Then
            MsgBox "No uncomplete jobs that are past the Expected Completion Date", vbOKOnly
            Exit Sub
        Else
            msg = "There are " & intStore & " people " & vbCrLf & vbCrLf & "Would you like to see these now?"
            If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
                DoCmd.Minimize
                DoCmd.OpenForm "frmVacateDeskReminder", acNormal
            End If
        End If
    
        'have a popup two weeks later to send another reminder
        If intStore1 = 0 Then
            MsgBox "No uncomplete jobs that require 2 week reminder", vbOKOnly
            Exit Sub
        Else
            msg = "There are " & intStore1 & " where " & vbCrLf & vbCrLf & "Would you like to see these now?"   '<<-- Not sure about this text. Seems incomplete...
            If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
                DoCmd.Minimize
                DoCmd.OpenForm "frmVacateDeskReminder14", acNormal
            End If
        End If
    
    End Sub
    Note:
    Forms "frmVacateDeskReminder" and "frmVacateDeskReminder14" would need to be Pop Up and Modal.
    Hi
    Thanks for the reply. I want to do exactly what you thought. Send first email reminder, then send another two weeks later. I will give your code a try later at work and thanks again for the suggestion.

  4. #4
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Hi ssanfu

    I tried the code and not quite what I was intending. Will try to make it better to understand. When the user logs in I want the code to run the first intstore, if this is zero then move on to instore1. If intstore1 is zero then open the switchboard. What is happening now is intstore is > 0 and the message box pops up asking do I want to see the records, I select YES and frmVacateDeskReminder is shown(as programmed), but then immediately as that happens the second message box for intstore1 is displayed. I don't want this message to appear if intstore value is > 0. I hope this makes sense. I can post parts of the database if that helps.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The code in the form load event will run from start tie end without stopping, even if you open another form.

    You must not have missed the NOTE line (last line) in Post #2.
    Note:
    Forms "frmVacateDeskReminder" and "frmVacateDeskReminder14" would need to be Pop Up and Modal.
    Try setting form "frmVacateDeskReminder" Modal property to YES.
    Set form "frmVacateDeskReminder14" Modal property to YES if there is more code in the real dB.

  6. #6
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Hi
    Both forms were set has you suggested. I understand your code better now thanks. I have set a date for a second email reminder when I login to see if it works as coded. Thanks again.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.

    Good luck on your project.....

  8. #8
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Quote Originally Posted by ssanfu View Post
    Happy to help.

    Good luck on your project.....
    Hi

    Still unable to get the code to work as expected. I know I have an email that is sat in a query for a two week reminder(todays date). If I comment out intStore check then the reminder email is prompted. However, if I uncomment intstore then the 2 week check is bypassed. Not sure why to be honest.

  9. #9
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    ********************
    Private Sub Form_Load()
    Dim intStore As Integer
    Dim intStore1 As Integer
    Dim msg As String


    'Count of uncomplete jobs that are past the Expected Completion Date
    intStore = DCount("[PeopleID]", "[qryAllDeskVacatesIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND FirstEmailReminder IS NULL")
    intStore1 = DCount("[PeopleID]", "[qryAllDeskVacatesSEcondReminderIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND FirstEmailReminder IS NOT NULL AND SecondEmailReminder = Date()")
    'checks to see if due date is within 14 days
    If intStore = 0 Then
    MsgBox "No immediate email reminders need to be sent!", vbOKOnly + vbInformation
    Exit Sub
    Else
    msg = "There are " & intStore & " reminder emails to be sent!" & vbCrLf & vbCrLf & "Would you like to see these now?"
    If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
    DoCmd.Minimize
    DoCmd.OpenForm "frmVacateDeskReminder", acNormal

    End If
    End If

    'have a popup two weeks later to send another reminder
    If intStore1 = 0 Then
    MsgBox "There are no emails that require a 2 week reminder!", vbOKOnly + vbInformation
    Exit Sub
    Else
    msg = "There are " & intStore1 & " emails that have reached the two week reminder date! " & vbCrLf & vbCrLf & "Would you like to see these now?" '<<-- Not sure about this text. Seems incomplete...
    If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
    DoCmd.Minimize
    DoCmd.OpenForm "frmVacateDeskReminder14", acNormal
    End If
    End If

    End Sub

    ****************

    I have the code above and when I run the code and intStore > 0, then I get the (msg) as expected. I process the emails and send them to their recipients The idea then is to store these emails in a query for 14 days and when I load the form again these emails will be flagged up(intStore1) and I will send a final reminder email. The problem is that if intStore = 0 then the next part of the code intStore1 is ignored, so I don't get the prompt to send the final email and the Switchboard opens. "ssanfu", offered good suggestions but I feel I am missing something. Any thoughts welcome. Thanks in advance.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Do Forms "frmVacateDeskReminder" and "frmVacateDeskReminder14" open in datasheet view?

  11. #11
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    I have commented out the first Exit Sub after intstore and this appears to have done the trick. Will run program tomorrow to see if one day reminder pops up. Thanks for your help.

    The forms open up in datasheet view.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There it is!!
    From https://support.office.com/en-us/art...8-3A61219C6D99

    The form isn't modal in Design view or Datasheet view and also isn't modal if you switch from Datasheet view to Form view.
    Try changing the form to open in Continuous View


    In Continuous View, the code will pause while form "frmVacateDeskReminder" is open.

  13. #13
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    I will give your suggestion a try. Thanks once again for your input, much appreciated.

  14. #14
    hinchi1 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2017
    Posts
    181
    Can't use continuous form as form as a subform. Interested to create a continuous form to try the code. Will let you know how it works tomorrow.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sure you can. I do it all the time. Access complains, but just go ahead and set main form and sub form to continuous forms view.
    But you are opening a form stand alone (DoCmd.OpenForm "frmVacateDeskReminder", acNormal) - setting the form to continuous forms view shouldn't be a problem.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Popup reminders in access
    By hinchi1 in forum Access
    Replies: 3
    Last Post: 11-15-2019, 05:53 PM
  2. Replies: 2
    Last Post: 12-01-2015, 10:14 PM
  3. Using Reminders in Access
    By davek81 in forum Access
    Replies: 1
    Last Post: 09-10-2015, 01:20 PM
  4. Popup Reminders
    By burrina in forum Sample Databases
    Replies: 0
    Last Post: 06-14-2014, 02:04 PM
  5. Reminders
    By bursteffect in forum Queries
    Replies: 1
    Last Post: 02-20-2013, 03:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums