Create or edit Access Form object event handler dynamically using vba code

I am working with unbound forms in Access 2007.  Once a user clicks on an item to edit a new form opens, "frm_GRP_IND_Booking" with the information requested.  The information is populated dynamically in the form controls based off the field names of the recordset ( = rst.fieldname). I need to flag the form as a being edited.  I created  checkbox control named FlagEdited to flag this on a field change.  I want the OnChange event of the fields to be dynamic like the population of the form OnLoad.  I researched on how to create events dynamically but I keep getting the following error after I update a field:  Object doesn't support this property or method.  My code is attached.  
Thanks for your help experts.
Public Sub uf_OpenRecord(frm As Form, intID As Integer)
'Displays the record determined by intRecord
'Returns the number of records in the recordset

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.RecordSet
Dim strConnection As String
Dim fld As ADODB.Field
Dim ctl As Control
Dim vartemp As Variant
'    On Error GoTo ErrorHandler
    Set cnn = CurrentProject.Connection

    'Set cnn = CurrentProject.Connection
    strConnection = "Select * From " & frm.Controls("SourceRecordSet") & " WHERE " & frm.Controls("SourceKey") & " = " & intID
    rst.Open strConnection, cnn, adOpenForwardOnly, adLockReadOnly

    'Iterate through controls on form that match fields in recordset
    For Each ctl In frm
        'if error the field is not on the form
        On Error Resume Next
        Err = 0
        vartemp = rst.Fields(ctl.Name).Name
        'MsgBox ("I ran to set vartemp " & vartemp)
        If Err = 0 Then
            On Error GoTo ErrorHandler
            'if control enabled then set default value from tag
            '   and set focus if tab index 0
            If ctl.Enabled Then
                ctl.Value = rst.Fields(ctl.Name).Value
                If ctl.Tag = "UPDATEABLE" Then
                    ctl.OnChange = "[Event Procedure]"
                    Call uf_CreateFormModuleEventHandler(frm, ctl)
                End If
                If ctl.TabIndex = 0 Then ctl.SetFocus
            End If
        End If

    GoTo Done

   MsgBox Err.Description

End Sub

Public Sub uf_CreateFormModuleEventHandler(frm As Form, ctl As Control)
Dim mdl As Module
Dim lngReturn As Long
Dim strCode As String
Dim strCtlName As String

'MsgBox (frm.Name & " Control: " & ctl.Name)

strCode = vbCrLf & "   frm.FlagEdited = True"
strCtlName = ctl.Name

Set mdl = ctl.Module
    'Add event procedure
    lngReturn = mdl.CreateEventProc("Change", strCtlName)
        'Body of procedure
        mdl.InsertLines lngReturn + 1, strCode

End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should never use methods like this in a runtime environment. Doing so will decompile your application, which can (and almost always will) lead to instability and corruption.

However: If I follow what you're trying to do then your form would have to be open in Design view to do this, which is (again) not a good idea in a runtime environment (and it also precludes you from deploying a compiled file, and it also requires your endusers to have the full version of Access instead of the runtime).

Given that your control exist at Design time, what is the reason for modifying/adding code at runtime? What is the purpose of the code? If you're trying to check a form level item in the Change event of those controls, then just add the code at design time for all your controls. If you need to "toggle" certain controls to active the checkbox, then add module-level flags and have your control's code check those flags to see if their code needs to fire.

I also would say the same as LSMConsulting - functions like "CreateEventProc" are surely possible in VBA, but their existence is not to create code "on the fly" in a regular database application, they exist to be able to create for example add-ins for the VBA editor (that's a great feature in VBA). With them you can add add-ins which could be used to create code in design time or things like a code documentation application or anything else like that.

But it doesn't mean that you cannot add events dynamically. One (the simplest) possibility is to add a lot of standard module functions and global variables to handle what a control should do dynamically.

But a more elegant variant which is much more flexible is to create a class module and use the "WithEvents" keyword. In this case you can not only create different classes for different functions, you can also add additional functionality to your controls with an easy overview what each of them should handle. An example; create a new class module and insert this:

Option Compare Database
Option Explicit

Private WithEvents ctlTextBox As Access.TextBox

Public bolChanged As Boolean

Public Sub Init(ByRef frmTextBox As Access.TextBox)
   Set ctlTextBox = frmTextBox
   ctlTextBox.OnChange = "[Event Procedure]"
   ctlTextBox.OnUndo = "[Event Procedure]"
End Sub

Private Sub ctlTextBox_Change()
    Me.bolChanged = True
End Sub

Private Sub ctlTextBox_Undo(Cancel As Integer)
    Me.bolChanged = False
End Sub

Open in new window

In the form where you want to use it you can now init all textboxes like this:

Option Compare Database
Option Explicit

Private colControls As Collection

Private Sub Form_Load()
    Dim ctl As Access.Control
    Dim objTextbox As clsevtTextBox     ' The name of the class module above

    Set colControls = New Collection
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Then
            If ctl.Tag = "UPDATEABLE" Then
                Set objTextbox = New clsevtTextBox
                objTextbox.Init ctl
                colControls.Add ctl, ctl.Name
            End If
        End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set colControls = Nothing
End Sub

Open in new window

Now the class module reacts on every defined event (you can add more in the same schema), in this case on "Change" and "Undo" to set or clear the "bolChanged" flag.

If you now want to test this flag (for example inside a save button) you can use the collection:

If colControls("NameOfYourTextbox").bolChanged Then Do Anything

Open in new window

You can also use a "For Each" loop to go through the collection and test all of them. Moreover you can now add more code like other public functions, subs or properties which do individual things. If you now need other functionality for textboxes, simply add a new class module, create any code you want for it and assign it on the same way. This allows you to change the complete functionality of each control at runtime. You could, for example, add further code in the Init sub which assigns a special design to your control like background colors or border thickness and so on. Changing at runtime then only means to remove the object from the collection and add another object from another class module to the collection with the name of the control and you have a different look and/or functionality for the new textbox.

You can also add interfaces to the class modules so they all have garantueed specific functions even if the rest is completely different. Examples would be the Init sub which must exist in any kind of such class modules or a property to clear the contents (which would be different in comboboxes, checkboxes or buttons and "clear" could mean set them to NULL or set them to any default value for this special control - different code needed, but one interface function "Clear" which can be called in a simple For Each loop through all different objects in the collection as the collection can contain any mixture of objects).

That's maximum flexibility, reusable code for different applications, maximum simplicity for the form code (less individual code needed) and anything works in runtime environment also.


BushmouseAuthor Commented:
Thanks for the input.  The above code looks like something I'm looking for.  I added the code in the OnLoad Event of the form and created a class module as suggested.  I then added the other code to the cmdSave button.  I used the field "BOOKING_NUM" from the form, and keep getting the following error when I click the cmdSave button:  "Run-time error 438.  Object doesn't support this property or method"

Private Sub cmdSave_Click()
If colControls("BOOKING_NUM").bolChanged Then
    MsgBox ("Data Changed")
End If
End Sub

Open in new window


the collection "colControls" need to be a module level variable like in the example above. You also must make sure that the collection is really filled with objects (you can test that if you set a breakpoint and use the "Locals" view to open the collection with the [+] symbol and look into it's items). Any item must be of the expected class module type and contain a "bolChanged" variable if you implemented the class module correctly.
Please remember that you must also make sure that you captured all errors with On Error in your form. If your form runs into an error which is not captured Access kills any object variable.

The above example was made to only include textboxes with a tag contents of "UPDATEABLE" like in your example, if "BOOKING_NUM" is not a textbox or has not this tag it will not be inside the collection. You can add more controls to the collection if you leave out the tag test. You can also create additional class modules for other control types, check their type in the loop above and init the right class module for the right type.

To say more about that please copy and paste the complete form code here.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.