Updating Query through VBA

[Guest]

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. My code is listed below:

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

Thanks for your help.

 

[Stefan Hoffmann]

hi J,

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.

I assume that your code loops over a recordset when sending the e-mail?
Why not use this loop, e.g. rs![EmailSent] = True?


mfG
--> stefan <--

 

[Guest]

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,

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.

I assume that your code loops over a recordset when sending the e-mail?
Why not use this loop, e.g. rs![EmailSent] = True?


mfG
--> stefan <--

 

[Guest]

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
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,

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.

I assume that your code loops over a recordset when sending the e-mail?
Why not use this loop, e.g. rs![EmailSent] = True?


mfG
--> stefan <--