Add to text box String

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

Im trying to add a total off my report to this string for emailing, problem
being the text box is on the report and not the form
tbGrandTotalM is on the report that is being emailed rptOwnerPaymentMethod
How do I code tbAmount below in between the *******
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
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer
Dim strFormat As String, tbAmount 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 =
Format(rptOwnerPaymentMethod.tbGrandTotalM.value, "$#,###.00")******

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) & 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
'User 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
 
Hi Bob,

I think you will need to use one of two methods:

1.) A Domain Aggregrate function, DSum, which uses a table or query with a
specified field, and the appropriate criteria. Here is an example of using
the DLookup function; all of the D functions (DLookup, DSum, DMin, DMax,
etc.) include parameters for specifying a field, a table or query, and an
optional criteria.

2.) Open a recordset, based on a saved query or a SQL statement, to
determine the appropriate sum.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Bob Vance said:
Im trying to add a total off my report to this string for emailing, problem
being the text box is on the report and not the form
tbGrandTotalM is on the report that is being emailed rptOwnerPaymentMethod
How do I code tbAmount below in between the *******
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
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer
Dim strFormat As String, tbAmount 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 =
Format(rptOwnerPaymentMethod.tbGrandTotalM.value, "$#,###.00")******

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) & 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
'User 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
Click to expand...
 
I forgot to add a URL for the sample I mentioned...

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Tom Wickerath said:
Hi Bob,

I think you will need to use one of two methods:

1.) A Domain Aggregrate function, DSum, which uses a table or query with a
specified field, and the appropriate criteria. Here is an example of using
the DLookup function; all of the D functions (DLookup, DSum, DMin, DMax,
etc.) include parameters for specifying a field, a table or query, and an
optional criteria.

2.) Open a recordset, based on a saved query or a SQL statement, to
determine the appropriate sum.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Bob Vance said:
Im trying to add a total off my report to this string for emailing, problem
being the text box is on the report and not the form
tbGrandTotalM is on the report that is being emailed rptOwnerPaymentMethod
How do I code tbAmount below in between the *******
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
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer
Dim strFormat As String, tbAmount 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 =
Format(rptOwnerPaymentMethod.tbGrandTotalM.value, "$#,###.00")******

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) & 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
'User 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
Click to expand...
Click to expand...
 
Thanks Tom, My DLookup for my text box is:
=Format((NZ(DLookUp("Payable","qPayableTotalForPayment","OwnerID =" &
[tbOwnerID].[Value]),"")),"$#,##0.00;($#,##0.00)")
How can I incorperate this into my code?
Regards Bob

Tom Wickerath said:
I forgot to add a URL for the sample I mentioned...

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Tom Wickerath said:
Hi Bob,

I think you will need to use one of two methods:

1.) A Domain Aggregrate function, DSum, which uses a table or query with
a
specified field, and the appropriate criteria. Here is an example of
using
the DLookup function; all of the D functions (DLookup, DSum, DMin, DMax,
etc.) include parameters for specifying a field, a table or query, and an
optional criteria.

2.) Open a recordset, based on a saved query or a SQL statement, to
determine the appropriate sum.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Bob Vance said:
Im trying to add a total off my report to this string for emailing,
problem
being the text box is on the report and not the form
tbGrandTotalM is on the report that is being emailed
rptOwnerPaymentMethod
How do I code tbAmount below in between the *******
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
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String,
msgResp As
Integer
Dim strFormat As String, tbAmount 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 =
Format(rptOwnerPaymentMethod.tbGrandTotalM.value, "$#,###.00")******

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) & 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
'User 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
Click to expand...
Click to expand...
Click to expand...
 
Thanks Tom, Found what i needed in my Combo Box to add to my email;
tbAmount = Nz(Me.cbOwnerName.Column(5), 0)

Regards Bob

Bob Vance said:
Thanks Tom, My DLookup for my text box is:
=Format((NZ(DLookUp("Payable","qPayableTotalForPayment","OwnerID =" &
[tbOwnerID].[Value]),"")),"$#,##0.00;($#,##0.00)")
How can I incorperate this into my code?
Regards Bob

Tom Wickerath said:
I forgot to add a URL for the sample I mentioned...

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Tom Wickerath said:
Hi Bob,

I think you will need to use one of two methods:

1.) A Domain Aggregrate function, DSum, which uses a table or query with
a
specified field, and the appropriate criteria. Here is an example of
using
the DLookup function; all of the D functions (DLookup, DSum, DMin, DMax,
etc.) include parameters for specifying a field, a table or query, and
an
optional criteria.

2.) Open a recordset, based on a saved query or a SQL statement, to
determine the appropriate sum.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

Im trying to add a total off my report to this string for emailing,
problem
being the text box is on the report and not the form
tbGrandTotalM is on the report that is being emailed
rptOwnerPaymentMethod
How do I code tbAmount below in between the *******
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
'*****JK: Added 17/10/06
Dim msgPmt As String, msgBtns As Integer, msgTitle As String,
msgResp As
Integer
Dim strFormat As String, tbAmount 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 =
Format(rptOwnerPaymentMethod.tbGrandTotalM.value, "$#,###.00")******

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) & 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
'User 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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top