470,614 Members | 1,510 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

E-mailing current record on form

easydoesit
Hello all,

I am looking for a way to be able to enter data into fields on a form, then be able to e-mail a report that shows only that record.

This is what I have thus far:

At the end of my Form, I have a Command Button. Right now the Command Button has an On Click... [Event Procedure] to basically E-mail a Report.

Here is my Visual Basic code for it:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Mail_Report_Button_Click()
  2. On Error GoTo Err_Mail_Report_Button_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "TrainingRequestSingle"
  7.     DoCmd.SendObject acReport, stDocName
  8.  
  9. Exit_Mail_Report_Button_Click:
  10.     Exit Sub
  11.  
  12. Err_Mail_Report_Button_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_Mail_Report_Button_Click
  15.  
  16. End Sub
The Report, "TrainingRequestSingle" basically has all of the Fields that are on the Form.

However, this Report that ends up being e-mailed has all of the Records the Form has total.

I want to to be able to click the Command Button to E-mail the Report which would then show only the current Record being viewed on the Form.

I have another Command Button on the same Form that Prints the current Record only (and not all Records) with success. I have tried manipulating it to work for my E-mailing situation with no success, however.

Here is the Visual Basic code I used in that situation:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Print_Record_Button_Click()
  2. On Error GoTo Err_Print_Record_Button_Click
  3.  
  4.  
  5.     DoCmd.OpenReport "TrainingRequestSingle", acViewPreview, , _
  6.     "[TrainingRequestID]=Forms![Training Request Form]!TrainingRequestID"
  7.  
  8. Exit_Print_Record_Button_Click:
  9.     Exit Sub
  10.  
  11. Err_Print_Record_Button_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_Print_Record_Button_Click
  14.  
  15. End Sub
The name of my form is:
Training Request Form

The name of my report is:
TrainingRequestSingle

The name of my e-mail report command button is:
Mail Report Button

I am using Microsoft Access 2002.
Aug 27 '07 #1
2 1998
JConsulting
603 Expert 512MB
Hello all,

I am looking for a way to be able to enter data into fields on a form, then be able to e-mail a report that shows only that record.

This is what I have thus far:

At the end of my Form, I have a Command Button. Right now the Command Button has an On Click... [Event Procedure] to basically E-mail a Report.

Here is my Visual Basic code for it:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Mail_Report_Button_Click()
  2. On Error GoTo Err_Mail_Report_Button_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "TrainingRequestSingle"
  7.     DoCmd.SendObject acReport, stDocName
  8.  
  9. Exit_Mail_Report_Button_Click:
  10.     Exit Sub
  11.  
  12. Err_Mail_Report_Button_Click:
  13.     MsgBox Err.Description
  14.     Resume Exit_Mail_Report_Button_Click
  15.  
  16. End Sub
The Report, "TrainingRequestSingle" basically has all of the Fields that are on the Form.

However, this Report that ends up being e-mailed has all of the Records the Form has total.

I want to to be able to click the Command Button to E-mail the Report which would then show only the current Record being viewed on the Form.

I have another Command Button on the same Form that Prints the current Record only (and not all Records) with success. I have tried manipulating it to work for my E-mailing situation with no success, however.

Here is the Visual Basic code I used in that situation:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Print_Record_Button_Click()
  2. On Error GoTo Err_Print_Record_Button_Click
  3.  
  4.  
  5.     DoCmd.OpenReport "TrainingRequestSingle", acViewPreview, , _
  6.     "[TrainingRequestID]=Forms![Training Request Form]!TrainingRequestID"
  7.  
  8. Exit_Print_Record_Button_Click:
  9.     Exit Sub
  10.  
  11. Err_Print_Record_Button_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_Print_Record_Button_Click
  14.  
  15. End Sub
The name of my form is:
Training Request Form

The name of my report is:
TrainingRequestSingle

The name of my e-mail report command button is:
Mail Report Button

I am using Microsoft Access 2002.

couple of ways really...easiest would be to modify the report's recordsource. It uses a temp queery that you'll need to assign as the recordsource of your report.

Expand|Select|Wrap|Line Numbers
  1. Function MailMyRecord()
  2. On Error Resume Next
  3. Dim qDf As dao.QueryDef
  4. DoCmd.DeleteObject acQuery, "tempqry"
  5. Set qDf = CurrentDb.CreateQueryDef("TempQry", "Select * from myrecordsource where ID=" & Me.ID)
  6. stDocName = "TrainingRequestSingle"
  7.     DoCmd.SendObject acReport, stDocName
  8. End Function
  9.  
I assume you know how to address the e-mail and all that.
J
Aug 28 '07 #2
couple of ways really...easiest would be to modify the report's recordsource. It uses a temp queery that you'll need to assign as the recordsource of your report.

Expand|Select|Wrap|Line Numbers
  1. Function MailMyRecord()
  2. On Error Resume Next
  3. Dim qDf As dao.QueryDef
  4. DoCmd.DeleteObject acQuery, "tempqry"
  5. Set qDf = CurrentDb.CreateQueryDef("TempQry", "Select * from myrecordsource where ID=" & Me.ID)
  6. stDocName = "TrainingRequestSingle"
  7.     DoCmd.SendObject acReport, stDocName
  8. End Function
  9.  
I assume you know how to address the e-mail and all that.
J
Thanks for your reply, but I am so lost I don't even know where to begin, sadly.
Sep 3 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Tony | last post: by
2 posts views Thread by Tom Clavel | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.