Update date after printing documents

[huongdl1987]

Hi, I want to automatically update dates in access after printing a document. More specifically, I have 5 templates (Introductory letters, Thank you letters, etc.) to mail merge from word. I have a combo box to choose from these 5 templates, and a button in which when clicked, will print the document. I want to record the date after printing each document.

Below is my code:

Option Compare Database
Public Function CreateWordLetter(strDocPath As String)
    'if no path is passed to function, exit
    
    If IsNull(strDocPath) Or strDocPath = "" Then
        Exit Function
    End If
    
    Dim dbs As Database
    Dim objWord As Object
    Dim PrintResponse
    
    Set dbs = CurrentDb
    
    'create reference to Word Object
    
    Set objWord = CreateObject("Word.Application")
    
    'Word Object is created - now let's fill it with data
    
    With objWord
        .Visible = True
        .Documents.Open (strDocPath)
        
        'move to each bookmark, and insert correct text.
        .ActiveDocument.Bookmarks("firstname").Select
        .Selection.Text = (CStr(Forms!MailMerge_Jun6!strMomFirstName))
        On Error Resume Next
                        
        .ActiveDocument.Bookmarks("address").Select
        .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomAddress))
        On Error Resume Next
        
        .ActiveDocument.Bookmarks("city").Select
        .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomCity))
        On Error Resume Next
        
        .ActiveDocument.Bookmarks("state").Select
        .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomState))
        On Error Resume Next
        
        .ActiveDocument.Bookmarks("zip").Select
        .Selection.Text = (CStr(Forms!MailMerge_Jun6!UpdatedMomZip))
        On Error Resume Next
        
        .ActiveDocument.Bookmarks("firstname2").Select
        .Selection.Text = (CStr(Forms!MailMerge_Jun6!strMomFirstName))
        On Error Resume Next
        
        .ActiveDocument.Bookmarks("lastname").Select
        .Selection.Text = (CStr(Forms!MailMerge_Jun6!strMomLastName))
        On Error Resume Next
    End With
    
    'find out if the user would like to print the document at this time.
    
    PrintResponse = MsgBox("Print this document?", vbYesNo)
    If PrintResponse = vbYes Then
        objWord.ActiveDocument.PrintOut Background:=False
    End If
    
    'release all objects
    
    Set objWord = Nothing
    Set dbs = Nothing
    
End Function
          
Private Sub cbxTemplate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
End Sub
Private Sub cmdMailMerge_Click()
    If Not Len(cbxTemplate.Value) = 0 Then
        CreateWordLetter (cbxTemplate.Value)
        
    Else
        MsgBox "Select Mail Merge process"
    End If
           
    
End Sub

Thank you so much,


Meo

[June7]

One way is to run an UPDATE sql action.

CurrentDb.Execute "UPDATE tablename SET datefieldname = Date() WHERE IDfieldname = " & criteria

Assumes criteria will be a numeric key.

[huongdl1987]

Thank You. I tried that but it didn't work. Where exactly should I put the UPDATE sql?

Here is my code

Private Sub cmdMailMerge_Click()
    If Not Len(cbxTemplate.Value) = 0 Then
        CreateWordLetter (cbxTemplate.Value)
        CurrentDb.Execute "UPDATE UtahMotherInformation SET DateofFirstContact = Date() WHERE cmdMailMerge_CLICK = TRUE"
    Else
        MsgBox "Select Mail Merge process"
    End If
           
    
End Sub

[June7]

The WHERE clause must reference a field in the destination table and appropriate parameter (in this case, I think a reference to control on form or field of the form's RecordSource) must be concatenated into the SQL string.

Is the field DateofFirstContact included in the form's RecordSource. If so, another method to populate would simply be:

Me!DateofFirstContact = Date()

[huongdl1987]

Thank You. I finally decided to use the If Then Statement. For some reasons, when I use the Select Case statement, only the clause Case Else is applied.

Here is my code if anyone wants to know the solution

Private Sub cmdMailMerge_Click()
    If Not Len(cbxTemplate.Value) = 0 Then
        CreateWordLetter (cbxTemplate.Value)
    Else
        MsgBox "Select Mail Merge process"
    End If
           
    If cbxTemplate.Value = "strDoc" Then
            DateofThankYouLetterSent = Date
            TrackingStatus = 8
        Else
            DateofLetterSent = Date
            TrackingStatus = 2
    End If
    
End Sub

Thank you so much for your help June7