adding code to every form in the database

[ryetee]

i want to add the same four lines of code to the load event for every single form (70+).

I know I can open each form in design mode, go to the event and copy and paste the code, but is there a smarter way?

 

[CJ_London]

in a module create a Public function (not a sub) called say FormLoadEvent and place your four lines of code there

then in the same module write a temporary sub to populate each form load event with

=FormLoadEvent

you can do this by looping through the forms collection - something like

dim frm as form
for each frm in forms
frm.onload="=FormLoadEvent()"
next

then run it

Not sure whether you need to open the form in design view, make the change then save it - you'll need to experiment

 

[ryetee]

in a module create a Public function (not a sub) called say FormLoadEvent and place your four lines of code there

then in the same module write a temporary sub to populate each form load event with

=FormLoadEvent

you can do this by looping through the forms collection - something like

dim frm as form
for each frm in forms
frm.onload="=FormLoadEvent()"
next

then run it

Not sure whether you need to open the form in design view, make the change then save it - you'll need to experiment

Thanks CJ, I'll have a play around later when I get chance. In the meantime does this create the on load function for each form? At the moment quite a few forms have some code already in there. I want to add my lines at the 'top'.

 

[gemma-the-husky]

I cant share it, but I found/enhanced some code that uses the vbide library to do just that. google "vbide code to edit code", or similar.

basically I do a lot of stuff like this.

examine every code module.
if there is no line of code that matches what I want to add, then I can add those lines at a given insertion point.

very helpful to keep code consistent.


The only catch is that editing the code in this way destroys the programme context. All variables are lost, so you are obviously doing something slightly different to what the interactive editor does. As long as you know this will happen, there is no issue.

Sorry. Another catch. I seem to get out of memory problems if I amend too many modules in one go, so I limit it to processing 50 at a time, save the changes, then do another 50.

 

[Gasman]

You might be able to adapt some of the code from here?

http://www.cpearson.com/excel/vbe.aspx

 

[ryetee]

I cant share it, but I found/enhanced some code that uses the vbide library to do just that. google "vbide code to edit code", or similar.

basically I do a lot of stuff like this.

examine every code module.
if there is no line of code that matches what I want to add, then I can add those lines at a given insertion point.

very helpful to keep code consistent.


The only catch is that editing the code in this way destroys the programme context. All variables are lost, so you are obviously doing something slightly different to what the interactive editor does. As long as you know this will happen, there is no issue.

Sorry. Another catch. I seem to get out of memory problems if I amend too many modules in one go, so I limit it to processing 50 at a time, save the changes, then do another 50.

Cheers Gemma. Back home now so I'll have a look at what you say together with CJ and Gasman's suggestions.

Just one thing, OK 2 things, what do you mean by
"The only catch is that editing the code in this way destroys the programme context "
and
"All variables are lost," (or is that part of The only catch is.....)

 

[ryetee]

You might be able to adapt some of the code from here?

http://www.cpearson.com/excel/vbe.aspx

OK I'll take a look!

 

[ryetee]

Thanks CJ, I'll have a play around later when I get chance. In the meantime does this create the on load function for each form? At the moment quite a few forms have some code already in there. I want to add my lines at the 'top'.

OK had a play around with this and it seems I need to open each form before running the temporary sub. This would work (I think) if the on load event only had to include the 4 lines of code because it updates the On Load property in the form to =FormLoadEvent. A lot of my forms already have an On Load event so this won't work for me.

I've learnt something new today so thanks. How long I'll retain that is another thing.

 

[Gasman]

In that link I posted is some code to find text in a module.

You could try an adapt that?

 

[ryetee]

To Gemma and Gasman

On googling all I can find that looks anywhere near to what I want to do is the link that Gasman posted.

This appears to list code to insert Module/Procedure into a Project but it's for Excel workbooks and I'm not clever enough to work out what needs changing for Access 2010 and inserting lines into an already existing event.

Looks ,like I'm going to have to do this longhand!

 

[ryetee]

In that link I posted is some code to find text in a module.

You could try an adapt that?

Looking at it now, but I'm thinking by the time I suss this out I could have copy and pasted all the forms and drunk Banks' brewery dry (or is that Cardiff?).

 

[Gasman]

Brains is Cardiff

This would be more in line for what you need. You will need to strip out the unneeded code but worth keeping I think?

Found at http://www.demtron.com/blog/post/2008/10/12/Export-Modules-to-Text-Files-in-Microsoft-Access.aspx

Option Compare Database
Option Explicit
Public Sub CreateModuleFiles()
'On Error GoTo Err_Handler
Dim accObj As AccessObject  'Each module/form/report.
Dim bWasOpen As Boolean     'Flag to leave form/report open if it was open.
Dim strDoc As String        'Name of each form/report
'Stand-alone modules.
For Each accObj In CurrentProject.AllModules
Call WriteOutModules(accObj.Name, True)
Next
'Modules behind forms.
For Each accObj In CurrentProject.AllForms
strDoc = accObj.Name
bWasOpen = accObj.IsLoaded
If Not bWasOpen Then
DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
End If
If Forms(strDoc).HasModule Then
Call WriteOutModules("Form_" & accObj.Name, False)
End If
If Not bWasOpen Then
DoCmd.Close acForm, strDoc, acSaveNo
End If
Next
'Modules behind reports.
For Each accObj In CurrentProject.AllReports
strDoc = accObj.Name
bWasOpen = accObj.IsLoaded
If Not bWasOpen Then
'In Access 2000, remove the ", WindowMode:=acHidden" from the next line.
DoCmd.OpenReport strDoc, acDesign, WindowMode:=acHidden
End If
If Reports(strDoc).HasModule Then
Call WriteOutModules("Report_" & accObj.Name, False)
End If
If Not bWasOpen Then
DoCmd.Close acReport, strDoc, acSaveNo
End If
Next
End Sub
Private Function WriteOutModules(strModule As String, bIsStandAlone As Boolean)
Dim strCode As String
Dim bWasOpen As Boolean     'Flag applies to standalone modules only.
Dim lngLineNo As Long
If bIsStandAlone Then
bWasOpen = CurrentProject.AllModules(strModule).IsLoaded
End If
If Not bWasOpen Then
DoCmd.OpenModule strModule
End If
For lngLineNo = 1 To Modules(strModule).CountOfLines
strCode = strCode & Modules(strModule).Lines(lngLineNo, 1) & vbCrLf
Next
strCode = strCode & vbCrLf & vbCrLf & vbCrLf
If Not bWasOpen Then
On Error Resume Next
DoCmd.Close acModule, strModule, acSaveNo
End If
Dim intFile As Integer
'*** Set to next free open number ***
intFile = FreeFile()
Open "C:\output\modules\" & strModule & ".txt"   For Output As #intFile
Print #intFile, strCode
Close #intFile
Debug.Print strModule & " complete"
DoEvents
End Function

Sample Output of a form

Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim strSub
strSub = "Test"

End Sub

Private Sub Text7_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub txtValue_LostFocus()
If Me.txtValue > 200 Then
MsgBox "> 200"
Else
MsgBox "<= 200"
End If
End Sub
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click



DoCmd.OpenQuery "TestTransactions Query"

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

 

[ryetee]

Brains is Cardiff

This would be more in line for what you need. You will need to strip out the unneeded code but worth keeping I think?

Found at http://www.demtron.com/blog/post/2008/10/12/Export-Modules-to-Text-Files-in-Microsoft-Access.aspx

Option Compare Database
Option Explicit
Public Sub CreateModuleFiles()
'On Error GoTo Err_Handler
Dim accObj As AccessObject  'Each module/form/report.
Dim bWasOpen As Boolean     'Flag to leave form/report open if it was open.
Dim strDoc As String        'Name of each form/report
'Stand-alone modules.
For Each accObj In CurrentProject.AllModules
Call WriteOutModules(accObj.Name, True)
Next
'Modules behind forms.
For Each accObj In CurrentProject.AllForms
strDoc = accObj.Name
bWasOpen = accObj.IsLoaded
If Not bWasOpen Then
DoCmd.OpenForm strDoc, acDesign, WindowMode:=acHidden
End If
If Forms(strDoc).HasModule Then
Call WriteOutModules("Form_" & accObj.Name, False)
End If
If Not bWasOpen Then
DoCmd.Close acForm, strDoc, acSaveNo
End If
Next
'Modules behind reports.
For Each accObj In CurrentProject.AllReports
strDoc = accObj.Name
bWasOpen = accObj.IsLoaded
If Not bWasOpen Then
'In Access 2000, remove the ", WindowMode:=acHidden" from the next line.
DoCmd.OpenReport strDoc, acDesign, WindowMode:=acHidden
End If
If Reports(strDoc).HasModule Then
Call WriteOutModules("Report_" & accObj.Name, False)
End If
If Not bWasOpen Then
DoCmd.Close acReport, strDoc, acSaveNo
End If
Next
End Sub
Private Function WriteOutModules(strModule As String, bIsStandAlone As Boolean)
Dim strCode As String
Dim bWasOpen As Boolean     'Flag applies to standalone modules only.
Dim lngLineNo As Long
If bIsStandAlone Then
bWasOpen = CurrentProject.AllModules(strModule).IsLoaded
End If
If Not bWasOpen Then
DoCmd.OpenModule strModule
End If
For lngLineNo = 1 To Modules(strModule).CountOfLines
strCode = strCode & Modules(strModule).Lines(lngLineNo, 1) & vbCrLf
Next
strCode = strCode & vbCrLf & vbCrLf & vbCrLf
If Not bWasOpen Then
On Error Resume Next
DoCmd.Close acModule, strModule, acSaveNo
End If
Dim intFile As Integer
'*** Set to next free open number ***
intFile = FreeFile()
Open "C:\output\modules\" & strModule & ".txt"   For Output As #intFile
Print #intFile, strCode
Close #intFile
Debug.Print strModule & " complete"
DoEvents
End Function

Sample Output of a form

Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim strSub
strSub = "Test"

End Sub

Private Sub Text7_KeyPress(KeyAscii As Integer)
KeyAscii = 0
End Sub

Private Sub txtValue_LostFocus()
If Me.txtValue > 200 Then
MsgBox "> 200"
Else
MsgBox "<= 200"
End If
End Sub
Private Sub Command2_Click()
On Error GoTo Err_Command2_Click



DoCmd.OpenQuery "TestTransactions Query"

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

I'll take a look. As Cardiff is a bit too far I decided to have a look at find text in a module and add procedure to module from your 1st link to see if I can use it to add my four lines of code to the on load event for each form.

Incidentally the code from your second link looks like it's exporting allmcode to a text file. Am I missing the point?

 

[Gasman]

No, but it is opening the modules.

I've since discovered that this

    DoCmd.OpenModule strModule, "Form_Load"

will open just that sub, so it is easy enough to insert your code into a variable, split it where you want and concatenate it with the extra code.

The bit I cannot find as yet, is how to update the form back with the amended code

Modules(strModule) = strCode

produces and error stating it is read only.

So almost there. We have the ability to get all the forms, open to the sub required, add the code. now all is left is to write it back and then

DoCmd.Close acModule, strModule, acSaveYes

Also if the form does not have that sub it errors, but you could code for that

Though as you say though, you could have added the code by now manually.

Edit: Just discovered that extra parameter only affects the start point of the module, so more work involved in splitting the code

 

[ryetee]

No, but it is opening the modules.

I've since discovered that this

    DoCmd.OpenModule strModule, "Form_Load"

will open just that sub, so it is easy enough to insert your code into a variable, split it where you want and concatenate it with the extra code.

The bit I cannot find as yet, is how to update the form back with the amended code

Modules(strModule) = strCode

produces and error stating it is read only.

So almost there. We have the ability to get all the forms, open to the sub required, add the code. now all is left is to write it back and then

DoCmd.Close acModule, strModule, acSaveYes

Also if the form does not have that sub it errors, but you could code for that

Though as you say though, you could have added the code by now manually.

Edit: Just discovered that extra parameter only affects the start point of the module, so more work involved in splitting the code

I've given up for tonight. The code I was looking at was for Excel and I've no idea what to substitute for things like Set VBProj = ActiveWorkbook.VBProject. If I did I could probably by trial and error get it working. Be quicker to cut and paste I know but as Magnus Magnusson said on several occasions "I've started so I'll finish"!

Think I'll take a closer look at your second link now (yeah I know I said I'd given up for the night, but.....).

 

[Gasman]

And this might be the final piece in the puzzle?

https://msdn.microsoft.com/en-us/library/office/ff194137.aspx

Though I hope you kept the code standard otherwise a bit more logic is required.

I've tested it and it works great.

 

[Gasman]

Take a break for now, start again tomorrow and it should be a lot easier.

The second link for the code is all access based and is what i would use.

 

[ryetee]

Firstly I'll have to ignore the module loop.
In the form loop if I can change "If Forms(strDoc).HasModule" to "Forms(strDoc).OnLoad = "[Event Procedure]"" I think this will give me all the forms with the On Load event. Then rather than writing the code to a file I check to see if line equals "Private Sub Form_Load()" and if it is read next line and some how insert 4 lines of code. This is where I think I'll need VBIDE so back to square 1.

 

[ryetee]

Take a break for now, start again tomorrow and it should be a lot easier.

The second link for the code is all access based and is what i would use.

yeah good idea. I'll look at your 3rd link tomorrow!!!

 

[Gasman]

As always, there are various ways.

From what I have found out tonight I'd be....

Looking at processing only the forms and open their module.
Create the text with vbCrLf as required.
Look for where you want to insert the code, checking each line as it is read in. When found save the line and exit the search loop.
Then .insertlines savedlinenumber requiredtext
Close the module with acSaveYes

As mentioned I just inserted some text to a random line and it worked great.

The hardest part seems in finding the correct location in each form. That is why standardised code would be great.

Good luck with it.

Firstly I'll have to ignore the module loop.
In the form loop if I can change "If Forms(strDoc).HasModule" to "Forms(strDoc).OnLoad = "[Event Procedure]"" I think this will give me all the forms with the On Load event. Then rather than writing the code to a file I check to see if line equals "Private Sub Form_Load()" and if it is read next line and some how insert 4 lines of code. This is where I think I'll need VBIDE so back to square 1.