My collection of things.

jKafer on the Net

March 8th, 2016 at 4:06 pm

Access / VB Programming – Using a MultiSelect field to send selected info via email

I have never used a multi-select list before, but now is the time.

Below is my adaption of Allen Browne‘s code.

My requirements are this:

  • Allow user to select from open orders
  • send list of selected orders to production – formatted simply – via an email

The database I am working with has a SQL datafile attached so saving a queryDef with the selected records was not an option. So I came up with this work around:

  • Collect the selected items and create the strWhere variable
  • Open the report using the condition – but open hidden (WindowMode:=acHidden) – it does not display to the user, but you can manipulate anything it it = assign the where condition
  • Send the report as an Excel file to specified email address with a subject of “Open Orders” and “Attached is the latest Open Orders list” in the body of the email – because the report is open and the filter is applied that is what gets sent out in the email – kinda slick. ūüôĄ¬†

 

My changes to his code are bolded:

Private Sub cmdSendList_Click()
 On Error GoTo Err_Handler
 'Purpose:  Open the report filtered to the items selected in the list box.
 'Author:   Allen J Browne, 2004.   http://allenbrowne.com
 'implemented into database - 3/8/2016 - jk
 Dim varItem As Variant      'Selected items
 Dim strWhere As String      'String to use as WhereCondition
 Dim strDescrip As String    'Description of WhereCondition
 Dim lngLen As Long          'Length of string
 Dim strDelim As String      'Delimiter for this field type.
 Dim strDoc As String        'Name of report to open.
 'strDelim = """"            'Delimiter appropriate to field type. See note 1. -
 strDoc = "rptOrderList"
'Loop through the ItemsSelected in the list box.
 With Me.lstJobs
   For Each varItem In .ItemsSelected
     If Not IsNull(varItem) Then
       'Build up the filter from the bound column (hidden).
       strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
       'Build up the description from the text in the visible column. See note 2.
       strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
     End If
   Next
 End With
'Remove trailing comma. Add field name, IN operator, and brackets.
 lngLen = Len(strWhere) - 1
 If lngLen > 0 Then
    strWhere = "[jobID] IN (" & Left$(strWhere, lngLen) & ")"
    lngLen = Len(strDescrip) - 2
    If lngLen > 0 Then
       strDescrip = "Categories: " & Left$(strDescrip, lngLen)
    End If
 End If
'Report will not filter if open, so close it. For Access 97, see note 3.
 If CurrentProject.AllReports(strDoc).IsLoaded Then
     DoCmd.Close acReport, strDoc
 End If
'Omit the last argument for Access 2000 and earlier. See note 4.
 DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, WindowMode:=acHidden, OpenArgs:=strDescrip
 DoCmd.SendObject acSendReport, strDoc, "Excel", "OpenOrders@company.com", , , "Open Orders", "Attached is the latest Open Orders list" 

 

If CurrentProject.AllReports(strDoc).IsLoaded Then
 'closes the report that I had opened with "WindowMode:=acHidden"
 DoCmd.Close acReport, strDoc
End If
Exit_Handler:
 Exit Sub
Err_Handler:
 If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
 End If
Resume Exit_Handler
End Sub

Tags: , ,
-

Comments are closed.


  • Count per Day

    • 11Visitors today:
    • 33Visitors per day:
    • 0Visitors currently online:
  • ScrapBook Software - MyMemoriesSuite
  • Latest Pins on Pinterest

    • @suppose_quilts I totally OMG'd outloud when I saw this quilt! I love it! By @suppose_quilts "It just snowed here but we're pretending it's spring. Pattern: Amsterdam, free at cloud9fabrics.com. Fabric: Pam Kitty Garden" via @PhotoRepost_app

    • Striking Strip Quilts: 16 Amazing Patterns for 2 1/2"-Strip Lovers: Kate Henderson: Jelly roll tulip quilt. Flower quilt pattern. affiliate link.

    • Fall Table Runner | from Marta with love http://frommartawithlove.com/fall-table-runner/

    • Scrappy Butterfly Baby Quilt Tutorial | Let your imagination take flight with this baby quilt!

    • All In A Row Again!

    • Feathered stars are fun to quilt! #freemotionquilting #longarmquilting #quiltedthistle

    • Westalee - Curly Q's

    • 18 Easy tutorials for making your own fabric labels at home. Click through to browse the list! DIY Crush

    • 6a0120a5f3f908970b015437f40faf970c-pi 3,312√ó4,416 pixels