Prev: Filter and Search button not work in Access 2007
Next: Form opens to blank record
From: Bob Vance on 16 Apr 2010 20:44
All my other Case scenarios are working except fpr "Word". But word is
working on another case code on another form
Can any see why this word not work?
Case "WORD"
strFormat = acFormatRTF
-------------------------------------------------
Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler
If Me.Dirty = True Then
Me.Dirty = False
End If

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer, tbAmount As String
Dim strFormat As String

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
Case "WORD"
strFormat = acFormatRTF
Case "SNAPSHOT"
strFormat = acFormatSNP
Case "TEXT"
strFormat = acFormatTXT
Case "HTML"
strFormat = acFormatHTML
Case Else ' catch all others
strFormat = acFormatHTML
End Select

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"


lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)
tbAmount = Nz(Me.cbOwnerName.Column(5), 0)

strBodyMsg = "To: "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Please find attached your Statement, Dated:" & " " &
Format(Date, "d-mmm-yyyy") & Chr(10) & "Your Statement Total: " &
Format(tbAmount, "$ #,##.00") & Chr(10) & Chr(10) &
Nz(DLookup("[EmailMessage]", "tblCompanyInfo"), "") & eMailSignature("Best
Regards", True) & Chr(10) & Chr(10) & DownloadMessage("PDF") _


CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET EmailDateState = Now() " & _
"WHERE OwnerID = " & lngID, dbFailOnError



DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
ExitProc:
Exit Sub
ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation

Select Case Err.Number
'Cancelled message (2293 & 2296 are raised
'by Outlook, not Outlook Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle
End Select

Resume ExitProc

End Sub
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3


--
Thanks in advance for any help with this......Bob
MS Access 2007 accdb
Windows XP Home Edition Ver 5.1 Service Pack 3


From: Dirk Goldgar on 17 Apr 2010 17:34
"Bob Vance" <rjvance(a)ihug.co.nz> wrote in message
news:%239Uotcc3KHA.5880(a)TK2MSFTNGP02.phx.gbl...
> All my other Case scenarios are working except fpr "Word". But word is
> working on another case code on another form
> Can any see why this word not work?
> Case "WORD"
> strFormat = acFormatRTF
> -------------------------------------------------
> Private Sub SendMailButton_Click()
>
> On Error GoTo ErrorHandler
> If Me.Dirty = True Then
> Me.Dirty = False
> End If
>
> Dim lngID As Long, strMail As String, strBodyMsg As String, _
> blEditMail As Boolean, sndReport As String, strCompany As String
> Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp
> As Integer, tbAmount As String
> Dim strFormat As String
>
> Select Case Me.tbEmailOption.value
>
> Case "ADOBE"
> strFormat = acFormatPDF
> Case "WORD"
> strFormat = acFormatRTF
> Case "SNAPSHOT"
> strFormat = acFormatSNP
> Case "TEXT"
> strFormat = acFormatTXT
> Case "HTML"
> strFormat = acFormatHTML
> Case Else ' catch all others
> strFormat = acFormatHTML
> End Select
>
> Select Case Me.OpenArgs
>
> Case "OwnerStatement"
>
> sndReport = "rptOwnerPaymentMethod"
>
>
> lngID = Nz(Me.cbOwnerName.Column(0), 0)
> strMail = OwnerEmailAddress(lngID)
> tbAmount = Nz(Me.cbOwnerName.Column(5), 0)
>
> strBodyMsg = "To: "
> strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
> "tblOwnerInfo", _
> "[OwnerID]=" & lngID), " ") & " "
> strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
> "tblOwnerInfo", _
> "[OwnerID]=" & lngID), " Owner")
> strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
> & "Please find attached your Statement, Dated:" & " " &
> Format(Date, "d-mmm-yyyy") & Chr(10) & "Your Statement Total: " &
> Format(tbAmount, "$ #,##.00") & Chr(10) & Chr(10) &
> Nz(DLookup("[EmailMessage]", "tblCompanyInfo"), "") & eMailSignature("Best
> Regards", True) & Chr(10) & Chr(10) & DownloadMessage("PDF") _
>
>
> CurrentDb.Execute "UPDATE tblOwnerInfo " & _
> "SET EmailDateState = Now() " & _
> "WHERE OwnerID = " & lngID, dbFailOnError
>
>
>
> DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
> Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
> Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
> Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
> "tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
> cbOwnerName.SetFocus
>
> Case Else
> Exit Sub
>
> End Select
> ExitProc:
> Exit Sub
> ErrorHandler:
>
> msgTitle = "Untrapped Error"
> msgBtns = vbExclamation
>
> Select Case Err.Number
> 'Cancelled message (2293 & 2296 are raised
> 'by Outlook, not Outlook Express).
> Case 2501, 2293, 2296
> Case Else
> MsgBox "Error Number: " & Err.Number & Chr(13) _
> & "Description: " & Err.Description & Chr(13) & Chr(13) _
> & "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle
> End Select
>
> Resume ExitProc
>
> End Sub
> Thanks in advance for any help with this......Bob
> MS Access 2007 accdb
> Windows XP Home Edition Ver 5.1 Service Pack 3
>
>
> --
> Thanks in advance for any help with this......Bob
> MS Access 2007 accdb
> Windows XP Home Edition Ver 5.1 Service Pack 3


Bob -

You need to tell us in what way it's not working, what error message (if
any) and error number are generated, and exactly which line rasies the error

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: Bob Vance on 17 Apr 2010 20:10


>
>
> Bob -
>
> You need to tell us in what way it's not working, what error message (if
> any) and error number are generated, and exactly which line rasies the
> error
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
Thanks Dirk
Enter parameter Value Forms!frmBillStatement!cbOwnerName
But That is the Form and Combo box to select Owner


From: Dirk Goldgar on 17 Apr 2010 23:23
"Bob Vance" <rjvance(a)ihug.co.nz> wrote in message
news:%23DEB7to3KHA.4016(a)TK2MSFTNGP05.phx.gbl...
>> You need to tell us in what way it's not working, what error message (if
>> any) and error number are generated, and exactly which line rasies the
>> error
>>
> Thanks Dirk
> Enter parameter Value Forms!frmBillStatement!cbOwnerName
> But That is the Form and Combo box to select Owner


Is that coming up as an *error*? That just looks like a parameter prompt,
for which the most likely cause would be that the form, "frmBillStatement",
isn't open at the time it is being called on by a query. There is no way it
can have anything directly to do with the format you are using, acFormatRTF.
Maybe they are related in some coincidental way, but I don't see how.

Looking at your code, I'm guessing that the recordsource of the report
refers to Forms!frmBillStatement!cbOwnerName. That form must be open when
the report is run, or else you will get a parameter prompt such as you
describe. Is it?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

From: Bob Vance on 18 Apr 2010 01:22

>
>
> Is that coming up as an *error*? That just looks like a parameter prompt,
> for which the most likely cause would be that the form,
> "frmBillStatement", isn't open at the time it is being called on by a
> query. There is no way it can have anything directly to do with the
> format you are using, acFormatRTF. Maybe they are related in some
> coincidental way, but I don't see how.
>
> Looking at your code, I'm guessing that the recordsource of the report
> refers to Forms!frmBillStatement!cbOwnerName. That form must be open when
> the report is run, or else you will get a parameter prompt such as you
> describe. Is it?
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>
You are right Dirk the report is not open so I would need to have this code
on the On Deactivate, do you think?
Regards Bob

Private Sub SendMailButton_Click()

On Error GoTo ErrorHandler
If Me.Dirty = True Then
Me.Dirty = False
End If

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer, tbAmount As String
Dim strFormat As String

Select Case Me.tbEmailOption.value

Case "ADOBE"
strFormat = acFormatPDF
Case "WORD"
strFormat = acFormatRTF
Case "SNAPSHOT"
strFormat = acFormatSNP
Case "TEXT"
strFormat = acFormatTXT
Case "HTML"
strFormat = acFormatHTML
Case Else ' catch all others
strFormat = acFormatHTML
End Select

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"


lngID = Nz(Me.cbOwnerName.Column(0), 0)
strMail = OwnerEmailAddress(lngID)
tbAmount = Nz(Me.cbOwnerName.Column(5), 0)

strBodyMsg = "To: "
strBodyMsg = strBodyMsg & Nz(DLookup("[ClientTitle]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " ") & " "
strBodyMsg = strBodyMsg & Nz(DLookup("[OwnerLastName]",
"tblOwnerInfo", _
"[OwnerID]=" & lngID), " Owner")
strBodyMsg = strBodyMsg & "," & Chr(10) & Chr(10) & Chr(13) _
& "Please find attached your Statement, Dated:" & " " &
Format(Date, "d-mmm-yyyy") & Chr(10) & "Your Statement Total: " &
Format(tbAmount, "$ #,##.00") & Chr(10) & Chr(10) &
Nz(DLookup("[EmailMessage]", "tblCompanyInfo"), "") & eMailSignature("Best
Regards", True) & Chr(10) & Chr(10) & DownloadMessage("PDF") _


CurrentDb.Execute "UPDATE tblOwnerInfo " & _
"SET EmailDateState = Now() " & _
"WHERE OwnerID = " & lngID, dbFailOnError



DoCmd.SendObject acSendReport, sndReport, strFormat, strMail,
Cc:=DLookup("EmailCC", "tblOwnerInfo", "OwnerID = " & lngID),
Bcc:=DLookup("EmailBCC", "tblOwnerInfo", "OwnerID = " & lngID), _
Subject:="Your Statement" & " / " & Nz(DLookup("[CompanyName]",
"tblCompanyInfo")), MessageText:=strBodyMsg 'EditMessage:=blEditMail
cbOwnerName.SetFocus

Case Else
Exit Sub

End Select
ExitProc:
Exit Sub
ErrorHandler:

msgTitle = "Untrapped Error"
msgBtns = vbExclamation

Select Case Err.Number
'Cancelled message (2293 & 2296 are raised
'by Outlook, not Outlook Express).
Case 2501, 2293, 2296
Case Else
MsgBox "Error Number: " & Err.Number & Chr(13) _
& "Description: " & Err.Description & Chr(13) & Chr(13) _
& "(frmBillStatement SendMailButton_Click)", msgBtns, msgTitle
End Select

Resume ExitProc

End Sub


 |  Next  |  Last
Pages: 1 2
Prev: Filter and Search button not work in Access 2007
Next: Form opens to blank record