[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Updated Calendar and Subform

Posted on 2014-02-17
2
Medium Priority
?
326 Views
Last Modified: 2014-02-17
We have a subform "CustomerQuotes" which sits within Main Form "JobReports". When a customer quote is generated it has a "status" field that defaults to "In Progress". This is then updated to Accepted", "Declined" etc.

The field "generateddate" is populated when a new record is raised.

I am using the following code to put a calendar entry into Outlook if the customer quote status has no changed from "In Progress" after  7 Days.

The code is triggered from the Load Event of the JobReports Form.

The idea of the sevendayreminder is that this is populated when the calendar reminder is sent., so that it will not be resent every time the form is loaded.

My problem is where do I put the code so that both actions take place?

      Dim objApp As Object
    Dim objNS As Object
    Dim objFolder As Object
    Dim objDummy As Object
    Dim objRecip As Object
    Dim objAppt As Object
    Dim strMsg As String
    Dim strName As String
    Dim strSubject As String

           
    On Error Resume Next
strSubject = DLookup("[ID]", "[CustomerQuote]", "[Status] = 'In Progress' and [generateddate] < Now() -7 and [sevendayreminder] Is Null")
    strName = "User"
     
    Set objApp = CreateObject("Outlook.Application")
    Set objNS = objApp.GetNamespace("MAPI")
    Set objDummy = objApp.CreateItem(0)
    Set objRecip = objDummy.Recipients.Add(strName)
    objRecip.Resolve

    If objRecip.Resolved Then
        On Error Resume Next
        Set objFolder = _
          objNS.GetSharedDefaultFolder(objRecip, _
            9)
         
        If Not objFolder Is Nothing Then
            Set objAppt = objFolder.Items.Add
                   If DLookup("[generateddate]", "[CustomerQuote]", "[Status] = 'In Progress' and [generateddate] < Now() -7 and [generateddate] > Now() -14 and [sevendayreminder] Is Null") Then
       
           
           If Not objAppt Is Nothing Then
                With objAppt
         
                      .Subject = strSubject
                      .body = Forms![Job Reports]!CustomerQuote!Purpose
                    .Start = Now() + 7
                    .alldayevent = True
                    .Save
                End With
                End If
            End If
            End If
    Else
        MsgBox "Could not find " & Chr(34) & strName & Chr(34), , _
               "User not found"
    End If
                              If DLookup("[generateddate]", "[CustomerQuote]", "[Status] = 'In Progress' and [generateddate] < Now() -7 and [generateddate] > Now() -14 and [sevendayreminder] Is Null") Then
Forms![Job Reports]![CustomerQuotes]!SevenDayReminder = Forms!frmdatabaselogin!cboStaffMember
End If

    Set objApp = Nothing
    Set objNS = Nothing
    Set objFolder = Nothing
    Set objDummy = Nothing
    Set objRecip = Nothing
    Set objAppt = Nothing
0
Comment
Question by:SparkyP
2 Comments
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 39865083
You would have to put the code in the Open event of your login or main menu form.  It needs to go some place where the user isn't going to have to click on something to make it happen.  As long as the user opens the app within the time period, using the login or main menu will make the entry in Outlook.

PS - I didn't examine the code to see if it would work.
0
 

Author Closing Comment

by:SparkyP
ID: 39866651
Just what I wanted

Many Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question