Bad Data is Bad for Business #1 and #2

[Pat Hartman]

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy

I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a video to talk about the example. It's about a half hour long. Please take a look if you have time and we'd love to hear your comments.

You should be able to click on play without having to create an account.

Bad Data is Bad for Business - Pat & Tony (screencast-o-matic.com)

I have a couple more examples to add to the database and when I'm finished, I'll post the actual database for you to play with. It is a great learning tool if you care to learn how Access works so you can use Form and Control events as the MS Access design team intended them to be used. This is a difficult topic for even experienced developers to get their heads around. The video is longer than I'd like but I'm new at this. Once you view this one, the others in the set can be much shorter. I will explain the Contro''s BeforeUpdate event and when you can use it and when you shouldn't use it for validation. and also why you should NOT use the On Exit in particular for validation. You can keep Access from leaving a control but unless you take Draconian measures and remove the user's update, you can't stop Access from saving the bad data without code in multiple events. If you limit yourself to coding your validation in the form's BeforeUpdate event, you only ever need to use ONE event to have complete control over whether or not a record gets saved.

Here is the code in the State form's BeforeUpdate event along with the two functions it calls so you can see what is running behind the scenes in the video.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Call LogEvent(Me, "Form_BeforeUpdate")
  
    On Error GoTo ErrProc
  
    If EditRequired(Me) = "Error" Then      ' check required fields
        Cancel = True
        Exit Sub
    End If
  
    If Me.Population & "" = "" Then
    Else
        If IsNumeric(Me.Population) Then
            If Me.Population < 0 Then
                MsgBox "Population must be a positive number", vbOKOnly
                Cancel = True
                Me.Population.SetFocus
                Exit Sub
            End If
        Else
            MsgBox "Population must be numeric.", vbOKOnly
            Cancel = True
            Me.Population.SetFocus
            Exit Sub
        End If
    End If
          
  
    Me.UpdateBy = Environ("UserName")
    Me.UpdateDT = Now()
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & " -- " & Err.Description
            Resume ExitProc
    End Select
End Sub

Public Function LogEvent(frm As Form, EventName As String)
    Dim strSql As String

    Select Case Forms!frmEventSamples!fraControlLogging
        Case 2          ''Form events only
            If Left(EventName, 5) = "Form_" Then
            Else
                Exit Function
            End If
        Case 3          ''Control events only
            If Left(EventName, 5) <> "Form_" Then
            Else
                Exit Function
            End If
        Case 4          ''No Logging
            Exit Function
        Case Else   '' Log all events with log code
    End Select
  
    strSql = "Insert Into tblEventLog ( FormName, EventName, FormType) Values(" & QUOTE & frm.Name & QUOTE & ", " & QUOTE & EventName & QUOTE
    strSql = strSql & ", " & QUOTE & Left(frm.Name, 1) & QUOTE & ");"
    DoCmd.RunMacro "mWarningsOff"
    DoCmd.RunSQL strSql
    DoCmd.RunMacro "mWarningsOn"
    Forms!frmEventSamples!sfrmEventLog.Requery
End Function

Public Function EditRequired(frm As Form) As String
    Dim ctl As Control
  
    EditRequired = "Error"
  
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If InStr(ctl.Tag, "Required") Then              ''' used to allow the Tag property to be used for multiple procedures
                        If ctl.Value & "" = "" Then
                            MsgBox ctl.Name & " -- is required."
                            ctl.SetFocus
                            Exit Function
                        End If
                End If
        End Select
    Next ctl
    Set ctl = Nothing
  
    EditRequired = "Valid"
End Function

 

[Jan Pawlowski]

Interesting piece. I'll check out when I'm home. My PC at the office has not way of outputting sound.

 

[Mike Krailo]

Ooooh, that's a nice demo Pat. Now you can just put a link to that video in your signature and just keep referring to it.

 

[Mike Krailo]

I will explain the Contro''s BeforeUpdate event

There's a typo here in case you didn't see it yet.

 

[arnelgp]

...so, where is the Bad data?

 

[NauticalGent]

Excellant video you two. That should be required viewing for anyone wanting to build forms in Access.

 

[AccessBlaster]

Very nice!

Thank you both for taking the time to make clear the logic behind the Form's BeforeUpdate event.

 

[jdraw]

Here's the link to the original post that also has links and comments to and about the screencast-o-matic video.

 

[Mike Krailo]

Is there a link to the actual demo database? I would like to play around with that for sure. Seeing the events in order like that is really nice.

 

[AngelSpeaks]

Thanks. I'll take a look later

 

[Pat Hartman]

The db is still a work in process so I didn't post the db but I intend to at some point. I'm trying to figure out the best way to let you use this tool in your own database. Importing the tables, queries and forms is one thing but to actually use the functionality of the background form, you need to add the call to the logging function to all the form's events.

Here's a couple of the events. I put code in all the form's events that deal with "forms" but not any that deal with the web stuff. I know we had a discussion on the forum a month or so ago where someone was trying to figure out how to identify the name of a procedure. I couldn't figure it out so, I hard coded it. Notice that the first argument is "Me" which refers to the form's name but the second argument is the name of the control event that is running the code. I hard coded it in all the events. This was no problem for my test form because it didn't have any existing code. If you want to test your own forms, I need to figure out how to get the call's loaded into your form's events without interfering with your existing code. I haven't worked on this yet but if anyone has an brilliant ideas, I'd love to hear them. My current idea is to have you pick a form or bunch of forms from a list. Then my code would open the code module and add the call as the first statement in each sub. The problem is I don't know how to do this except by reading the text and searching for "Sub Form_BeforeDelConfirm" and using that to locate the line of code that is the sub header. Then putting my call after it and moving on. If the search doesn't find the event name, it would need to insert three lines instead of just one.

I look forward to hearing your brilliant suggestions

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    Call LogEvent(Me, "Form_BeforeDelConfirm")
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    Call LogEvent(Me, "Form_BeforeInsert")
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call LogEvent(Me, "Form_BeforeUpdate")
End Sub

@Mike Krailo I think I fixed that typo in the latest change but it's staying in the video
@arnelgp If there is something you think is wrong with the video, please tell me. But leave the snarkiness for some other time. It is not amusing.

 

[arnelgp]

I look forward to hearing your brilliant suggestions

i thought it was all about the All-in-one BeforeUpdate event? now 2 additional events, make-up your mind.

 

[Pat Hartman]

Please tell me exactly what you are objecting to. I did mention other events during the video but not because you need to use them for validation. I mentioned them as a way of explaining how the mainform/subform link works.

I understand that your comments are not intended in any way to be helpful. You are looking for some kind of gotcha but you need to be clear in what mistake you think I made. If I made a mistake, I do want to know what it is so I can withdraw the video and correct it.

 

[kentgorrell]

suggestions

Code to insert Code

Public Sub EventProcedure_Insert()
On Error GoTo Error_Handler
Dim Frm As Form, mde As Module, obj As Object
Dim strForm As String
Dim strTarget As String, strCode As String, strEnd As String
Dim lngStartLine As Long, lngStartColumn As Long, lngEndLine As Long, lngEndColumn As Long
Dim blnSave As Boolean
   
    strTarget = "Private Sub Form_Open(Cancel As Integer)"
    strCode = "    ' Whatever"
    strEnd = "End Sub"
   
    For Each obj In CurrentProject.AllForms
        strForm = obj.Name
        DoCmd.OpenForm strForm, acViewDesign
        Set Frm = Forms(strForm)

        Set mde = Frm.Module
        With mde
           
            ' note - the find method Uses ByRef to return the positions for start and end so we need to reset them to 1
            lngStartLine = 1: lngStartColumn = 1: lngEndLine = 1: lngEndColumn = 1
            If .Find(strTarget, lngStartLine, lngStartColumn, lngEndLine, lngEndColumn) Then
                ' The Target Procedure already exists
                If .Find(strCode, lngStartLine, lngStartColumn, lngEndLine, lngEndColumn) Then
                    ' AND The Code Exists
                    blnSave = False
                Else
                    ' we only need to insert the code
                    .InsertLines lngEndLine + 1, strCode
                    blnSave = True
                End If
            Else
                ' we need to insert the Procedure, Code and End
                ' so let's put it at the bottom of the module
                lngEndLine = .CountOfLines
                .InsertLines lngEndLine + 1, strTarget
                .InsertLines lngEndLine + 2, strCode
                .InsertLines lngEndLine + 3, strEnd
                blnSave = True
            End If
        End With
        DoCmd.Close acForm, strForm, blnSave
    Next obj
   
Exit_Procedure:
   On Error Resume Next
   Exit Sub
Error_Handler:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in EventProcedure_Insert"
    Resume Exit_Procedure
    Resume
End Sub

 

[arnelgp]

Code to insert Code

so you will insert the module/code to each forms even if you don't have any code in the Open event?

 

[Pat Hartman]

@kentgorrell Thanks, I'll see if I can work with that.

 

[Mike Krailo]

The db is still a work in process so I didn't post the db but I intend to at some point. I'm trying to figure out the best way to let you use this tool in your own database. Importing the tables, queries and forms is one thing but to actually use the functionality of the background form, you need to add the call to the logging function to all the form's events.

I'm thinking a class module that addresses each form event (withevents), and then the user can simply call the class module in any form they choose to use it with which would setup all the form events. The events do not have to be hard coded in there doing it this way.

The user could import the form and table that logs the events along with the class module that creates the events into a copy of their project that doesn't have any events in it. Then they could experiment using their own forms and seeing the sequence of events as you demoed in your video.

 

[Pat Hartman]

I didn't use a class module to build this. A class module wasn't going to help to determine the name of the procedure so it could be written to the log. That is why I added the call to each module. Also, a class module is inflexible in that the user won't get to not use the call in any specific event. I added the call for reference but commented it out in the mouse move in my sample because it was just too annoying. But, if what you are trying to understand is the Mouse Move event, then you can just turn it on.

 

[Mike Krailo]

I guess since I can't see what you have already did, it will be harder to determine what can be done. I was just learning about withevents recently and was amazed at what can be done in terms of not even having any events at all in the target form and yet using a class module code for any required event can be created behind the scenes so to speak. This appears to be a perfect use case for doing just that. As far as name of procedure, that would be done in the class module and on the target form, simply use ME as the argument to the function. This is just an idea that seems reasonable to me and that's what popped in my head the moment I saw your demo.

There might be a way around the inflexibility you mentioned but I was imagining just using this on a copy of a users database that had all the events removed for testing only. A fancier version would just check for existing events first and fill in the one's not used. The class module could have an argument for turning this extra functionality on or off as required by the user so something like: ExamineEvents(Me, False) would only work on existing events the user has created and ExamineEvents(Me, True) would create the additional unused form events for the user. Again, this is just an air code idea.

 

[Pat Hartman]

I posted a little code earlier to show how the event logging works. Each event you want to log has a call to the logging code. The problem I ran into was that I couldn't find any way in VBA to determine the name of the procedure my code was executing in. Hence, I had to hard code the name of the procedure in the call to the logging procedure. All the logging code does is to insert a record in the log table using the two values passed to it.

The point of the logging is to highlight the sequence of events that run your code. The simple act of putting the logging call into an event forces it to fire when Access gets to the point in its "mainline" where it will use its "hook" to call your event code. If the event has no code, Access doesn't run it. Most of the events in my sample form don't have any code except the call to the logging procedure. That leads to the problem of automatically inserting the code. You can do it manually but most people will just insert the logging into the events for which they have code or embedded macros and since I don't ever use embedded macros, I don't know if the macro runs first or the VBA runs first. That might be something to add to the sample. But, to actually give you a good picture, the most important form events need to be logged whether or not you have code in them. I'll make a list of those events just for reference. For example, way too many people don't understand that the Form's AfterUpdate event runs "AFTER" the form's BeforeUpdate event and make the mistake of putting code in the After event that modifies the current record which puts Access into an infinite loop.

The point of the class module is that it does the same thing each time it runs. If you need to support multiple paths, you are probably misusing the class module. If you use the "with events" to attach the call to each event, you have two issues.
1. how to determine the name of the event being logged
2. how to not execute the logging for a particular event.

You can always turn off the logging by adding an If to the log procedure. That allows you to add the code to all your events for testing but not have to remove the code once your're done. You can just use a table with a value and the logging procedure will check the table and log or not log based on that value. I suppose you could also keep a table of events you don't want to log for and then have the logging procedure use that table and compare the procedure name passed to it. If the name is in the table, don't log.

Also, does "withEvents" modify only events with code or does it modify all events?