Query By Form

I have a query by form whereby the form has a Command Button to run a module
which checks to see if the query has records. If the record count is 0 it
opens a Microsoft Word Document and the document is to mail merge to the
resulting query.

If the count is 0 then its just a message box to say there are no records
for that query.

Now my problem is that i cannot get the word document to use the query as
its data source, it returns no records. As the query is not actually run by
the data source the query is not current with the information from the form.
As a work around I export the query to an excel spreadsheet and then use the
excel spreadsheet as the data source for the word document.

Is there some way that i can get the query to update and store the
information from the query by form so I can directly use the query as the
data source. Module code follows:

Option Compare Database

Public Function OpenTYLLetters()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("qryThankYouLetters")
qdf.Parameters(0) = _
Forms!frmThankYouLetters!TYLBatchNumber
Set rst = qdf.OpenRecordset

'Set rst = CurrentDb.OpenRecordset("QryThankYouLetters")
If rst.RecordCount 0 Then
DoCmd.RunMacro "TYLtoExcel" 'Exports the query to an Excel Spreadsheet
Application.FollowHyperlink fGetSpecialFolderLocation(CSIDL_PERSONAL) &
"\UCP\Thank You _
Letters\CoverAllThankyouLetter.doc" ' Opens the word document and
datasource

Else
MsgBox "No records found for this batch number."
End If

rst.Close
qdf.Close

'Do Until rst.EOF
' Debug.Print rst!BatchNumber
' rst.MoveNext
'Loop

'rst.Close

End Function

Use the SQL Property of the qdf to modify the SQL String of the Query
(replacing the parameters with explicit values from your Form) so that you
don't have parameters in your Query.

IIRC. parametrised Queries are not available for selection as the DataSource
for MailMerge doc.

--
HTH
Van T. Dinh
MVP (Access)





"Craig" wrote in message
...
I have a query by form whereby the form has a Command Button to run a
module
which checks to see if the query has records. If the record count is 0
it
opens a Microsoft Word Document and the document is to mail merge to the
resulting query.

If the count is 0 then its just a message box to say there are no records
for that query.

Now my problem is that i cannot get the word document to use the query as
its data source, it returns no records. As the query is not actually run
by
the data source the query is not current with the information from the
form.
As a work around I export the query to an excel spreadsheet and then use
the
excel spreadsheet as the data source for the word document.

Is there some way that i can get the query to update and store the
information from the query by form so I can directly use the query as the
data source. Module code follows:

Option Compare Database

Public Function OpenTYLLetters()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("qryThankYouLetters")
qdf.Parameters(0) = _
Forms!frmThankYouLetters!TYLBatchNumber
Set rst = qdf.OpenRecordset

'Set rst = CurrentDb.OpenRecordset("QryThankYouLetters")
If rst.RecordCount 0 Then
DoCmd.RunMacro "TYLtoExcel" 'Exports the query to an Excel Spreadsheet
Application.FollowHyperlink fGetSpecialFolderLocation(CSIDL_PERSONAL) &
"\UCP\Thank You _
Letters\CoverAllThankyouLetter.doc" ' Opens the word document and
datasource

Else
MsgBox "No records found for this batch number."
End If

rst.Close
qdf.Close

'Do Until rst.EOF
' Debug.Print rst!BatchNumber
' rst.MoveNext
'Loop

'rst.Close

End Function

Although I understand what I need to do, I don't know how. Can anyone point
me in the right direction.

Thanks
Crait

"Van T. Dinh" wrote in message
...
Use the SQL Property of the qdf to modify the SQL String of the Query
(replacing the parameters with explicit values from your Form) so that you
don't have parameters in your Query.

IIRC. parametrised Queries are not available for selection as the
DataSource
for MailMerge doc.

--
HTH
Van T. Dinh
MVP (Access)





"Craig" wrote in message
...
I have a query by form whereby the form has a Command Button to run a
module
which checks to see if the query has records. If the record count is
0
it
opens a Microsoft Word Document and the document is to mail merge to the
resulting query.

If the count is 0 then its just a message box to say there are no
records
for that query.

Now my problem is that i cannot get the word document to use the query
as
its data source, it returns no records. As the query is not actually run
by
the data source the query is not current with the information from the
form.
As a work around I export the query to an excel spreadsheet and then use
the
excel spreadsheet as the data source for the word document.

Is there some way that i can get the query to update and store the
information from the query by form so I can directly use the query as
the
data source. Module code follows:

Option Compare Database

Public Function OpenTYLLetters()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("qryThankYouLetters")
qdf.Parameters(0) = _
Forms!frmThankYouLetters!TYLBatchNumber
Set rst = qdf.OpenRecordset

'Set rst = CurrentDb.OpenRecordset("QryThankYouLetters")
If rst.RecordCount 0 Then
DoCmd.RunMacro "TYLtoExcel" 'Exports the query to an Excel Spreadsheet
Application.FollowHyperlink fGetSpecialFolderLocation(CSIDL_PERSONAL) &
"\UCP\Thank You _
Letters\CoverAllThankyouLetter.doc" ' Opens the word document
and
datasource

Else
MsgBox "No records found for this batch number."
End If

rst.Close
qdf.Close

'Do Until rst.EOF
' Debug.Print rst!BatchNumber
' rst.MoveNext
'Loop

'rst.Close

End Function