Adding an OnOpen Event with VBA ? ? ?

[hughesai]

Hi all,

I have about 150 reports to which I need to add a simple bit of code to the OnOpen event.

Is there a way to add a piece of VBA to the OnOpen Event through VBA?

In VBA, I can loop through all the reports and open each in design view.
If I could add the code in this loop it would be so much easier than the manual exercise I am about to start!

Any suggestions will be gratefully listened to

Thanks,

Aidan Hughes.

 

[KenReay]

Hi

Yes, but I cannot quite recall the command, try to begin CreateModule in help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hughesai]

I should also have mentioned I am running Access 97 !

 

[KenReay]

Hi

That is OK, I have done this before in A97, but it was about 4 yeasr ago and I cannot recall the exact command, as I said try looking up CreateModule in help

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[jksmi]

Hi, you need a reference to Microsoft Visual Basic for Applications Extensibility to control the VBIDE, your code might look something like this

Sub TestCreateEventProc()
Dim n As Long
Dim vbc As VBComponent

For Each vbc In VBE.ActiveVBProject.VBComponents
    
    If vbc.Type = vbext_ct_Document Then
        Debug.Print vbc.Name
        With vbc.CodeModule
            ' Add a new event proc
            n = .CreateEventProc("Open", "Form")
            ' Add some code
            .InsertLines n + 1, "' inserted event goes here, i.e."
            .InsertLines n + 2, "MsgBox ""on open event triggered"", vbInformation"
        End With
    End If
Next
End Sub

only I've used form in the above example as I didn't have a report handy (sorry)...

Hope this helps, Jamie

 

[KenReay]

Hi

Sorry, my memory failed me, should have been

CreateEventProc Method


The CreateEventProc method creates an event procedure in a class module. It returns a Long value that indicates the line number of the first line of the event procedure.

Syntax

object.CreateEventProc(eventname, objectname)

The CreateEventProc method has the following arguments.

Argument Description
object A Module object whose Type property returns acClassModule, a constant with a value of 1.
eventname A string expression that evaluates to the name of an event.
objectname An object that has the event specified by the eventname argument. It may be a Form, Report, or Control object, a form or report section, or a class module.


Remarks

The CreateEventProc method creates a code stub for an event procedure for the specified object. For example, you can use this method to create a Click event procedure for a command button on a form. Microsoft Access creates the Click event procedure in the module associated with the form that contains the command button.

Once you've created the event procedure code stub by using the CreateEventProc method, you can add lines of code to the procedure by using other methods of the Module object. For example, you can use the InsertLines method to insert a line of code.


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[hughesai]

Thanks all.
I have it working now.
I found the CreateEventProc myself, and had it partially working, but I still needed the last bit to get it fully working.

Aidan.