Append Query Problem

[sexton75 via AccessMonster.com]

Good morning,

Using VBA, I created code that will query a list of emails that need to be
sent out automatically. That worked fine and did what I needed it to do.

I wanted to take it a step further and use an append query to record the
email event in the activities table. Unfortunately, I cant get it to work.
The error I get is that it pops up a box requesting the ticketid and
contactid when the append query runs. I am at a loss, so any help would be
great! Here is what I have:

Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set RS = MyDB.OpenRecordset("Qry_ErrorEmail")
lngRSCount = RS.RecordCount
strto = RS!Email
strdate = RS!DateIn
strtransactiono = RS!ProcessingID_pk
strclientname = RS!ClientName
strregistration = RS!RegistrationCategory
strerrorno = RS!TicketID_pk
strticket = RS!Ticket
strcontact = RS!ContactID_fk
Dim strappendactivity As String


strappendactivity = "INSERT INTO Tbl_Activities ( TicketID_fk, [Date], [Time],
ActivityType_fk, ActivityCategory_fk, ActivityGroup_fk, ActivityStatus_fk,
ContactID_pk, Notes ) " & _
"SELECT [Qry_ErrorEmail]![ticketid_pk] AS TicketID, Date() AS Adate, Time()
AS ATime, 5 AS ActivityType, 2 AS ActivityCategory, 4 AS ActivityGroup, 4 AS
Status, [Qry_ErrorEmail]![contactid_fk] AS contactid, ""<SYSTEM GENERATED
NOTE>: Initial E-Mail Notification Sent"" AS Notes; "

If lngRSCount = 0 Then
MsgBox "No Errors were found.", vbInformation
Else
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
lngCount = lngCount + 1

DoCmd.SendObject acSendNoObject, , , strto, , , "Order Error Notification",
"You are receiving this message because we need further information on
business you submitted. Please contact our office at your earliest
convenience!", False
DoCmd.SetWarnings False
DoCmd.RunSQL strappendactivity
DoCmd.SetWarnings True
RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close
MsgBox "Done sending Order Errors. ", vbInformation, "Done"
Exit Sub
End Sub

 

[Rob Wills]

Hi,

You need to mesh your variables with your SQL string

So your select query should look something like this

"Select " & RS!TicketID_PK & " as ticketID, " & rs!ContactID & " as Contact
ID ".....

(the above code is assuming that your ID's are numeric - otherwise you'll
need to add a ' before and after the "'s - so contact ID will look like....
'" & rs!ContactID & "' as Contact ID )

HTH
Rob

 

[sexton75 via AccessMonster.com]

Thanks Rob!

That eliminated the error I was getting, however now it only appends the
information from the first record. To test it, I entered data that allowed
the query to return two records with diffferent ticketid's and contactid's.
It appended two records, but it appended the data from the first record twice.
It looks like it is not running through the loop and pulling the contactid
and ticketid from the 1st record only. Hope that makes sense. Any ideas?

-Brad

Hi,

You need to mesh your variables with your SQL string

So your select query should look something like this

"Select " & RS!TicketID_PK & " as ticketID, " & rs!ContactID & " as Contact
ID ".....

(the above code is assuming that your ID's are numeric - otherwise you'll
need to add a ' before and after the "'s - so contact ID will look like....
'" & rs!ContactID & "' as Contact ID )

HTH
Rob

Good morning,

[quoted text clipped - 57 lines]

Exit Sub
End Sub

 

[Rob Wills]

Hi Brad,

Make sure the variables that you are using are being updated accordingly....

If in doubt use F8 to step through the code and use your locals window to
check the variables at each step....

HTH
Rob

Thanks Rob!

That eliminated the error I was getting, however now it only appends the
information from the first record. To test it, I entered data that allowed
the query to return two records with diffferent ticketid's and contactid's.
It appended two records, but it appended the data from the first record twice.
It looks like it is not running through the loop and pulling the contactid
and ticketid from the 1st record only. Hope that makes sense. Any ideas?

-Brad

Hi,

You need to mesh your variables with your SQL string

So your select query should look something like this

"Select " & RS!TicketID_PK & " as ticketID, " & rs!ContactID & " as Contact
ID ".....

(the above code is assuming that your ID's are numeric - otherwise you'll
need to add a ' before and after the "'s - so contact ID will look like....
'" & rs!ContactID & "' as Contact ID )

HTH
Rob

Good morning,

[quoted text clipped - 57 lines]

Exit Sub
End Sub