Sending email based on 2 combos

  • Thread starter Thread starter Mike
  • Start date Start date
M

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
 
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.
 
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
 
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
 
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.
 
Back
Top