Solved

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

Posted on 2016-07-25
6
120 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

809 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