How to get last record in table then email that record

[mrbboy]

I have a table for client complaints, tbl_CARGeneral. The fields are:

tbl_CARGeneral
CARID <autonumber>
Initiated By <Text>
Date Initiated <Date>
Status <Text>

I have a form, frm_CARNew, where users can log the complaints into tbl_CARGeneral. I then copied this form (frm_CARReview) then set the data entry property to No. I also set the recordsource to a query that gives me the last record in tbl_CARGeneral. When frm_CARNew is closed, frm_CARReview opens up and the last record in tbl_CARGeneral is shown. I then have a command button that emails this last record. Is it possible to email the last record entered into tbl_CARGeneral automatically when frm_CARNew is completed so that I don't have to open frm_CARReview?

 

[TheAceMan1]

How are ya mrbboy . . .

[blue]Is it possible to email the last record entered into tbl_CARGeneral automatically when frm_CARNew is completed so that I don't have to open frm_CARReview?[/blue][/quote]
Sure is! . . . just access the table [blue]tbl_CARGeneral[/blue] directly with a recordset and [blue]MoveLast[/blue] or use a table definition . . .

[img]http://home.nyc.rr.com/aceman1/Graphics/Calvin.gif[/img] [COLOR=darkgoldenrod][b]See Ya! .  .  .  .  .  .[/b][/color]

Be sure to see thread181-473997

 

[mrbboy]

Could you please show me an example?

 

[KenReay]

Hi

"Is it possible to email the last record entered into tbl_CARGeneral automatically when frm_CARNew is completed so that I don't have to open frm_CARReview?2

I assume that by "last record" you mean, the record you have just entered or updated?

In which case

Docmd.runcommand acCmdSaveRecord

Docmd.sendobject are probably the commands you are looking for, see

http://www.peterssoftware.com/c_emfa.htm

for example of how to use SendObject

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheAceMan1]

mrbboy . . .

If you put the button on [blue]frm_CARNew[/blue], sample code for the [blue]Click[/blue] event would look like:

[blue]   Dim rst As DAO.Recordset
   [green]'Dim Email Variables[/green]
   
   If Me.Dirty Then Me.Dirty = False
   Set rst = Me.RecordsetClone
   [green]'Set Email Objects[/green]
   rst.MoveLast
   
   [green]'Email code here[/green]
   
   [green]'set Email Objects = Nothing[/green]
   Set rst = Nothing[/blue]

. . . and sample code for emailing from a db:

[blue]   Dim email, ref, origin, destination, notes As String
   Dim objOutlook As Outlook.Application
   Dim objEmail As Outlook.MailItem
   
   [green]'gather information from your form.  this sets the string variable to your fields[/green]
   email = Me!email
   ref = Me!ref
   origin = Me!origin
   destination = Me!destination
   notes = Me!notes
   
   [green]'creates an instance of Outlook[/green]
   Set objOutlook = CreateObject("Outlook.application")
   Set objEmail = objOutlook.CreateItem(olMailItem)
   
   [green]'creates and sends email[/green]
   With objEmail
       .To = email
       .Subject = ref & " " & origin & " " & destination
       .Body = notes
       .Send
   End With
   
   [green]'closes outlook[/green]
   Set objEmail = Nothing
   objOutlook.Quit[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[mrbboy]

Aceman,

Instead of gathering information from the form, the email should be gathering information from the table, tbl_CARGeneral, correct?

 

[TheAceMan1]

. . . [blue]corrected![/blue]

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   [green]'Dim Email Variables & Objects[/green]
   
   If Me.Dirty Then Me.Dirty = False
   Set db = CurrentDb
   Set rst = db.OpenRecordset("tbl_CARGeneral", dbOpenDynaset)
   [green]'Set Email Objects[/green]
   rst.MoveLast
   
   [green]'Email code here[/green]
   
   [green]'set Email Objects = Nothing[/green]
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[mrbboy]

I am getting this error message when I run the code "Microsoft can't find the field "I" referred to in your expression." and highlights the OutlookTask.Subject line. Please help.

Dim rst As DAO.Recordset, db As Database
Dim OutlookApp As New Outlook.Application, OutlookTask As Outlook.TaskItem
Dim AssignedTo As Outlook.Recipient, olNS As Outlook.Namespace
Dim email As String

If Me.Dirty Then Me.Dirty = False
Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_CARGeneral", dbOpenDynaset)
Set OutlookApp = CreateObject("Outlook.Application")
Set olNS = OutlookApp.GetNamespace("MAPI")
olNS.Logon
Set OutlookTask = OutlookApp.CreateItem(olTaskItem)


rst.MoveLast

email = "email@domain.net"
OutlookTask.Assign
Set AssignedTo = OutlookTask.Recipients.Add(email)
OutlookTask.Subject = [Initiated By] & " has made a complaint. See CAR Number " & _
[CARID] & " to view the details of this complaint."
OutlookTask.Body = [Description]
OutlookTask.DueDate = Date + 14
OutlookTask.Importance = (olImportanceHigh)
OutlookTask.ReminderSet = True
OutlookTask.Save
OutlookTask.Send
OutlookTask.Display
OutlookApp.Quit

Set OutlookTask = Nothing
Set db = Nothing
Set rst = Nothing


I forgot to include that tbl_CARGeneral also contains te text field Description.

 

[TheAceMan1]

mrbboy . . .

Missing specification:

[blue]Dim rst As DAO.Recordset, db As [purple][b]DAO[/b][/purple].Database[/blue]

You never [blue]reference the recordset!:[/blue]

[blue]   OutlookTask.Subject = [purple][b]rst![/b][/purple][Initiated By] & " has made a complaint." & _
                        "See CAR Number " & [purple][b]rst![/b][/purple]CARID & _
                        " to view the details of this complaint."
   OutlookTask.Body = [purple][b]rst![/b][/purple]Description[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[mrbboy]

Aceman,

Thank you so much for your help. It works great.