C
Craig
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
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