Solved

Send and Infopath form for a specific record from an Access database form

Posted on 2016-07-25
6
108 Views
Last Modified: 2016-08-01
In my Access 2010 Inventory database I have a splitform to show my inventory list.
From a specific record in the form view I wish to send an InfoPath form to email.
In that InfoPath form recipients will fill in blank or missing data and update existing data in all fields.

I have created the InfoPath form, and pretty much everything I need.
What I am struggling with is the vba code to push the info path form to email on just that record from the open form in access.

Can anyone help please.

I am proficient in Access and can edit VBA code, but I am not fluent in writing code.

Thankks
0
Comment
Question by:Careca
  • 4
  • 2
6 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 41727881
I toyed briefly with InfoPath but ultimately abandoned it prior to implementation because Microsoft deprecated the feature.  If you have any plans to update Office (there are already two versions newer than O2010), you might want to consider an alternative.
0
 

Author Comment

by:Careca
ID: 41728987
Hi Pat, thank you for your comment. Yes I know that InfoPath is not the future, however I am in the hands of my client who will be using it for the foreseeable future so I need to use InfoPath.
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41729845
1
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:Careca
ID: 41737027
I worked around it and created an excel form with a named range for the export data.
As infopath will not be well supported, this will suit us best. Thank you
0
 

Author Closing Comment

by:Careca
ID: 41737029
I worked around it and made an excel form with named range for the export data
0
 

Author Comment

by:Careca
ID: 41737081
Here's the code I used incase anyone looking for similar work around ideas..

Private Sub SendTQ_Click()
Dim TQXLattachName As String
Dim TQPicAttachName As String
Dim Picfile As String
TQPicAttachName = Me!PicturePath
TQXLattachName = Me!XlFormPath
Picfile = Dir(TQPicAttachName) 'sets the variable file path of the picture file to be sent

    If IsNull(Me![End User]) Then
        MsgBox "You must select an End User"
        Exit Sub
    Else
        If Me.Dirty Then 'DSave any edits first - if true.
        Me.Dirty = False
        End If
    End If
    On Error GoTo Send_TQ_Err
   
    'EXPORT THE DATA TO A NAMED RANGE ON THE TEMPLATE FILE
    DoCmd.OpenQuery "Qry_End_User_TQ", acViewNormal, acEdit
    DoCmd.TransferSpreadsheet acExport, 9, "Qry_End_User_TQ", TQXLattachName, True, "ExportTQ"
    DoCmd.Close acQuery, "Qry_End_User_TQ"
   
   
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
   
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
 If Picfile <> "" Then ' checks to see if a picture exists for this record and jumps to the next WITH statement if not
    With objEmail
        .to = Forms!Fm_Data_Entry![End User email]
        .Subject = "NMA Inventory Reduction Project - Action Required for TQ on " & Forms!Fm_Data_Entry![Symbol No] & " - " & Forms!Fm_Data_Entry!Desc1
        .Body = "Sir," & vbNewLine & "you have been identified as the intended end user of this slow moving stock item." & vbNewLine & vbNewLine & "Please review the attached TQ form and complete the yellow highlighted areas and return your recommended actions and comments at your earliest convenience." & vbNewLine & "Please do make every effort to improve the JDE data on this high value item, particularly in setting appropriate Criticality and QA Codes and Min/Max Values." & vbNewLine & "Note: to qualify for DSE an item must have an individual unit cost of over $5000." & vbNewLine & "Descriptive dropdown lists are included in the form for your assistance, please do not hesitate to call for any clarification." & vbNewLine & "Thank you in advance for your timely response." & vbNewLine & "Kind regards"
        .Attachments.Add (TQXLattachName)
        .Attachments.Add (TQPicAttachName)
        .FlagStatus = olFlagMarked
        .FlagRequest = "Follow up"
        .Display        ' DISPLAY MESSAGE.
    End With
    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing
Else
    If MsgBox("There is no photograph associatead with this item, do you still want to send just the TQ form without the photo?", vbYesNo, "No Photograph!") = vbYes Then
    With objEmail
        .to = Forms!Fm_Data_Entry![End User email]
        .Subject = "NMA Inventory Reduction Project - Action Required for TQ on " & Forms!Fm_Data_Entry![Symbol No] & " - " & Forms!Fm_Data_Entry!Desc1
        .Body = "Sir," & vbNewLine & "you have been identified as the intended end user of this slow moving stock item." & vbNewLine & vbNewLine & "Please review the attached TQ form and complete the yellow highlighted areas and return your recommended actions and comments at your earliest convenience." & vbNewLine & "Please do make every effort to improve the JDE data on this high value item, particularly in setting appropriate Criticality and QA Codes and Min/Max Values." & vbNewLine & "Note: to qualify for DSE an item must have an individual unit cost of over $5000." & vbNewLine & "Descriptive dropdown lists are included in the form for your assistance, please do not hesitate to call for any clarification." & vbNewLine & "Thank you in advance for your timely response." & vbNewLine & "Kind regards"
        .Attachments.Add (TQXLattachName)
        .FlagStatus = olFlagMarked
        .FlagRequest = "Follow up"
        .Display        ' DISPLAY MESSAGE.
    End With
    End If
End If

    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing

Send_TQ_Exit:
    DoCmd.Close acQuery, "Qry_End_User_TQ"
    Exit Sub

Send_TQ_Err:
        MsgBox Error$
    Resume Send_TQ_Exit
[/code][/code]
End Sub
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 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