Capture all Form Loads in one global function

[rkruis]

We currently have over 800 forms in this company wide Access DB. We know many are not used.

What I would like to do, is capture the form load for every form that is opened and log, the form name and who opened it.
I don't know how in VBA I can subclass Form Load. Is there a way to do this?
My one though is to add a timer on the main form, that every x minutes, loops through all open forms and saves that to DB.
I am hoping to write one function that will do it, without having to past a function call in all 800 form loads.
Can someone show me exact code I would use to implement a global function such as this?

[OBP]

I am not even sure that this can be done.
First of all is the Database "Split"?
Do you have a log in for users to identify them?

It might actually be easier to write the code to enter the VBA in to each form's On Load Event Procedure to Call a public procedure or function.

[rkruis]

Yes, this is what I am hoping to avoid. Adding a function call, to a global function for every form load. I know in C, I can capture all WM_CREATE without being inside the for Proc.

[OBP]

Can you answer the questions?

[rkruis]

The users do not log in, I take the users IDs from Active Directory, to know who made the change.
No, the database is not Split.

However, I am not sure what this has to do with capturing the Form Load event in a global function.

[OBP]

Because if the database was split the Forms would be opened on individual PCs and not in the Main Database's Forms Collection.

You said "I take the users IDs from Active Directory, to know who made the change.", does that mean you already know who is currently in a Form making the changes?

Because at the moment I don't know of any way to identify the user of the form unless they were already identified when the form was opened, not after the event.

It is simple enought to identify all the open forms with the Forms Collection.

Which is why I suggested writing the Code to put the VBA Function Call in the On Load Event Precedure of each Form, which would be a one off operation.

[rkruis]

OK, the issue is not with who opened the form, or what is in the form.

I am trying to capture when a form is opening/loading.

If I can capture that window message, I can log the form name to a database. This way I can see what forms have not been opened over a period of time, and make a decision based on that data, if I can delete the form.

[OBP]

I don't know about the Window message, but capturing Forms that are and aren't open is relatively easy, you could do so every 5 seconds to any other period of time and ignore any that were already open and just add the ones that weren't and now are at that point to build up a usage map of which forms are opened and for how long.
Would that do?

[rkruis]

I think that would do.
Would I just add a timer to the main form and loop through the Forms.Item?

[OBP]

It appears that the AllForms collection only displays or counts the Mainform and not subfroms using the isloaded .
This is an example of adding the names to a String with the timer set.
Private Sub Form_Timer()
Dim obj As AccessObject, dbs As Object, Formlist As String
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
Formlist = Formlist & ", " & obj.Name
'Debug.Print obj.Name
End If
Next obj
MsgBox Formlist

End Sub

[jonh]

I wouldn't recommend a timer.

First thing I would do is loop through each form and generate a report of all the ones that have onload/onopen events.
For any that don't have events, you could set the property to a public function using a similar loop (i.e. rather than [Event Procedure] set it to =MyLog()).
Any forms with events already would need to be modified manually.

[PhilS]

I know in C, I can capture all WM_CREATE without being inside the for Proc.

You can do the same in Access. - It involves a bit of Win-API programming, though.
Subclass the MDI-Client-Window and watch out for WM_PARENTNOTIFY messages for a WM_CREATE event. The window handle from the message's lParam is the handle of the opened form.

Warning: Access/VBA will become unstable if you ever open the VBA-Editor during the application process lifetime in which subclassing is or will be activated.

[rkruis]

You can do the same in Access. - It involves a bit of Win-API programming, though.
Subclass the MDI-Client-Window and watch out for WM_PARENTNOTIFY messages for a WM_CREATE event. The window handle from the message's lParam is the handle of the opened form.

Warning: Access/VBA will become unstable if you ever open the VBA-Editor during the application process lifetime in which subclassing is or will be activated.

This is more what I was looking for. I was trying to figure out how to subclass using WM_CREATE. I see how I can capture it, if I know the exact form handle, but I can not figure how to capture all forms, with a single global function.

Can you give me an example?

[PhilS]

Sorry, I'm too busy at the moment to write a full working sample.
It shouldn't be to difficult, if you are familiar with subclassing and window procs. (If you hadn't made that impression, I wouldn't have suggested this!)

- You start with the window handle of the Access.Application (Application.hWndAccessApp),
- then you use EnumChildWindows to get the MDIClient-Window.
- You subclass this window with you own window proc.
- In the window proc you inspect all WM_PARENTNOTIFY messages. If it is a notification about a new child window (wParam=WM_CREATE).
- If yes, find the form name. The best way is probably by comparing the window handles (hwnd) from the Forms-Collection with the handle you got from the message (lParam).

The tricky part is that the subclassing will crash Access if you open the VBA-Editor during a session where you activate the subclassing. This makes debugging a PIA. It is advisable to use conditional compilation to deactivate the subclassing in the development environment.

[rkruis]

Thanks Phil - I am very familiar with it, however, it has been several years since I developed using windows messaging. This will get me moving forward.

[jonh]

It probably won't tell you anything about forms that are used as subs though.

[PhilS]

It probably won't tell you anything about forms that are used as subs though.

Correct.
However, with a reference to the main form you can loop through its Controls-Collection, find any SubformControl, and log its source form name. - Apply recursively for nested sub forms.

[rkruis]

Thanks everyone - I got all the information I need.

Stupid question, how do I mark this as solved? I can't find the link anywhere. lol. [Found it - Never mind]

[SamT]

Please share the solution here.

This question has had 1,986 views in the last 10 days.

A lot of people are interested