email PDF from multiselct listbox

[Ruegen]

Because sendobject doesn't allow me to filter before I make the report into a pdf and then email it I have to (as far as I'm aware) put the criteria into the form.



Since it's a multiselect I need to use "TeacherID IN("&strWhere&")

however since docmd.sendobject doesn't give me that option I thought of making a tempvars using tempvars.add and then having that store "TeacherID IN("&strWhere&") to which I can have the report filter to that.

howerver I am getting an error and I don't know how to fix.

I am using

Private Sub Command144_Click()
  On Error GoTo Err_cmdOpenReport_Click


  Dim strWhere      As String
  Dim ctl           As Control
  Dim varItem       As Variant
  


  'make sure a selection has been made
  
  If Me.lstCategory.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 employee"
    Exit Sub
  End If


  'add selected values to string
  Set ctl = Me.lstCategory
  For Each varItem In ctl.ItemsSelected
    strWhere = strWhere & ctl.ItemData(varItem) & ","
  Next varItem
  'trim trailing comma
  strWhere = Left(strWhere, Len(strWhere) - 1)
  'open the report, restricted to the selected items
  TempVars.Add "pdfsend", strWhere
  
DoCmd.SendObject acSendReport, "rptBookingEmailBatchPDF", "PDFFormat(*.pdf)", Me.txtEmail, , , , , True
Exit_cmdOpenReport_Click:
  Exit Sub


Err_cmdOpenReport_Click:
  MsgBox Err.Description
  Resume Exit_cmdOpenReport_Click
End Sub

My datatype is incorrect. So I put in(tempvars!pdfsend) in the criteria with of the reports record source query with no luck of course.

Any advice?

[ItsMe]

Because sendobject doesn't allow me to filter before I make the report into a pdf and then email it I have to (as far as I'm aware) put the criteria into the form.

I was looking through some threads and stumbled upon an example where the name of the report was left out. So, you open the report with the where clause and then use DoCmd.SendObject acSendReport without the name. Never tried it but June posted some pretty convincing code in post # 4.

https://www.accessforums.net/access/...ect-37936.html

[Ruegen]

Scrathes head* so open report filtered, have it event to send pdf email (*which I would pull from the other original open form) then close the report... seems a bit of a hassle.

I'm thinking I've got the wrong datatype for my tempvar...

[ItsMe]

You need to assign the temp var and send your email before the Next item line in your for each statement

[Ruegen]

You need to assign the temp var and send your email before the Next item line in your for each statement

is the tempvar even going to work with the data? I'm not sure where to place the in() as I could wrap it around the tempvar in the criteria of the query of the report or I could add it to the vba (not sure how) when I make it.

I mean the tempvar will store the same information and I can use it for the query while it builds the form but getting the data type correct is where I am lost...

[ItsMe]

Take a look at the following code. You can place it in a click event to visualize what looping through your multiselection list box looks like.

Private Sub cmdTEst_Click()
 Dim strEmail As String
 Dim varItem As Variant
 For Each varItem In Me.lstCategory
 strEmail = strEmail & Me.lstCategory.Column(1, varItem) & " "
 Next varItem
 MsgBox strEmail
 End Sub

Then maybe you could incorporate something like this. You will have to match data types for your where clause in the open report thing.

Dim lngEmailID As Long
 Dim varItem As Variant
 For Each varItem In ctlList.ItemsSelected
lngEmailID = Me.lstCategory.Column(0, varItem)
DoCmd.OpenReport "rptBookingEmailBatchPDF", acViewPreview, , "PrimeKey =" & lngEmailID
DoCmd.SendObject acSendReport, , "PDFFormat(*.pdf)", Me.txtEmail, , , , , True
Docmd.CloseReport "rptBookingEmailBatchPDF"
Next varItem

[Ruegen]

Take a look at the following code. You can place it in a click event to visualize what looping through your multiselection list box looks like.

Private Sub cmdTEst_Click()
 Dim strEmail As String
 Dim varItem As Variant
 For Each varItem In Me.lstCategory
 strEmail = strEmail & Me.lstCategory.Column(1, varItem) & " "
 Next varItem
 MsgBox strEmail
 End Sub

Then maybe you could incorporate something like this. You will have to match data types for your where clause in the open report thing.

Dim lngEmailID As Long
 Dim varItem As Variant
 For Each varItem In ctlList.ItemsSelected
lngEmailID = Me.lstCategory.Column(0, varItem) & " "
DoCmd.OpenReport "rptBookingEmailBatchPDF", acViewPreview, , "PrimeKey =" & lngEmailID
DoCmd.SendObject acSendReport, , "PDFFormat(*.pdf)", Me.txtEmail, , , , , True
Docmd.CloseReport "rptBookingEmailBatchPDF"
Next varItem

I see the method, however I'd like to learn more about this
http://www.techonthenet.com/sql/in.php

how in a regular query without using sql mode would you enter IN() into the criteria?
err, nevermind answered my own question

[Ruegen]

I guess what I am saying is if IN (TeacherID) is actually

IN(123,124,125,126)

and strWhere is IN(123,124,125,126)

then can't I make the tempvar = strwhere and then later set the tempvar as the criteria in the query?

shouldn't strwhere be a string? therefore the value of tempvar be the string?

[ItsMe]

Have you been able to get your email to attach the correct report yet?

[June7]

What exactly is not clear? Those examples will work in Access query object. Unfortunately quess doesn't work with variable. Sees content of variable is a string, not a set. However, the VBA construct can evaluate the variable as a set. Your other thread addresses the VBA https://www.accessforums.net/forms/m...orm-39262.html

[Ruegen]

What exactly is not clear? Those examples will work in Access query object. Unfortunately quess doesn't work with variable. Sees content of variable is a string, not a set. However, the VBA construct can evaluate the variable as a set.

ignore stupid question of mine sorry

[Ruegen]

Have you been able to get your email to attach the correct report yet?

not yet

I've only made it to open the report correctly, I haven't made it to email the report as I wanted to see if I could carry the in(strwhere) to a tempvar and use that first

[ItsMe]

I just noticed there is a typo in my second code for post #7

this
lngEmailID = Me.lstCategory.Column(0, varItem) & " "
should be
lngEmailID = Me.lstCategory.Column(0, varItem)

[June7]

However, can use variable in the WHERE CONDITION of OpenReport, instead of trying to reference variable in query object, as demonstrated in other thread. Why fixate on using the variable in query object? I never use dynamic parameters in queries anyway. I use WHERE CONDITION of OpenForm and OpenReport or set Filter property.

[Ruegen]

However, can use variable in the WHERE CONDITION of OpenReport, instead of trying to reference variable in query object.

how does access know to create a PDF from a report just opened without the report name??