PDA

View Full Version : Query By Form


Craig
May 28th, 2004, 03:33 PM
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

Van T. Dinh
May 29th, 2004, 04:17 AM
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
>
>
>
>
>

Craig
June 1st, 2004, 01:10 AM
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
> >
> >
> >
> >
> >
>
>

Google