473,326 Members | 2,013 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Report Filter in VBA

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
4 10020
"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
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Richard | last post by:
Hi, I have a form based on a table. When I filter the form I want to run a report based on the same table with the same filter as the form. No problem until I want to filter a combo box where...
2
by: Vikrant | last post by:
Hey Friends I need to export an Access Report using a filter. I am using the method OutputTo m_pDoCmd->OutputTo( 3, // asOutputReport COleVariant(strReportName), // <report name>...
5
by: MGFoster | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I've converted an ACC97 .mdb file to an ACC2K2 .adp. A report that worked in ACC97 doesn't work in ACC2K2. Report setup: ACC97 ...
8
by: David Horsman | last post by:
I have a report that lists File-B. My macro runs this report using a query as a filter. The query uses two files, the parent File-B and with a 0-many relationship to File-C. The query selects...
1
by: lorirobn | last post by:
Hi, I have a report that works just fine. Now I would like to add the capability to choose selection criteria to limit what is displayed. I created several reports that do this, but they used...
1
by: dfw1417 | last post by:
I have used a query and report filter to return records related to a specific account id. I want to print a report including only the latest 6 records out of the set returned by the record filter. I...
1
by: Cara Murphy | last post by:
Hi There! Hoping you are able to help me with a filtering scenario: I have created a pop up form (Filter) to filter a report (Open Issues), which currently has a number of combo boxes which...
4
by: Fran | last post by:
I recently tried to use code for "Use a multi-select list box to filter a report" from Allen Browne in my database. I was able to add the code and adapt it to my needs, however I am getting an...
5
by: Andrew Meador | last post by:
I have a form (Change Card List by Status) with a check box (cboNOT) and a list box (lstStatus). There is an Open Report button that opens a report (Report - Change Card List) which uses a query...
3
by: franc sutherland | last post by:
Hello, I have a report which I filter using the me.filter command in the OnOpen event. Me.Filter = "OrderID=" & Forms!variable_form_name! Me.FilterOn = True I want to be able to open that...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.