Report Filter in VBA

[John Galt]

I need to save a report to an RTF and I am using OutputTo acReport:

DoCmd.OutputTo acReport, stDocName, acFormatRTF, TodaysDir & "-" &
"Name.rtf"

This command saves the report nicely, however I cannot filter the report
like I can on an OpenReport and providing a filter statement. Has anyone
found a good way to do this.

I do not want my user to open and preview the report. It must be done
programmaticall y.

I am using Access 2002.

Please advise.

Thanks in advance

[Lyle Fairfield]

"John Galt" <js**@yahoo.com > wrote in news:ePBTb.1124 2$Bv6.3291854
@news1.epix.net :

I need to save a report to an RTF and I am using OutputTo acReport:

DoCmd.OutputTo acReport, stDocName, acFormatRTF, TodaysDir & "-" &
"Name.rtf"

This command saves the report nicely, however I cannot filter the report
like I can on an OpenReport and providing a filter statement. Has anyone
found a good way to do this.

I do not want my user to open and preview the report. It must be done
programmaticall y.

I am using Access 2002.

Please advise.

Thanks in advance

One way is to set the RecordSource of your report to a saved query. Before
opening the report, modify the SQL of the query as in:

Const qryChargesName As String = "qryCharges "
Function fCreateAccountF ile(strAccountN umber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0). QueryDefs(qryCh argesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account = " &
_
Chr(34) & strAccountNumbe r & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumbe r &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges ", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountF ile "905 690-2185"
End Sub


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[John Galt]

I tried this and I get an error on the dim querydef statement.
It is not a choice in Access 2002 or I am missing something.
I get "User defined type not defined"

????

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

"John Galt" <js**@yahoo.com > wrote in news:ePBTb.1124 2$Bv6.3291854
@news1.epix.net :

I need to save a report to an RTF and I am using OutputTo acReport:

DoCmd.OutputTo acReport, stDocName, acFormatRTF, TodaysDir & "-" &
"Name.rtf"

This command saves the report nicely, however I cannot filter the report
like I can on an OpenReport and providing a filter statement. Has anyone found a good way to do this.

I do not want my user to open and preview the report. It must be done
programmaticall y.

I am using Access 2002.

Please advise.

Thanks in advance

One way is to set the RecordSource of your report to a saved query. Before
opening the report, modify the SQL of the query as in:

Const qryChargesName As String = "qryCharges "
Function fCreateAccountF ile(strAccountN umber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0). QueryDefs(qryCh argesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account = " &
_
Chr(34) & strAccountNumbe r & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumbe r &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges ", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountF ile "905 690-2185"
End Sub


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Lyle Fairfield]

"John Galt" <js**@yahoo.com > wrote in
news:W6******** *************@n ews1.epix.net:

I tried this and I get an error on the dim querydef statement.
It is not a choice in Access 2002 or I am missing something.
I get "User defined type not defined"

????

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

"John Galt" <js**@yahoo.com > wrote in news:ePBTb.1124 2$Bv6.3291854
@news1.epix.net :

> I need to save a report to an RTF and I am using OutputTo acReport:
>
> DoCmd.OutputTo acReport, stDocName, acFormatRTF, TodaysDir & "-"
> &
> "Name.rtf"
>
> This command saves the report nicely, however I cannot filter the
> report like I can on an OpenReport and providing a filter statement.
> Has anyone > found a good way to do this.
>
> I do not want my user to open and preview the report. It must be
> done programmaticall y.
>
> I am using Access 2002.
>
> Please advise.
>
> Thanks in advance

One way is to set the RecordSource of your report to a saved query.
Before opening the report, modify the SQL of the query as in:

Const qryChargesName As String = "qryCharges "
Function fCreateAccountF ile(strAccountN umber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0). QueryDefs(qryCh argesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account =
" &
_
Chr(34) & strAccountNumbe r & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumbe r &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges ", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountF ile "905 690-2185"
End Sub


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Set a reference to dao and qualify the qdf with dao as in
DAO.Qdf

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[John Galt]

Thanks.
I did get it working now!
"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** **********@130. 133.1.4...

"John Galt" <js**@yahoo.com > wrote in
news:W6******** *************@n ews1.epix.net:

I tried this and I get an error on the dim querydef statement.
It is not a choice in Access 2002 or I am missing something.
I get "User defined type not defined"

????

"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message
news:Xn******** ***********@130 .133.1.4...

"John Galt" <js**@yahoo.com > wrote in news:ePBTb.1124 2$Bv6.3291854
@news1.epix.net :

> I need to save a report to an RTF and I am using OutputTo acReport:
>
> DoCmd.OutputTo acReport, stDocName, acFormatRTF, TodaysDir & "-"
> &
> "Name.rtf"
>
> This command saves the report nicely, however I cannot filter the
> report like I can on an OpenReport and providing a filter statement.
> Has

anyone

> found a good way to do this.
>
> I do not want my user to open and preview the report. It must be
> done programmaticall y.
>
> I am using Access 2002.
>
> Please advise.
>
> Thanks in advance

One way is to set the RecordSource of your report to a saved query.
Before opening the report, modify the SQL of the query as in:

Const qryChargesName As String = "qryCharges "
Function fCreateAccountF ile(strAccountN umber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0). QueryDefs(qryCh argesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account =
" &
_
Chr(34) & strAccountNumbe r & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumbe r &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges ", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountF ile "905 690-2185"
End Sub


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Set a reference to dao and qualify the qdf with dao as in
DAO.Qdf

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)