Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old January 13th, 2013, 04:12 AM
edman edman is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2013
Posts: 1 edman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 22 m 14 sec
Reputation Power: 0
Angry Opening a report and pass parameters to qry

Hi Team
I have a problem and write to see if you can give me some assistance. This is the first time in many years I have to ask for some assistance so I would appreciate nay help you can give me? Let me explain the scenario.

I have a user form called Frm_reports where the user can select a variety of filters

There is only one report available to the user at this time.

The form has preview button.

On clicking the preview button it should open the report filtered by the passed parameters..

I wish to pass the parameters to the query on the fly and then open the filtered report.

The code will not run?

Here is the code:
Public Sub cmdPreview_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
If rmselectreport.ItemsSelected.count = 0 Or rmselectperiod.ItemsSelected.count = 0 Then
MsgBox "You must select both a report and period(s) to run a report." & vbCr & vbLf _
& "Please check or reenter the requiired report / periods", vbOK, "Selection Error"
Exit sub
Else
Set qdf = dbs.QueryDefs("Qry_rpt_CR") <<
qdf.Parameters(0) = “*”
qdf.Parameters(4) = Forms!frm_reports.rmselectperiod.Column(0, 0)
qdf.Parameters(3) = Forms!frm_reports.rmselectperiod.Column(0, 1)
qdf.Parameters(2) = Forms!frm_reports.rmselectperiod.Column(0, 2)
qdf.Parameters(1) = Forms!frm_reports.rmselectperiod.Column(0, 3)
docmd.openreport(”rpt_cr”) <<
endif
'Close all objects
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing
End Sub

Qry_rpt_cr is the query for the report which in turns runs from another query that has five parameters

The problems I have are on lines with <<

The first line says Object Variable not set? What does this mean?

Do I need so open the report in a different way??

If you could provide some help that would be greatly appreciated.
Thanks
edman

Reply With Quote
  #2  
Old January 18th, 2013, 03:46 PM
June7's Avatar
June7 June7 is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Oct 2010
Location: The Great Land
Posts: 256 June7 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 Days 9 h 29 m 29 sec
Reputation Power: 5
I don't use QueryDefs to pass filter criteria to report. I avoid dynamic parameters in queries. I use the WHERE CONDITION argument of DoCmd.OpenReport (or OpenForm). Maybe setting QueryDef parameters is more efficient than building sql filter string in VBA - just don't know.

Why do you need all 4 values from combobox as criteria? Is one of them not a unique identifier?
__________________
I like to know if my suggestion helped you. One way to let me know is by clicking scales at top right corner of post.
Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Opening a report and pass parameters to qry


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap