Is it possible to change an event procedure from VBA?

[gnawoncents]

I have a dynamic report (created from VBA coding) and I want to set the OnClose event procedure to make another form visible when the report closes. I cannot access the events in the report itself since it doesn't exist until the user presses a button.

I have tried several variations with negative results:

Set Reports(rpt.Name).OnClose = "Forms!FRMDynamicReport.Visible = True"

(Where rpt.Name is the name of the newly created report)

AND

Reports(rpt.Name).OnClose = "Forms!FRMDynamicReport.Visible = True"

OR

Reports(rpt.Name).OnClose = Forms!FRMDynamicReport.Visible = True

ETC.

My primary question is this: Is what I'm trying to do possible? If so, how? If not, is there another way to go about accomplishing the same thing?

Thanks for your help!

[Oralloy]

Have you tried creating a support module which holds a state variable that the report can access?

Your report will copy the state value as part of the "Report_Ope n()" method.

Then the "Report_Close() " method acts, based on the saved state value.

Does that make sense?

[slenish]

I found another post about dynamic reports here in bytes that might help you.

http://bytes.com/topic/access/insights/696050-create-dynamic-report-using-vba

[MMcCarthy]

Your problem is that since you can't access the events of the report how to you trigger the on close event. It's an interesting problem :)

Let me see what I can find out and I'll get back to you.

[gnawoncents]

Have you tried creating a support module which holds a state variable that the report can access?

Your report will copy the state value as part of the "Report_Ope n()" method.

Then the "Report_Close() " method acts, based on the saved state value.

Does that make sense?

Oralloy,

I'm not entirely sure what you mean by "state variable." If it is something the report should access, then unless I am mistaken I am still stuck with the problem of how to tell the as of yet nonexistent report that when it is created to look at the state variable. I might just be missing your point though... please clarify. Thank you!

----

Slenish,

Thanks for the response, that's actually the main resource I used to create the dynamic report coding.

----

MMcCarthy,

Yes, that's exactly my problem. I toyed with the idea of a timed event, but am loathe to go that route. Please let me know if you come up with anything. Thanks!

[MMcCarthy]

OK in the code where the report is created dynamically you will have a report object. For this purpose lets assume that object is called rpt.

As part of your code you need to add the on close event as part of that report creation as follows.

Dim mdl As Module
Dim lngReturn As Long
Dim strCode As String

    strCode = vbCrLf & "   Forms!FRMDynamicReport.Visible = True"

    Set rpt = Reports("rpt.Name")
    Set mdl = rpt.Module
    ' Add event procedure.
    lngReturn = mdl.CreateEventProc("Close", "Report")
    ' Insert text into body of procedure.
    mdl.InsertLines lngReturn + 1, strCode

[Oralloy]

gnawoncents,

It's interesting that slenish's post points to one of Mary's posts. They're both really good at this sort of thing.

I was thinking that you had an existing report that you were modifying on the fly and adding code to. Thus, I must bow out of this, I think.

[gnawoncents]

Thank you all for your help.

MMcCarthy,

I stripped the quotes from "rpt.name", and once I realized I should add the code before the open report line, it worked like a charm. Thank you again!

[MMcCarthy]

LOL, sorry about the quotes. I had a report name in when testing it and I forgot when I copied the rpt.name in.