query parameters and TOP statement?

[wgoldfarb]

I have an Access form that performs a Word mailmerge, using a query as its
data source. The name of the Access query is 'AONLQuery' and the code for the
mail merge is the following:

....
strConnection = "Query AONLQuery"
strSql = "SELECT TOP " & MaxRecNum & " * FROM [AONLQuery]"
Set objWord = GetObject(DocSource, "Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource name:=Application.CurrentProject.FullName, _
ConfirmConversions:=False, _
LinkToSource:=True, _
Connection:=strConnection, _
SQLStatement:=strSql
objWord.MailMerge.SuppressBlankLines = True
objWord.MailMerge.Execute

This code works fine as it is now. The variable MaxRecNum holds the maximum
number of records to be used for the mail merge. It takes its value from a
control on teh form and is my way of allowing the user to specify how many
records to use for the merge.

My problem is as follows: once I conduct the mail merge I would like to
modify the records used for the merge by changing a Field called "status".
But the TOP keyword used above returns records at random, so I have no way of
knowing which records were used during the merge. So, is there a way to first
create a new recordset (or Table, or Query) with a SQL statement similar to
the one above, and then use this new recordset (or Table, or Query) as a
datasource for the mailmerge? This way, once the merge is completed I could
use an SQL statement on the new recordset to change the value of the status
field for all these records. Or is there a better way to do this?

Thanks,

William

 

[Douglas J. Steele]

TOP is intended to be used with a sorted query. Try putting an ORDER BY
clause in your SQL.

 

[wgoldfarb]

Thanks, I will order the table as you suggest. The second part of my question
(about using a temporary recordset as a data source for a mail merge) is
still causing some trouble, but I guess that belongs in the Microsoft Word
message board

TOP is intended to be used with a sorted query. Try putting an ORDER BY
clause in your SQL.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have an Access form that performs a Word mailmerge, using a query as its
data source. The name of the Access query is 'AONLQuery' and the code for
the
mail merge is the following:

...
strConnection = "Query AONLQuery"
strSql = "SELECT TOP " & MaxRecNum & " * FROM [AONLQuery]"
Set objWord = GetObject(DocSource, "Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource
name:=Application.CurrentProject.FullName, _
ConfirmConversions:=False, _
LinkToSource:=True, _
Connection:=strConnection, _
SQLStatement:=strSql
objWord.MailMerge.SuppressBlankLines = True
objWord.MailMerge.Execute

This code works fine as it is now. The variable MaxRecNum holds the
maximum
number of records to be used for the mail merge. It takes its value from a
control on teh form and is my way of allowing the user to specify how many
records to use for the merge.

My problem is as follows: once I conduct the mail merge I would like to
modify the records used for the merge by changing a Field called "status".
But the TOP keyword used above returns records at random, so I have no way
of
knowing which records were used during the merge. So, is there a way to
first
create a new recordset (or Table, or Query) with a SQL statement similar
to
the one above, and then use this new recordset (or Table, or Query) as a
datasource for the mailmerge? This way, once the merge is completed I
could
use an SQL statement on the new recordset to change the value of the
status
field for all these records. Or is there a better way to do this?

Thanks,

William