Re: Need VBA Codes for Update or Insert statement in MS Access
- From: John Nurick <j.mapSoN.nurick@xxxxxxxxxxxxxx>
- Date: Tue, 28 Feb 2006 06:55:51 +0000
I'd be inclined to do this by updating SysInfo.TestStatusID after every
successful .Send.
If qryContactList is updatable, you could do it very simply by including
TestStatusID in rst, opening the latter as dbForwardOnly and then having
stuff like this:
...
.Send
rst.Edit
rst.TestStatusID = 6
rst.Update
...
Otherwise, it would be something like this:
Dim strFlagSQL As String
...
.Send
strFlagSQL = "UPDATE SysInfo SET TestStatusID = 6 WHERE SYS_ID=" _
& rst.Fields("SYS_ID").Value & ";"
db.Execute strFlagSQL, dbFailOnError
...
On 27 Feb 2006 14:57:21 -0800, "FA" <October.April@xxxxxxxxx> wrote:
I would really really appreciate if someone help me out with this
issue. I have a query that will run after in the following codes,
Dim strTo As String
Dim strSubject As String
Dim varMsg As Variant
Dim varAttachment As Variant
'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("qryContactList", 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!PRA_CTAC_NME) > 0 Then
strTo = rst!PRA_CTAC_NME
'MsgBox emailBody
strSubject = rst!SYS_NME & " " & " " & "PRA Results"
varMsg = emailBody
Dim objEml As Outlook.MailItem
Set objEml = objOutl.CreateItem(olMailItem)
With objEml
.To = strTo
.Subject = strSubject
If Not IsNull(varMsg) Then
.Body = varMsg
End If
' Uncomment for attachment
' If Not IsMissing(varAttachment) Then
' .Attachments.Add varAttachment
' End If
.Send
End With
End If
Set objEml = Nothing
rst.MoveNext
Next I
ExitHere:
Set objOutl = Nothing
'Set objEml = Nothing
Set rst = Nothing
Set db = Nothing
Exit Sub
Errhandler:
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
The above codes runs the query and send email to all PRA_CTAC
In query "qryContactList" i have a column called TestStatusID from
Table SysInfo.
I want to do something like the following;
When user run this code it runs the query and email all the PRA_CTAC
and then insert "6" into filed TestStatusID in Table SysInfo for only
those records that were in the query. All records in the query are
coming from the main table SysInfo in which the Primary Key is SYS_ID.
TestStatusID is acting as a flag meaning the email has been sent to
these people.
So i need to put so me Update statement after this code in VBA form. If
anyone can help me out, i would really really appreciate it.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
.
- Follow-Ups:
- References:
- Prev by Date: Re: Retrieving records where yes/no data type is checked
- Next by Date: Re: Link excel to a query?
- Previous by thread: Need VBA Codes for Update or Insert statement in MS Access
- Next by thread: Re: Need VBA Codes for Update or Insert statement in MS Access
- Index(es):
Relevant Pages
|
|
