By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Report Filter in VBA

P: n/a
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
programmatically.

I am using Access 2002.

Please advise.

Thanks in advance


Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"John Galt" <js**@yahoo.com> wrote in news:ePBTb.11242$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
programmatically.

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 fCreateAccountFile(strAccountNumber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0).QueryDefs(qryChargesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account = " &
_
Chr(34) & strAccountNumber & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumber &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountFile "905 690-2185"
End Sub


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #2

P: n/a
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.11242$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
programmatically.

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 fCreateAccountFile(strAccountNumber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0).QueryDefs(qryChargesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account = " &
_
Chr(34) & strAccountNumber & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumber &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountFile "905 690-2185"
End Sub


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

Nov 12 '05 #3

P: n/a
"John Galt" <js**@yahoo.com> wrote in
news:W6*********************@news1.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.11242$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 programmatically.
>
> 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 fCreateAccountFile(strAccountNumber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0).QueryDefs(qryChargesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account =
" &
_
Chr(34) & strAccountNumber & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumber &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountFile "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)
Nov 12 '05 #4

P: n/a
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*********************@news1.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.11242$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 programmatically.
>
> 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 fCreateAccountFile(strAccountNumber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0).QueryDefs(qryChargesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account =
" &
_
Chr(34) & strAccountNumber & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumber &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountFile "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)

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.