Saving record to table and email notification

Arvin

I'm liv'in the dream ....
Local time
Today, 03:09
Joined
Jul 22, 2008
Messages
192
Hello,

I have a form where the user will fill it out and hit "submit" and I would like it to save the information to the table and send an email to me. what is the best solution?

Can one command button preform 2 functions.


Thank you in advance
 
Take a moment and search these forums, I know this has been addressed at least three times in the past 3 weeks.

-dK
 
No need for a button. You can do it in the "After Update" event of the form (or a number of other places).

The tough part is the e-mail and that can be quickly resolved by a quick search on the forums.
 
Thank you so much for your replies!

I chose the After Update event ont he form and it works great ....but now :) I noticed that it emails all of the records when I only want to see the new entry... is that possible?

Thank you in advance
 
I am not sure of the underlying code you are using to send the e-mail or how you are packaging the data (query, dlookup, etc.)

Whichever method you are using, it sounds as if you need a 'where clause' so that when you pull the record the where is equal to the index of the current record so it only sends you the current record and not all records.

-dK
 
Would this work ...... this is just an example .....

Macro Name Action
-----------------------------
mcrPrintRecord RunCommand
OpenReport

mcrPrintRecord Actions
--------------------------------------------------------------
RunCommand
Command: Save Record

OpenReport
Report Name: rptPrintRecord
View: Print Preview
Where Condition: [CustomerID]=Forms![Customers]![CustomerID]


Also, I'm very new at this so please forgive me .....still learning : )

Thank you
 
or this


Dim stDocName As String

stDocName = "rptnewsletterdatabase"
strWhere = "[Reference Number] = " & Me.[Reference Number]
DoCmd.SendObject acReport, stDocName, , strWhere

Thank you in advance
 
Your last post should work ...

Not sure about the one before because I avoid macros like vampires to sunlight.

-dK
 
"I avoid macros like vampires to sunlight."

LMAO ! ! !

Thank you for your replies ...I will try the second idea ....
 
Just wondering ...

where would I put this code?

Dim stDocName As String

stDocName = "rptnewsletterdatabase"
strWhere = "[Reference Number] = " & Me.[Reference Number]
DoCmd.SendObject acReport, stDocName, , strWhere

Thank you in advance
 
Anywhere ... =]

Or do you want my other smart a crooked-letter crooked-letter answer? :D

Kidding ... Supposing you want control over when it occurs so drag a command button to a form in design mode. When the wizard starts, cancel it. On the comman button properties, rename it to something like "cmdSendEmail" so you are following good naming techniques. Click the events tab and on the "On Click" event, click the ellipse (on the right of the row) and go to the code editor (visual basic editor or VBE).

It will open up the VBE and you will see something like ...

Sub cmdSendEmail_Click()
End Sub

These 'subs' are what controls what happens when you click the button so paste the code in between those two statements.

Also, I don't see something to save the record in there so adding that for the final bit ....
Code:
Sub cmdSendEmail_Click()
     Dim stDocName As String
 
     stDocName = "rptnewsletterdatabase"
     strWhere = "[Reference Number] = " & Me.[Reference Number]
 
     DoCmd.RunCommand acCmdSaveRecord
     DoCmd.SendObject acReport, stDocName, , strWhere
End Sub

You can use this code on an After Update event like if a user completed some field it automatically sends it, I am just exemplifying the example. Another way is to put it on a "Save" button for when the user is done making or updating the record.

-dK

EDIT: If you test and it is successful, your report probably won't be that pretty. You will probably need to explore .rtf to get some semblance of a decent enough report. You can change the send report options to different formats and this is one of them.
 
LMAO !

Thank you so much ! .... okay this is working ....kinda .... I have done exactly as you stated and this is what happens ....

I click on the cmd button

the records gets saved : )

My Outlook opens up with an Attachment and in the "to" field it has "Change request number" ....... wierd ....

so I popped in my own email and sent it ... I got the email ...however ... I seem to get all of the records wen I only what the newest one entered .....
 
Okay .. let's move some stuff around, add some other, take away another and finally throw some chicken bones and speak in tongues to hopefully get it working for you. In the QBE builder, edit the query for the report. In the criterial for the reference number column, use:

Code:
[Forms]![YourForm]![ControlName]

Change the form name and the control name that is bound to the reference number of the record.

On the button, let's modify it to customize it a bit so you can work this the way you want ....

Code:
Dim sSubject As String
Dim sToName As String
Dim sDocName As String
 
sDocName = "rptnewsletterdatabase"
sSubject = "New Record Added for " & Me.ReferenceControlName
sToName = "you@yourdomain.com"
 
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acSendReport, sDocName, acFormatRTF, _
sToName, , , sSubject, , False

Notice I added an address to send it to, a subject line that will include the reference number and set it to send in the .rtf format. Just more example. I didn't test this but I think I have all the commas correct and think for the quotes it's all correct but it may need more or less. Try it out and let me know.

-dK
 
Last edited:
Holy S#$! BATMAN !

It worked exactly how I wanted it to ! ! ! !

dkinley ...you are FREAK'in awesome !

This is unreal! ....can you be cloned and be sent to Canada?

Thank you so very much! bless you ...wow !
 
hehehehe ... I am guessing we had a good throw with the chicken bones, and oh yeah, I could be moving up there depending on what happens down here in the next 2 years. :D

Eh, it's small beans compared to some of the stuff I've seen real programmers on this site do. I've just hacked at that particular problem before.

Awesome it is working for you!

-dK
 

Users who are viewing this thread

Back
Top Bottom