acsendreport

[malibu]

I'm running Access 2007 and I recently programmed my db to send an
email of a report. This use to work, but not it doesn't fire. Is the
code correct or could it be improved? I'm running this on XPSP3,
running Access 2007 SP1.

Here is the code.

Private Sub cmdEmailInvoice_Click()
On Error GoTo Err_Button_Click

DoCmd.SendObject acSendReport, "Reingold Tutoring Invoice",
acFormatPDF, DLookup("[Parents_email]", "Customers", "[StudentID] =" _
& Forms![Add Lesson and Details]!StudentID), , , ("Reingold Tutoring"
& " " & MonthName(Month(Date) - 1) & " " & "Invoice"), _
"Dear " & DLookup("[Parents Title]", "Customers", "[StudentID] = " &
Forms![Add Lesson and Details]!StudentID) & " " & _
DLookup("[Parents LastName]", "Customers", "[StudentID] =" & Forms!
[Add Lesson and Details]!StudentID) & "," & _
vbCrLf & vbCrLf & _
"Please find attached your invoice for this past month of lessons.
Payment instructions are listed at the bottom of the invoice." _
& _
vbCrLf & vbCrLf & _
"Thank you very much," & _
vbCrLf & vbCrLf & _
"Reingold Tutoring", True

Err_Button_Click:
If Err.Number = 2501 Then

Resume Next

End If

End Sub

 

[Pete D.]

My first thought is...is this the only code you have, Access 2007 tightened
up the security and file needs to be in a trusted location, signed with a
certificate or lower security to allow all code to run. 99.99% (okay I
didn't actually test it all) of the code in previous versions works fine in
2007 but you must make sure the security settings are good. Pete

 

[malibu]

I agree with Pete! When I read "2007" and "code used to work but doesn't now"
this is the first thing you have to think about.

To trust your folder, click:

Office Button (top left)
Access Options (bottom of dialog)
Trust Center (left)
Trust Center Settings (button)
Trusted Locations (left)
Add new location (button)

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200901/1

Thanks for those tips, but it still doesn't work. Even before I moved
to 2007 it gave me intermittent results. I'm not sure if it's my poor
coding skills or what. Since the DB contains sensitive information, I
thought I would convert the database to the new format accdb which may
or may not add problems.

I found this kb article http://support.microsoft.com/kb/260819 but I
don't think this will solve my problem either.

As far as other code in my program, I'm simply calling a standard
access report that runs fine on its own.

 

[Pete D.]

Okay, post your code so we can see what might be a problem. Pete

I agree with Pete! When I read "2007" and "code used to work but doesn't
now"
this is the first thing you have to think about.

To trust your folder, click:

Office Button (top left)
Access Options (bottom of dialog)
Trust Center (left)
Trust Center Settings (button)
Trusted Locations (left)
Add new location (button)

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200901/1

Thanks for those tips, but it still doesn't work. Even before I moved
to 2007 it gave me intermittent results. I'm not sure if it's my poor
coding skills or what. Since the DB contains sensitive information, I
thought I would convert the database to the new format accdb which may
or may not add problems.

I found this kb article http://support.microsoft.com/kb/260819 but I
don't think this will solve my problem either.

As far as other code in my program, I'm simply calling a standard
access report that runs fine on its own.

 

[malibu]

Thanks for those tips, but it still doesn't work.  Even before I moved
to 2007 it gave me intermittent results. I'm not sure if it's my poor
coding skills or what.  Since the DB contains sensitive information, I
thought I would convert the database to the new format accdb which may
or may not add problems.

I found this kb articlehttp://support.microsoft.com/kb/260819but I
don't think this will solve my problem either.

As far as other code in my program, I'm simply calling a standard
access report that runs fine on its own.- Hide quoted text -

- Show quoted text -

This is the code from the form "Add lesson and details" in the create
email button.
Private Sub cmdEmailInvoice_Click()
On Error GoTo Err_Button_Click

DoCmd.SendObject acSendReport, "Reingold Tutoring Invoice",
acFormatPDF, DLookup("[Parents_email]", "Customers", "[StudentID] =" _
& Forms![Add Lesson and Details]!StudentID), , , ("Reingold Tutoring"
& " " & MonthName(Month(Date) - 1) & " " & "Invoice"), _
"Dear " & DLookup("[Parents Title]", "Customers", "[StudentID] = " &
Forms![Add Lesson and Details]!StudentID) & " " & _
DLookup("[Parents LastName]", "Customers", "[StudentID] =" & Forms!
[Add Lesson and Details]!StudentID) & "," & _
vbCrLf & vbCrLf & _
"Please find attached your invoice for this past month of lessons.
Payment instructions are listed at the bottom of the invoice." _
& _
vbCrLf & vbCrLf & _
"Thank you very much," & _
vbCrLf & vbCrLf & _
"Reingold Tutoring", True

Err_Button_Click:
If Err.Number = 2501 Then

Resume Next

End If

End Sub

This is the code from the report in the on open event
Private Sub Report_Open(Cancel As Integer)
Dim bCreditCard As Boolean
Dim bInvoicePayed As Boolean

bCreditCard = DLookup("[CreditCard_Denied]", "Customers", "[StudentID]
=" _
& Forms![Add Lesson and Details]!StudentID)

bInvoicePayed = DLookup("[Invoice_Payed]", "Lessons", "[BillingID] ="
_
& Forms![Add Lesson and Details]!BillingID)

'MsgBox (bCreditCard)

If bCreditCard = True Then
lblCreditcardDenied.Visible = True
Else
lblCreditcardDenied.Visible = False
End If

If bInvoicePayed = True Then
lblInvoicePayed.Visible = True
Else
lblInvoicePayed.Visible = False
End If

End Sub

I'm not sure what other code to post related to the e-mail issue.
Marshall

 

[malibu]

Okay, post your code so we can see what might be a problem.  Pete"malibu" <[email protected]> wrote in message

On Jan 3, 6:55 pm, "Linq Adams via AccessMonster.com" <u28780@uwe>
wrote:

Thanks for those tips, but it still doesn't work.  Even before I moved
to 2007 it gave me intermittent results. I'm not sure if it's my poor
coding skills or what.  Since the DB contains sensitive information, I
thought I would convert the database to the new format accdb which may
or may not add problems.

I found this kb articlehttp://support.microsoft.com/kb/260819butI
don't think this will solve my problem either.

As far as other code in my program, I'm simply calling a standard
access report that runs fine on its own.- Hide quoted text -

- Show quoted text -

This is the code from the form "Add lesson and details" in the create
email button.
Private Sub cmdEmailInvoice_Click()
On Error GoTo Err_Button_Click

DoCmd.SendObject acSendReport, "Reingold Tutoring Invoice",
acFormatPDF, DLookup("[Parents_email]", "Customers", "[StudentID] =" _
& Forms![Add Lesson and Details]!StudentID), , , ("Reingold Tutoring"
& " " & MonthName(Month(Date) - 1) & " " & "Invoice"), _
"Dear " & DLookup("[Parents Title]", "Customers", "[StudentID] = " &
Forms![Add Lesson and Details]!StudentID) & " " & _
DLookup("[Parents LastName]", "Customers", "[StudentID] =" & Forms!
[Add Lesson and Details]!StudentID) & "," & _
vbCrLf & vbCrLf & _
"Please find attached your invoice for this past month of lessons.
Payment instructions are listed at the bottom of the invoice." _
& _
vbCrLf & vbCrLf & _
"Thank you very much," & _
vbCrLf & vbCrLf & _
"Reingold Tutoring", True

Err_Button_Click:
If Err.Number = 2501 Then

Resume Next

End If

End Sub

This is the code from the report in the on open event
Private Sub Report_Open(Cancel As Integer)
Dim bCreditCard As Boolean
Dim bInvoicePayed As Boolean

bCreditCard = DLookup("[CreditCard_Denied]", "Customers", "[StudentID]
=" _
& Forms![Add Lesson and Details]!StudentID)

bInvoicePayed = DLookup("[Invoice_Payed]", "Lessons", "[BillingID] ="
_
& Forms![Add Lesson and Details]!BillingID)

'MsgBox (bCreditCard)

If bCreditCard = True Then
    lblCreditcardDenied.Visible = True
Else
    lblCreditcardDenied.Visible = False
End If

If bInvoicePayed = True Then
    lblInvoicePayed.Visible = True
Else
    lblInvoicePayed.Visible = False
End If

End Sub

I'm not sure what other code to post related to the e-mail issue.
Marshall- Hide quoted text -

- Show quoted text -

So, I turned off the error catching code and I'm getting an "Invalid
Procedure call or argument". I'll take a closer look, but I think it
might be dlookups. Maybe that's not allowed anymore.

Is there a workaround for that? What I do is find the email address in
the customers table and I lookup the parents title and lastname to
customize the e-mail. I was taught to use dlookups since they are
pretty powerful, however, I'm not aware of another method.

 

[malibu]

This is the code from the form "Add lesson and details" in the create
email button.
Private Sub cmdEmailInvoice_Click()
On Error GoTo Err_Button_Click

DoCmd.SendObject acSendReport, "Reingold Tutoring Invoice",
acFormatPDF, DLookup("[Parents_email]", "Customers", "[StudentID] =" _
& Forms![Add Lesson and Details]!StudentID), , , ("Reingold Tutoring"
& " " & MonthName(Month(Date) - 1) & " " & "Invoice"), _
"Dear " & DLookup("[Parents Title]", "Customers", "[StudentID] = " &
Forms![Add Lesson and Details]!StudentID) & " " & _
DLookup("[Parents LastName]", "Customers", "[StudentID] =" & Forms!
[Add Lesson and Details]!StudentID) & "," & _
vbCrLf & vbCrLf & _
"Please find attached your invoice for this past month of lessons.
Payment instructions are listed at the bottom of the invoice." _
& _
vbCrLf & vbCrLf & _
"Thank you very much," & _
vbCrLf & vbCrLf & _
"Reingold Tutoring", True

Err_Button_Click:
If Err.Number = 2501 Then

Resume Next

End If

End Sub

This is the code from the report in the on open event
Private Sub Report_Open(Cancel As Integer)
Dim bCreditCard As Boolean
Dim bInvoicePayed As Boolean

bCreditCard = DLookup("[CreditCard_Denied]", "Customers", "[StudentID]
=" _
& Forms![Add Lesson and Details]!StudentID)

bInvoicePayed = DLookup("[Invoice_Payed]", "Lessons", "[BillingID] ="
_
& Forms![Add Lesson and Details]!BillingID)

'MsgBox (bCreditCard)

If bCreditCard = True Then
    lblCreditcardDenied.Visible = True
Else
    lblCreditcardDenied.Visible = False
End If

If bInvoicePayed = True Then
    lblInvoicePayed.Visible = True
Else
    lblInvoicePayed.Visible = False
End If

End Sub

I'm not sure what other code to post related to the e-mail issue.
Marshall- Hide quoted text -

- Show quoted text -

So, I turned off the error catching code and I'm getting an "Invalid
Procedure call or argument".  I'll take a closer look, but I think it
might be dlookups. Maybe that's not allowed anymore.

Is there a workaround for that? What I do is find the email address in
the customers table and I lookup the parents title and lastname to
customize the e-mail. I was taught to use dlookups since they are
pretty powerful, however, I'm not aware of another method.- Hide quoted text -

- Show quoted text -

Alright, I figured that this piece of code was terrible wrong.
MonthName(Month(Date) - 1) - this was suppose to return the previous
months name, however, (January (1)- 1)=0 and monthname of (0) computes
to nothing, so I fixed it with this:

MonthName(Month((DateSerial(Year(Date), Month(Date), 0))))

Problem is now solved!