Need VBA Codes for Update or Insert statement in MS Access

[FA]

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]

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

 

[FA]

Thanks John, i copied your code
strFlagSQL = "UPDATE SysInfo SET TestStatusID = 6 WHERE SYS_ID=" _
& rst.Fields("SYS_ID").Value & ";"
db.Execute strFlagSQL, dbFailOnError
under my .send its giving me error "3265: Item not found in this
collection Ok!" i do have TestStatusID in my query and in the table as
well. Although i am not including TestStatusID and SYS_ID in the email
but i do have it in the query ContactList.

What would be the possible problem here ?

 

[FA]

Sorry John i forgot to include the SYS_ID in the query and its sending
emails now but still not updating the table SysInfo. its giving me this
error now:
"3622: You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an identity column."

My tables are linked with sql server but i dont know if that should be
a problem.

Please help me i would really really appreciate it.

Moe

 

[John Nurick]

I know very little about working with SQL Server, but I'd be inclined to
do what the message says.

I presume you have the line

Set rst = db.OpenRecordset("qryContactList", dbOpenSnapshot)

or something similar in your code. Just change it to

Set rst = db.OpenRecordset("qryContactList",_
dbOpenSnapshot, dbSeeChanges)

and see what happens. If that doesn't fix it, maybe talk to your local
SQL Server admin.

 

[FA]

The problem is not on the in Set rst =
db.OpenRecordset("qryContactList",_
dbOpenSnapshot, dbSeeChanges
because its pulling the query results and emailing the contact persons,
the problem is after
..send where i put the update statement
now i have something like this
..Send
Set rst = CurrentDb().OpenRecordset("UPDATE dbo_SYS_INFO SET
dbo_SYS_INFO.TEST_STAT_ID = 6 WHERE dbo_SYS_INFO.SYS_ID_CODE =" _
& rst.Fields("SYS_ID_CODE").Value & ";", dbOpenSnapshot, dbSeeChanges)


Now its giving me the error "3219: Invalid Operation"

i dont know what to do and what i am doing wrong now.

 

[John Nurick]

What you're doing is trying to open a recordset using a SQL statement
that doesn't return a recordset.

In my first message, I said you should do this:

.Send
strFlagSQL = "UPDATE SysInfo SET TestStatusID = 6 WHERE SYS_ID=" _
& rst.Fields("SYS_ID").Value & ";"
db.Execute strFlagSQL, dbFailOnError

That uses the table names and field names you posted. In your latest
message all the names are different. This means I can't rely on your
descriptions of what's happening, which makes it much harder to work out
what's going on (and doesn't exactly encourage me to try).

You also didn't use the code I posted, but something quite different.
Try again. Modify the code above so it uses your actual table and field
names. Remember that in this expression
rst.Fields("SYS_ID")
the "SYS_ID" is the name of the field in the *recordset* - i.e. the name
from qryContactList, not the name of the corresponding field in the
table SysInfo (or dbo_SYS_INFO or whatever its real name is).

At the db.Execute line, strFlagSQL should look like this:

UPDATE tablename SET fieldname = 6
WHERE otherfieldname = 99;

where 99 is the current value of rst!SysInfo (or whatever its real name
is).

I've been assuming that SYS_ID or whatever it's called is a number
field. If it's text, you need to add quotes to the SQL to produce
something like this:

UPDATE tablename SET fieldname = 6
WHERE otherfieldname = 'ABC';