Calling a Form from a Report

[DCrake]

Take off the X from the form (properties Close Button) and force them to click a close button. Also have a cancel button to allow them to close the form without running the report. The close button should say Print, whereby the user is acknowledging they are printing the report. When the report is finished then close the form.

David

 

[seohadczy]

Your users should never see the database window

 

[gemma-the-husky]

you can do all this, surely

in the reports open event, popup a form.
suspend processing while the form is open, ands save the entered parameters in global variables. you can even have a cancel button

when the popup closes, the report processing will continue.
if gblcancel then exit the report

otherwise, use the entered variables in the source query

when the form opens, it can even use the last saved values from the global variables
so it doesnt really matter whether the form opens the report, or the report opens the form, does it?

---------
with the MS switchboard one of the menu options is open a rpeort, so you dont even have to worry about users seeing the dbs window, and the interfaxe is all ready for you if you want it.

 

[Simon_MT]

A Menu system is critical to provide a consistent user interface. I resolved a similar situation by putting a Dailogue Box before the Sales Reports from a Menu:

Open Dialogue Form
Enter Start Date
Enter End Date

Two Buttons

Detail Report
Summary Report

On Click (Detail or Summary Button)
DoCmd.OpenReport
DoCmd.OpenForm Dialogue Form (Hidden)

On Report Close
DoCmd.OpenForm Dialogue Form (Normal)

Users may want both Reports.

I went a little further and have a table of Reports that users can select.

Simon

Here's an example of a Menu.

http://www.designedlogic.com/Screens/MenuSales.jpg

 

[The_Doc_Man]

I'm with Bob and SQL Hell here.

Use a splash screen (which is an unbound form, usually) rather than allowing user-level access to a database window. When they click on the "Open XYZ Report" you can have code under the splash pop up input boxes or a pop-up form to let users enter the dates they need. Then the code in the splash can open the report with a parameter query for which the dates are supplied in the code. You can see how to do this by looking up Parameter Query in Access Help.

I'll be flat honest. You are never going to make this work right with forms calling reports calling the same forms unless you have a way to terminate the recursion. The mere fact that you detected the recursion should tell you something important. You have lots of control over the Access environment - but not FULL control. Microsoft has this crazy idea that they want you to not do certain things that might compromise the integrity of their environment, and forcing recursive execution of the same entity in several layers is one of those nasties. For code that you write yourself in VBA, you can easily write recursive call facilities. But you don't write the code inside Access that actually implements the step of opening the form or report. MS does that. And THAT is where a recursive call risks various execution issues.

In a simple phrase, don't do this. No matter what your reasons might be, they aren't good enough to justify the level of headache your signing up for. Can't be clearer than that, either. Don't attempt to recursively activate forms. Don't do it. Just DON'T.

 

[DCrake]

Doc man

You are never going to make this work right with forms calling reports calling the same forms unless you have a way to terminate the recursion. The mere fact that you detected the recursion should tell you something important. You have lots of control over the Access environment

May be its late and the bottle of stella I have in front of me should nver have been opened but can you elaborate on what you mean by recrursion?

I use public variables that are passed to functions within underlying record sources that reports/queries are based upon - see earlier attachment - to detract from them being solely dependant from the active form being open.

David

 

[Rabbie]

Doc man


May be its late and the bottle of stella I have in front of me should nver have been opened but can you elaborate on what you mean by recrursion

I use public variables that are passed to functions within underlying record sources that reports/queries are based upon - see earlier attachment - to detract from them being solely dependant from the active form being open.

David?

David, enjoy your stella. I am sure you have earned it. meanwhile from the bottom of my wine glass just a few thougts in vino veritas

Brumshine should read the excellent advice he has been given in this thread and not expose the database window to his users. A splash screen (or a menu form) is the sensble way to proceed in this case.

BTW recursion is going round in circles ie A calls B calls A calls B etc

 

[DCrake]

It's a good job we are both under the same time zone and speak the same language, English I mean, otherwise I would have gone to bed alot eariler.

The recursion now reminds me of the the bird once featured in a carry on film that actually flew up its own arse. I can't remember that name o the film, nver mind the bird, but you can see what I mean. Can You? Going round in circles that much that you actually end up you own arse. Am I rambling or is there such a bird?

David

 

[gemma-the-husky]

BTW recursion is going round in circles ie A calls B calls A calls B etc

isnt that mutual recursion, which is OK if its planned, but not if its not, as it could lead to stack overflow and crashing

i would have classed recursion just as a procedure calling itself

sub foo
  if not something then
    call foo
end sub

 

[DCrake]

Gemma are you still awake? I thought it was only us drunks and insomniacs who patrolled this forum at this time of night? Anyway I am going to be night night......zzzzzzzzzzz

 

[gemma-the-husky]

seriously - why is it better to open a form (from a button on a menu), and then open a report from a button on the form, compared with opening a report (from a button on a menu), which then opens the form to request its parameters.

i dont really see any practical difference

although i do launch my reports from forms ... i inherited some apps which do it the other way

 

[Rabbie]

seriously - why is it better to open a form (from a button on a menu), and then open a report from a button on the form, compared with opening a report (from a button on a menu), which then opens the form to request its parameters.

i dont really see any practical difference

although i do launch my reports from forms ... i inherited some apps which do it the other way

The OP was originally starting his users from the database window

I assume by menu you actually mean a form with buttons on it. In that case I think your method is perfectly sensible as I would expect from you. However the OP wanted to open a report from the database window and then click a button on the report to open the parameter form and then click on another button to generate the report. I can't see my users liking that.

 

[Rabbie]

isnt that mutual recursion, which is OK if its planned, but not if its not, as it could lead to stack overflow and crashing

i would have classed recursion just as a procedure calling itself

sub foo
  if not something then
    call foo
end sub

Yes you are correct. Since the OP was opening a report that opened a form that opened the original report my mindset was focused on mutual recursion.

All recursion can cause stack overflow and crashing so needs to be thought through carefully.

 

[gemma-the-husky]

dc

playing poker and checking this site unitl the early hours, but the hours are giving me grief

you are ok with recursion arent you? i've put a sample of b) below (fibonacci numbers) - its very elegant and only only about 5 lines of code.

but i have used it to
a) walk and extract info from a xml file, - excellent practical example
(sort of thing you just cant do easily any other way)

b) demonstrate recursion wrt fibonacci numbers - see below

c) at uni, write a minimax game tree search (in pascal) to play draughts/checkers

i may have used it for some other things. eg you can tree walk any hierarchical structure with recursion, but one problem with vba is there's no pointer type, and you really need a pointer to construct a tree/queue etc

--------
heres the fibonacci example. its noticeable how slow this runs for quite modest values of x - its the process of pushing and popping things on and off the stack, (growing exponentially? - or powers of 2 anyway with each iteration.)

sub tryit
msgbox(fib(5))
end sub

function fib (x as long) as long
  if x=1 or x=2 then 
     fib=1 
  else
     fib = fib(x-1)+fib(x-2)
  end if
end function

 

[duluter]

I was wondering the same thing that Gemma was @ #31 above.

Others here state that it is better to open a form, set parameters, and then open the report from the form. I don't have much experience, but I would think that, in many cases, the opposite approach would be better. For instance, if I have five reports that all need the same parameter criteria, then I can create one generic form and open that form from the open event of all of my reports rather than making 5 separate parameter forms, each one for each report.


Duluter

 

[Rabbie]

Alternatively if all five reports use the same parameters you could open the form, then set the parameters and press a button that generates the required report or combination of reports.

IMHO a nice gui requires you to have a button to open the report that opens the parameter form. Nobody sensible would want users to access the database window. The best solution would be what suits your particular needs/requirements.

 

[duluter]

Totally agreed regarding the database window. I was envisioning that the report would be opened from a switchboard or such.

Conceptually, it makes more sense to me to open the form from the report rather than opening the report from the form because the report needs the form. In other words, the report should be responsible for itself. It opens up any forms it needs and it runs any queries it needs. The way I think about it, the form is just a tool that is used by the report, which is why I like to open the form from the report.


Duluter

 

[Rabbie]

Totally agreed regarding the database window. I was envisioning that the report would be opened from a switchboard or such.

Conceptually, it makes more sense to me to open the form from the report rather than opening the report from the form because the report needs the form. In other words, the report should be responsible for itself. It opens up any forms it needs and it runs any queries it needs. The way I think about it, the form is just a tool that is used by the report, which is why I like to open the form from the report.


Duluter

1) The switchboard is just a special case of a form.
2)I don't think it really matters whether the report opens the form or vice versa but I do think if you have 1 form providing selection data for 5 reports I would open the form and then the reports but it's your decision.

 

[duluter]

1) The switchboard is just a special case of a form.
2)I don't think it really matters whether the report opens the form or vice versa but I do think if you have 1 form providing selection data for 5 reports I would open the form and then the reports but it's your decision.

1. Right. My first paragraph was meant to be conceptually separate from my second paragraph. The form in question is the parameter form. The switchboard/database window issue is separate and I think we're all in agreement on that.

2. I brought it up because bob and dennisk both suggested that it is better to open the parameter form, then the report, but gemma and I think it might be better, at least in some cases, to do the reverse.

Regarding a code behind in the parameter form to determine which report to open, this seems much less efficient than simply opening the form from the report's open event. With the report-->form method, no switching logic is needed to determine what to do. And if you want to find out how the report works, all you have to do is open the report to see what its dependencies are. I think these are good reasons for the report-opens-the-form model. I'd like to hear from bob and dennisk on why they recommend the opposite.


Duluter

 

[boblarson]

I'd like to hear from bob and dennisk on why they recommend the opposite.

You're going to select parameters, correct? You are going to open a report, correct? If you open the report and then open the form it will still need to essentially requery the report to add the parameters. If I open the form first you can select the report you want and whatever parameters go with that report and then you open the report ONCE and you don't go through the extra overhead.

I've always just had the form which selects the report be the same which selects the parameters so you can do it all from one location and then open it up. It just makes more sense to me to do it that way.