Skipping "password" On Open event on report when it is used a subreport

[sean86rc]

Hello,



I have a two reports I want a password to open. One is also used as a subreport within the other report which is what is causing me the problem. This "subreport" has a password for the On Open event to prevent unauthorised viewing of the report. I would also like to add a password to the On Open event of the "main report" which contains other information, but if I add to the On Open event of the main report, opening this report will trigger two parameter boxes asking for the password.

Currently if the user fails to enter the password on the main report it will fail to open that subreport section, but the rest of the information is still visible which I do not want.

Is there a way I can skip the On Open event of the subreport when it is used as a subreport in the main report? Or add a cancel = true to the opening of the main report if the subreport password has been entered incorrectly and that subform has not opened?

I have tried various methods but to no avail since the On Open events seem to trigger before any data is loaded.

Any help is appreciated.

[Minty]

How are you checking for the password, on a form that then opens the reports I assume?
I would have thought you could use the form to verify the access to either report then simply open them accordingly.

In other words move the password checking from the report to what opens them, or pass the password in as an open args for the reports and verify it from there.

[CJ_London]

perhaps don't load the subreport until the main report is open (sub reports/forms open before the main form). i.e. leave the source object blank

Once main form is open and the user has entered the correct password, using the section the subreport is on onformat or print event to populate the subreport control dependant on the user or however you are managing user access.

The subreport open event can then check if it has a parent and if so, not ask for the password.

to check if a form has a parent use something like

On Error Resume Next 'required to handle the error generated if there is no parent on the next line
If Parent Is Nothing Then
    Debug.Print "no parent"
    'ask for password
Else
    Debug.Print Parent.Name
    'don't ask for password
End If
On Error GoTo 0

[Micron]

(sub reports/forms open before the main form)

If that means a sub report opens before the main report, I think you will find that it is true of forms but not reports.

Putting a password check on the form that opens the report won't stop anyone from opening the report from the nav pane if is visible there. We know that users should be prevented from being able to do that but I don't know if the OP does.

If I was to take the password approach I don't see why putting the password on the main report only wouldn't work in normal circumstances. Because the main report opens first, then you should be able to pass a value to OpenArgs of the subreport. If the sub is opened independently, it won't have open args property and should error. Trap the error and cancel the opening, same as for the main report.

EDIT - not sure if OpenArgs can be used without DoCmd.Openreport, which would be of no use. If not, a similar approach might work; not sure what that might be at present. Perhaps a custom report property.

[Micron]

Since the goal is to prevent the report being used as a subreport from being opened by other means, and to avoid 2 password prompts, this is what I came up with a very simplified version as a test.

If Not CurrentProject.AllReports("rptSubTest").IsLoaded Then
   MsgBox "report cannot be directly opened"
   Cancel = True
End If

As written, you cannot open this report independently. Instead of a message box you'd probably have your password code to allow you to open it independently. If the main report opens the subreport, this code "sees" that the main report is open so the subreport just opens without prompting.

[sean86rc]

Thank you for your responses and solutions! I love how many creative ways there are to achieve the same solution.

I have opted for the disabling opening the reports from the navigation pane (Yes, I did NOT know I could do this!) and adding the password to the button instead. Works perfectly. In fact I can now control all my subforms and reports better too!

[isladogs]

There is another method that can be used to prevent any form or report being opened from the navigation pane.
Add this code to the On Open event of the form/report

    'block opening item from nav pane
    If Application.CurrentObjectName = Me.Name Then Cancel = True