Solved

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

Posted on 2016-07-25
6
152 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 37

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 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41729845
1
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 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