Mail Merge Transaction

[Matthew DeAngelis]

I am using a reference book to help me write a transaction for my mail
merge. Basically, the transaction will create the source of the merge,
drop the date into a field on a table, and call the merge function. I
think the individual commands work, as they did before I included them
in a transaction. However, after the mail merge completes
(successfully), I get the following message: "No value given for one or
more required parameters." I think it probably has something to do
with the message box asking if the merge was successful, because the
transaction does not commit. Here is the code block:



Dim cnn As ADODB.Connection
Dim VbMsgBoxReject, VbMsgBoxMerge, VbMsgBoxSuccess, strQuerySQL,
strQuerySQL2 As String
Dim cmd1 As New ADODB.Command
Dim cmd2 As New ADODB.Command
Dim cmd3 As New ADODB.Command

VbMsgBoxReject = MsgBox("Would you like to register this deal as
rejected and update its status to 'Dead/No Action'?", vbYesNo)

If VbMsgBoxReject = vbYes Then
If Forms![frm: SpecificDeal]!Personal Or _
Forms![frm: SpecificDeal]!NoReject = True Then
MsgBox ("You cannot reject this deal. It is marked as
'Personal' or 'No Reject'.")
Exit Sub
Else
strQuerySQL = "UPDATE [tbl: Data - StatusOnReject] SET [tbl:
Data - StatusOnReject].DateNotice = Date()" & _
" WHERE ((([tbl: Data - StatusOnReject].DealID)
= [Forms]![frm: SpecificDeal]![DealID]))" & _
" WITH OWNERACCESS OPTION;"
strQuerySQL2 = "SELECT [tbl: Data - Deals].DealID, [tbl: Data -
Companies].Company, [tbl: Data - Companies].Contact1, [tbl: Data -
Companies].Email1, [tbl: Data - Deals].Personal, [tbl: Data -
Deals].NoReject INTO [tbl: Event - RejNotifyMergeSource]" & _
" FROM [tbl: Data - Companies] INNER JOIN [tbl:
Data - Deals] ON [tbl: Data - Companies].CompanyID = [tbl: Data -
Deals].CompanyID" & _
" WHERE ((([tbl: Data - Deals].DealID) =
[Forms]![frm: SpecificDeal]![DealID]) And (([tbl: Data -
Deals].Personal) = No) And (([tbl: Data - Deals].NoReject) = No))" & _
" WITH OWNERACCESS OPTION;"
Set cnn = CurrentProject.Connection
cnn.BeginTrans
cmd1.ActiveConnection = cnn
cmd2.ActiveConnection = cnn
cmd3.ActiveConnection = cnn
cmd1.CommandText = strQuerySQL
cmd2.CommandText = strQuerySQL2
cmd3.CommandText = MergeIt()
cmd1.Execute
cmd2.Execute
cmd3.Execute
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
cnn.CommitTrans
Else
cnn.RollbackTrans
End If
strQuerySQL = Null
strQuerySQL2 = Null
End If
End If


I would appreciate any help on this.


Regards,
Matt

 

[Matthew DeAngelis]

P.S. I realized that I cannot create the source for the merge and do
the merge in the same transaction; I fixed that problem, but the same
error message remains.


Matt