> I am having problems using an update query. The code appears in VBA function
> designed to send emails to employees. I would like to have the query update
> based on who the email was just sent to, so that i know that an email was
> sent to that employee.
Thanks, however i have tried it. I got an error saying the database or query
is read-only. what do i do next then? I have included all of my code, that
might help. I think the problem might be the line:
Set rst = db.OpenRecordset("qryEmailReminder", dbOpenSnapshot)
However, if i remove the "dbOpenSnapshot" i get an error saying that "3020:
Update or CancelUpdate without AddNew or Edit." Thanks for all of your help.
Function Email()
'Set reference to Outlook
On Error GoTo Errhandler
'Dim strBCC As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
'Dim objEml As Outlook.MailItem
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailReminder", dbOpenSnapshot)
Set objOutl = CreateObject("Outlook.application")
'Set objEml = objOutl.createItem(olMailitem)
With rst
If .RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With
For i = 1 To rst.RecordCount
If Len(rst!EmployeeEmail) > 0 Then
strTo = rst!EmployeeEmail
Dim objEml As Outlook.MailItem
Dim strSQL As String
Set objEml = objOutl.createItem(olMailitem)
'With objEml
' .To = strTo
' .Subject = rst!Action & " Today"
' .Body = rst!Action & " with " & rst!Contact & " of " &
rst!Company & vbCrLf & "Notes: " & rst!Comments
'.Importance = olImportanceHigh
'.Send
'End With
'strSQL = "UPDATE qryEmailReminder SET qryEmailReminder.ReminderSent
= Yes WHERE (((qryEmailReminder.Company)=rst!Company) AND
((qryEmailReminder.Contact)=rst!Contact) AND
((qryEmailReminder.Action)=rst!Action) AND
((qryEmailReminder.Comments)=rst!Comments) AND
((qryEmailReminder.EmployeeEmail)=rst!EmployeeEmail))"
'CurrentDb().Execute strSQL, dbFailOnError
End If
Set objEml = Nothing
rst![ReminderSent] = True
rst.MoveNext
Next i
ExitHere:
Set objOutl = Nothing
'Set objEml = Nothing
Set rst = Nothing
Set db = Nothing
Exit Function
Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End Function
> hi J,
>
[quoted text clipped - 7 lines]
> mfG
> --> stefan <--
JKarchner - 13 Mar 2007 18:44 GMT
I figured out my problem. I needed to include a .Edit and a .Update section
to my code. It now looks like this:
rst.Edit
rst.EmailReminder = True
rst.Update
Thank you for all of your help!
> Thanks, however i have tried it. I got an error saying the database or query
> is read-only. what do i do next then? I have included all of my code, that
[quoted text clipped - 84 lines]
> > mfG
> > --> stefan <--