Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

requery a report

Posted on 2009-02-14
8
Medium Priority
?
816 Views
Last Modified: 2013-11-28
access 2000 report

        If IsLoaded("rptTransGroups") = True Then
            DoCmd.Close acReport, "rptTransGroups"

so that I can reopen the report with new criteria

Function IsLoaded(ByVal strObjName As String) As Integer
' Returns True if the specified form is loaded.
   
    Const conDesignView = 0
    Const conObjStateClosed = 0

    IsLoaded = False
    If SysCmd(acSysCmdGetObjectState, acForm, strObjName) <> conObjStateClosed Then
        If Forms(strObjName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If
    If SysCmd(acSysCmdGetObjectState, acReport, strObjName) <> conObjStateClosed Then
        If Reports(strObjName).CurrentView <> conDesignView Then <<<<<<<<<<<<<<<<<<
            IsLoaded = True
        End If
    End If

End Function

The line in <<<<<<<<<< above is giving me
Run time error 2465      
Application defined or object defined error.

The report is open already but I need to roguery it since its data are based on a query. So how do I do that, is there a report roguery or close and reopen? And how to fix this?
Thanks
0
Comment
Question by:samj
  • 4
  • 2
  • 2
8 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23642978
samj,

If the Report is based on a query, then every time you open it, it refreshes and displays the most current data.

So unless you are leaving the report open on the screen for "Hours" at a time, I don't see the need to refresh it.

That being said, ...In Access 2007 there is a "Refreh All" button that will refresh (requery) the Report on demand.

In Access 2003 and earlier, you may just have to close then re-open the report.

JeffCoachman
0
 
LVL 58

Accepted Solution

by:
harfang earned 300 total points
ID: 23642992
Reports do not have a current view, and cannot be re-queried. You need to close and reopen. From an interface point of view, it makes little sense to test for design view. If you are designing the report, you would not run the same code anyway, would you?

It looks as though you are trying to adapt something that works for forms and use it for reports, but they are quite different beasts. Do not mix form and report management in the same function, it's only confusing.

(°v°)
    Const RPT_NAME = "rptTransGroups"
 
    If SysCmd(acSysCmdGetObjectState, acReport, RPT_NAME) Then _
        DoCmd.Close acReport, RPT_NAME
    DoCmd.OpenReport RPT_NAME, acViewPreview

Open in new window

0
 
LVL 58

Expert Comment

by:harfang
ID: 23642997
Hi Jeff. I don't know much about Access 2007... is there an equivalent RunCommand constant, too?

(°v°)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:samj
ID: 23643260
I am getting Run time error 3078, at the line is marked below.

        Const RPT_NAME = "rptTransGroups"
        If SysCmd(acSysCmdGetObjectState, acReport, RPT_NAME) Then
            DoCmd.Close acReport, RPT_NAME
            DoCmd.OpenReport RPT_NAME, acViewPreview, , crit
        Else
            DoCmd.OpenReport RPT_NAME, acViewPreview, , crit <<<<<<<<<<<<<<<<<<<<<<<
        End If


?crit
[TrnsDate] >=#02-01-2002# AND  [TrnsDate] <= #02-28-2002#
0
 
LVL 58

Expert Comment

by:harfang
ID: 23643352
Error 3078 is "The Microsoft Jet database engine cannot find the input table or query"... This would mean your report doesn't work at all at the moment... Does it? Can you open it from the interface? Does it work without the criteria (which is perfectly correct, assuming there is a field TrnsDate in the report's record source)?

(°v°)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23643549
harfang,

" is there an equivalent RunCommand constant, too?"

I could not find any.
I tried these:
(which may or may not be the same in Access 2003 and lower)

    'DoCmd.RunCommand acCmdRefresh
    'DoCmd.RunCommand acCmdRefreshData
    'DoCmd.RunCommand acCmdRefreshPage
    'DoCmd.RunCommand acCmdDiscardChangesAndRefresh

... and none refreshed the report, the only thing that dynamically refreshed the report was clicking the "Refresh All" button from the Records "chunk" in Accecess 2007.

Jeff
0
 
LVL 58

Expert Comment

by:harfang
ID: 23643569
Jeff,

Perhaps Access 2007 does it like we did here: close and reopen the report? On the other hand, there are so many acCmd constants, it's hard to get an overview. I sometimes copy-paste the entire list of constants from help to a text editor, just so I can search for key words among them...  I should really take the time to study that version a bit...

samj,

I take it you found what was wrong with the report. Kudos and success with your project!
(°v°)
0
 

Author Comment

by:samj
ID: 23643635
Thanks harfang. all is well.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question