Report_Close() ... problem

[snayjay]

(Short Story)
I have put code in the Report_Close() Event. It is supposed to open Modal forms I had to close in order for the Report to gain focus once opened (which still doesn't work). Anyway, I can tell this code is being processed multiple times while the report is being opened as if the report is opening and closing multiple times before it stays opened... when it should only be opening once. Any clue why my Close_Event would be processing while the form is Opening?

(More if interested or if you need it)
On my report I have 3 graphs of the same height/width sitting on top of each other. I have code under the button_click event that opens this report, that checks to see a certain option the user has made and then makes 2 of the graphs invisible and 1 visible depending on the option. I think this is causing my problem (from above)... it would appear that since I have 3 graphs and I can see the process happening 3 times that the Close_Event is processing in conjunction with something to do with the graphs. Is there any connection there? The graphs rowsource is coming from a query which comes from fields from another form (not the form that opens the report, because that form is immediately closed in order to bring the report to view... which as I said before I still can't get to work, I have to alt-tab to the report right now)

Sorry for the run-on sentences I write like I talk.

 

[dhookom]

The general method used is to make the modal form(s) invisible rather than closing them.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[snayjay]

Yeah I tried that... and when it comes to making them visible again on the Report_Close() event it would only make one of the forms visible.

So in order to fix that I transferred the reports query info from one form (the one that opens the report) to another non-modal form (program background) then closed the Modal forms and opened the report. Report opened pulling data from the Non-Modal form (background).

I'd like to open the forms again when the report closes but adding code to the Report_Close event caused my original problem.

Thanks for trying to solve that problem, but any clue on my original one?

 

[dhookom]

You haven't provided any code. We also don't know the full filtering and sequence of opening forms and reports. Do you have any subreports in your report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[snayjay]

Yeah my general question here was why my Report_Close event would be processing because of the graphs or is there any general correlation to using multiple graphs (as I have) to making the Report_Close event process. But in case it does help. Here's the other info:

Forms
Main Form - (Background) BGfrm - Non Modal
Next Form - (Option Form) REPORTSfrm - Modal
Next Form - (Condition Form) REPORT1frm - Modal

Reports
DailyNumbers
this is opened from REPORT1frm RUNcmd_Click()

Select Case OPTfrm.Value [COLOR=green]'option on form indicating either a days report, 
                          months report, or range report[/color]
        Case 1
            Forms!BGfrm!OPTfrm.Value = 1
            Forms!BGfrm!DATECAL.Value = DATECAL.Value
            DoCmd.OpenReport "DailyNumbers", acViewPreview, , "[DATEX] = #"_
            & DATECAL.Value & "#", acWindowNormal
            Reports!DailyNumbers!DAYGRAPH.Visible = True
            Reports!DailyNumbers!MONTHGRAPH.Visible = False
            Reports!DailyNumbers!RangeGraph.Visible = False
            DoCmd.Close acForm, "REPORT1frm"
            DoCmd.Close acForm, "REPORTSfrm"
        Case 2
            Forms!BGfrm!OPTfrm.Value = 2
            Forms!BGfrm!MONTHOPT.Value = MONTHOPT.Value
            DoCmd.OpenReport "DailyNumbers", acViewPreview, , "(Month_
            ([DATEX])) = " & MONTHOPT.Value, acWindowNormal
            Reports!DailyNumbers!DAYGRAPH.Visible = False
            Reports!DailyNumbers!MONTHGRAPH.Visible = True
            Reports!DailyNumbers!RangeGraph.Visible = False
            DoCmd.Close acForm, "REPORT1frm"
            DoCmd.Close acForm, "REPORTSfrm"
        Case 3
            Forms!BGfrm!OPTfrm.Value = 3
            Forms!BGfrm!DATECAL.Value = DATECAL.Value
            Forms!BGfrm!DATECAL2.Value = DATECAL2.Value
            DoCmd.OpenReport "DailyNumbers", acViewPreview, , "[DATEX]_
            BETWEEN #" & DATECAL.Value & "# AND #" & DATECAL2.Value & "#",_
            acWindowNormal
            Reports!DailyNumbers!DAYGRAPH.Visible = False
            Reports!DailyNumbers!MONTHGRAPH.Visible = False
            Reports!DailyNumbers!RangeGraph.Visible = True
            DoCmd.Close acForm, "REPORT1frm"
            DoCmd.Close acForm, "REPORTSfrm"
    End Select

This works fine, it opens the report and the report has all the info I need on it based on the option chosen. In the ReportHeader of this report I have pasted in a graph that I generated from another report. Pasting it in brings over the rowsource and formatting and I don't have any problems with that either. I'm putting it in the ReportHeader because I only want it to appear once at the beginning of the report. I can't assign the rowsource on ReportHeader_Format. So I've made 3 graphs showing the 3 possibilities of report options (day,month,range) and I make the appropriate one visible per it's option. This works fine as well... no errors.

The report gets it's data from a code above, criteria based on fields form the REPORT1frm. The graphs however get their queries criteria from the BGfrm. I had to do this because if I leave the Modal forms open when the report opens, the report opens behind the Modal forms. If I make the Modal forms invisible, then when I go to make them visible again it only makes one visible.

Now if I was to use the invisible/visible method. I would have the code as so:

from the code above I would change:

            DoCmd.Close acForm, "REPORT1frm"
            DoCmd.Close acForm, "REPORTSfrm"

To:

            Forms!REPORT1frm.Visible = "False"
            Forms!REPORTSfrm.Visible = "False"

And on the Report 'DailyNumbers' I would have

  Report_Close()
      Forms!REPORT1frm.Visible = "True"
      Forms!REPORTSfrm.Visible = "True"
  End Sub

When I tried that, it would make the REPORTSfrm visible but not the REPORT1frm. And it didn't matter which order I did it... had I reversed the order, it was the same result. The good thing about it though was it brought the report to focus... because I'm now closing those Modal forms, the report doesn't come to focus and I have to Alt-Tab or use the Task bar to get to the report.

I know my problem has something to do with the graphs, even though the graph gets it's data from the BGfrm... when I take out the graphs and 'comment out the graphs code... everything works fine. I had thought about using subreports... that might be what I need to do.

Any thoughts?

 

[dhookom]

I'm not sure if this would make any difference but I never use code in a form to change properties of controls in reports. I would place the code to hide graphs in the on format event of the report section containing the graphs. The DoCmd.Close... would only need to be included once after the Select Case.

If the report criteria only differ by the date range then can't you combine all three criteria into just Between DateCal and DateCal2? Set both of these values in the After Update of the OPTfrm.

Are the graphs identical other than date range? If so, you should only need a single graph control.

When attempting to make the dialog forms visible, there will be an order of one in front of the other. I don't believe you can change this other than the order they are initially opened.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[snayjay]

I'm not sure if this would make any difference but I never use code in a form to change properties of controls in reports.

I kinda have to... as I stated before I got an error when I tried to assign .rowsource to the graph in the ReportHeader_Format() Event, it wouldn't allow assigning that value while the Report was printing.

I would place the code to hide graphs in the on format event of the report section containing the graphs.

Ok, I can do that. But it won't change anything else...the report close event still gets processed while opening, and it doesn't fix the invisible form issue either.

The DoCmd.Close... would only need to be included once after the Select Case.

Gotcha, adjusted the code... saved 4 lines but still have the original problem.

If the report criteria only differ by the date range then can't you combine all three criteria into just Between DateCal and DateCal2? Set both of these values in the After Update of the OPTfrm.

Actually yes, I think this will fix another problem I hadn't listed having to do with determining the year of a Month query. So yeah, changing that will reduce the graphs to 2. One for Day/Range and one for Month. Unless you can tell me how to identify the first day of the month and the last day of the month. Then if the user sets his option to month. I can have him choose the month from the dropdown box and I can auto set the DATECAL to the first day and the DATECAL2 to the last day of that month.
(And actually if you can fix this you fix the answer to the first quote from above)

When attempting to make the dialog forms visible, there will be an order of one in front of the other. I don't believe you can change this other than the order they are initially opened.

As I stated before, I tried alternating the order... same result the REPORT1frm wouldn't become visible regardless of order.

Thanks for the inputs that helped any clue to the others?

v/r

~Snay

 

[dhookom]

I kinda have to... as I stated before I got an error when I tried to assign .rowsource to the graph in the ReportHeader_Format() Event, it wouldn't allow assigning that value while the Report was printing.

Your code to hide the graphs is what I was referring to.

You should have a single sub or function that updates the two dates. The beginning of any month for a given date is:
DateSerial(Year([Given Date]), Month([Given Date]),1)
The end of the month is:
DateSerial(Year([Given Date]), Month([Given Date])+1,0)

Do you understand the order you attempt to make the dialog forms visible doesn't make any difference? It should depend on the order the forms were originally opened.

Isn't your code

 Report_Close()

actually

Private Sub Report_Close()

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]