Solved

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

Posted on 2016-07-25
6
59 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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
1
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Careca
Comment Utility
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
Comment Utility
I worked around it and made an excel form with named range for the export data
0
 

Author Comment

by:Careca
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Are you having trouble connecting or getting your iPhone / Samsung device(s) to sync with Microsoft Exchange Server?   What have you tried?   What haven't you tried?
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now