Sending email based on 2 combos

[Mike]

Hi,

The following code triggers a report based on two combos and works fine.

I'd like to send the same report via email, so I changed the DoCmd but didn't
do the trick. What am I missing?

TIA,

Mike



Private Sub Command16_Click()

On Error GoTo Err_Command16_Click



Dim stDocName As String

Dim strSQL As String

Dim strCriteria As String

stDocName = "REPORT1"



If Me!type.Column(0) <> "" Or Me!series.Column(0) <> "" Then

'The line below doesn't work

DoCmd.SendObject acReport, stDocName, , " [CONTRACT_ID] = " &
Me!type.Column(0) & _

" and [TYPE_CONTRACT_ID] = " & Me!series.Column(0)

'The line below works

'DoCmd.OpenReport stDocName, acViewPreview, , " [CONTRACT_ID] = " &
Me!type.Column(0) & _

" and [TYPE_CONTRACT_ID] = " & Me!series.Column(0)



Me!type = ""

Me!series = ""



End If



Exit_Command16_Click:

Exit Sub

Err_Command16_Click:

MsgBox Err.Description

Resume Exit_Command16_Click

End Sub

 

[Daniel Pineault]

A couple things come up

First off it should be acSendReport and not acReport
Secondly, there is no means for you to apply a WhereCondition in the
sendObject method.

Please check the help file for SendObject to get a listing of all the input
variables.

Post back and I'll work with you to get it running.

 

[Mike]

Thanks for getting back to me Daniel and sorry for the time delay.I'm in
Europe.
The expression works if I'm mailing out a standard Report linked to a query
(no combo boxes used). However, I need to use the combos to select the type
and series for the Report which is using Stored Procedure. Here's a reworked
code:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

Dim stDocName As String
Dim strSQL As String
Dim strCriteria As String
stDocName = "REPORT1"
stCombos = " [CONTRACT_ID] = " & Me!type.Column(0) & _
" and [TYPE_CONTRACT_ID] = " & Me!series.Column(0)

If Me!type.Column(0) <> "" Or Me!series.Column(0) <> "" Then
DoCmd.SendObject acSendReport, stDocName, , , , , "TEST COMBOS", , stCombos,
False
Me!type = ""
Me!series = ""
End If

Exit_Command16_Click:
Exit Sub
Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click
End Sub

 

[Mike]

Thanks for getting back to me Daniel and sorry for the time delay.I'm in
Europe.
The expression works if I'm mailing out a standard Report linked to a query
(no combo boxes used). However, I need to use the combos to select the type
and series for the Report which is using Stored Procedure. Here's a reworked
code:

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click

Dim stDocName As String
Dim strSQL As String
stDocName = "REPORT1"
stCombos = " [SERIES_ID] = " & Me!series.Column(0) & _
" and [TYPE_SERIES_ID] = " & Me!type.Column(0)

If Me!type.Column(0) <> "" Or Me!series.Column(0) <> "" Then
DoCmd.SendObject acSendReport, stDocName, , , , , "TEST COMBOS", , stCombos,
False
Me!series = ""
Me!type = ""
End If

Exit_Command16_Click:
Exit Sub
Err_Command16_Click:
MsgBox Err.Description
Resume Exit_Command16_Click
End Sub

 

[Daniel Pineault]

I think I would simply link the combo boxes directly into the query as
criteria and then the SendObject would work.

You can set your criteria for a field to take the value from a form by using
something like

[Forms]![FormName]![ControlName]

and you can also throw in the NZ() to return all if nothing is specified...

Nz([Forms]![FormName]![ControlName],"*")

This way you no longer have to supply a WhereCondition when opening the
query...report.