Removing events from a form with VBA

[kev newton]

Hi , I am building a new database but wish to reuse some forms from an old one. Unfortunatly each control on the old forms has at least 2 events such as before update and after update and there are many controls on the form. I do not need to use these in my replacement form.

is there any way of removing all the unwanted events on the form using VBA ? or am i stuck doing this manually ?

thanks...

 

[HiTechCoach]

I like to use MZ Tools. It will allow you to copy and paste controls and the VBA code!

You will find a link to it Click Here

Hope this helps ...

 

[Mr. B]

Removing code manually is really not a big deal. If you have a form from which you want to remove all existing code, open the VBA code window for that form and select all of the code, except the top two lines which should read:

Option Compare Database
Option Explicit

When all of the code is selected, just press the delete key.

That's it.

 

[boblarson]

Removing code manually is really not a big deal. If you have a form from which you want to remove all existing code, open the VBA code window for that form and select all of the code, except the top two lines which should read:

Option Compare Database
Option Explicit

When all of the code is selected, just press the delete key.

That's it.

And even easier - just set the HAS MODULE property of the form (in Design View properties) to NO and then answer YES when it asks if you want to delete it all.

 

[Mr. B]

Thanks, "super easy Bob". LOL.

I did't think of that one.

 

[dcb]

And if you really want to do it in code!!!!

Here's a module

Public Sub sKillModule(frm As String)
    DoCmd.OpenForm frm, acDesign
    Forms(frm).HasModule = False
    DoCmd.Save acForm, frm
    DoCmd.Close acForm, frm
    Debug.Print "   Module in " & frm & " deleted!"
End Sub
Public Sub sKill_Cycle()
    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    Dim frm As Object, mdl As Module
    Dim frmOpen As Form
    Dim strFrm As String
    ' Search for AccessObject objects in AllForms collection.
    For Each obj In dbs.AllForms
                strFrm = obj.Name
            If MsgBox("Do You want to delete the module for " & strFrm, vbYesNo, "Confirm") = vbYes Then
                sKillModule (strFrm)
            Else
                Debug.Print "Did not delete " & strFrm & " Module"
            End If
    Next obj
End Sub

Didnt make it pretty but it looked like a fun idea
You run sKill_Cycle -> it will go through all forms and ask you if you want to delete the module behind each form


Some points before you do it:

1. Backup
2. If run from a form it does not exempt Me.Name!!!
3. Backup
4. Credit to Bob for the Has Module idea - all I have done is added a loop through AllForms and YesNo MsgBox
5. Backup

Cheers

 

[kev newton]

Thanks for the advice gents.
I have removed the module for one of the forms which is fine but i am still left with about 70 controls on the form each one still has a reference to the removed modules code in both the before and after update events.

is there any way i can loop through all of the controls on the form and delete the events ?

 

[kev newton]

Got it now thanks
did a bit of digging on line and came up with the code below. This loops through all of the controls on all 4 of my reused forms, clears any reference to an event in the before or after update and then saves. This has saved me hours of tedious work.

Public Sub Clear_Old_Form_Data()
Dim Fname(1 To 4) As String
Dim count As Integer
'Form names to be cleared
Fname(1) = "20010_DieData_1"
Fname(2) = "20020_DieData_2"
Fname(3) = "20030_DieData_3"
Fname(4) = "20040_DieData_4"
For count = 1 To 4 Step 1
Delete_Form_Events (Fname(count))
Next count
End Sub


Public Sub Delete_Form_Events(formname As String)
Dim ctl As Control
Dim mycont As String
DoCmd.OpenForm formname, acDesign
For Each ctl In Forms(formname).Controls

mycont = ctl.ControlName

If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Or ctl.ControlType = acComboBox Then
Forms(formname).Controls(mycont).BeforeUpdate = ""
Forms(formname).Controls(mycont).AfterUpdate = ""

Forms(formname).Controls(mycont).ControlSource = Forms(formname).Controls(mycont).Name
End If

Next ctl
DoCmd.Close acForm, formname, acSaveYes

End Sub