Search Down atm - Error Handling Code

[JMongi]

I tried evaluating the expression in an immediate window and got buzzed. Probably doing it wrong
Anywho, so a numerical entry is evaluated as a string by Len()? If so, I don't see how this accounts for a 0 entry? Which is why my current checker fails. It only checks for nulls and the combo boxes are returning 0s even if I don't add anything but that passes the checker since its not null.

 

[JMongi]

which will be a coerced to the string type by way of the concatenation

Ok....this the key. So the concatenation is a VBA shortcut (so-to-speak) to convert the number to a string?

 

[JMongi]

So, to check for 0s using your method would be:

If Len("" & Me.Control.Value) = 0 OR Control.Value = 0 Then
'more code here

 

[JMongi]

For MajP's code:

if trim(ctrl & "") = "" or ctrl = 0

[Temp Removed - Cases all wrong]
Don't need to work through the cases anymore. I think I understand what this one is doing now too.

 

[Isaac]

So, to check for 0s using your method would be:

If Len("" & Me.Control.Value) = 0 OR Control.Value = 0 Then
'more code here

Yes, I guess you could add the second bit (after the OR) if you have default values that you need the user to have changed

 

[JMongi]

Sorry for the confusion @Isaac - Somewhere along the way I though your code handled 0s too (which is the issue I was having after I implemented my own null check).

Thanks to Isaac and MajP for their code snippets. I think I like MajP's suggestion best since it handles all of the gotchas (Null, Zero Length String, Spaces and Zeros).

 

[MajP]

I don't want to use it until I understand what it's actually doing as a check.

It is just a quick way to cover some less common "empty" conditions.

1. someValue & "" = "someValue"

2. Null & "" = ""

Trim removes any empty spaces before or after a string " Some String " = "SomeString"
3. trim " " = ""

Empty space and empty = empty
3. "" & "" = ""

so trim(ctrl & "") = ""
a null becomes ""
a "" becomes ""
a " " becomes ""

It is a short version but you can handle all of those. If you wanted to check for zero too then should be able to

it trim(ctrl & "") & 0 = "0"
because "" & 0 = "0"

Getting a empty string "" in the control is possible but not common. Getting a space/s is even harder " " and probably only happens in an import. But this covers all cases.

FYI only variants can ever be null. So this catches a lot of people on checks. All other value types have a default
dim x as string
' at this point x is not null but ""
msgbox isnull x
returns false because it is an empty string not null

dim y as integer
msgbox isnull y
returns false because a default integer = 0

One more thing
dim x as variant
msgbox X = null
This returns false always. Null = Null returns false.
Must use isnull()

 

[JMongi]

Thanks MajP! I had worked through most of those but seeing you do it helped reinforce it.

Good News Everyone! - The function check works!!
Good News #2! - The error checking is working too as I'm getting some access errors because of my rough code handling saves/closes but it is successfully trapping and displaying the error codes which should now help with troubleshooting.

 

[MajP]

One more thing. I often see people check a date or numeric value and they use isnull. That only checks if it is null. Most of the time you want a date and a number so better

if IsDate(x)
or
isNumeric(Y)

Is numeric is not full proof but handles 99.9% of numbers.

 

[JMongi]

Here is the full module code with Function and Public Sub for Error Processing

Option Compare Database
Option Explicit
Global DeveloperMode As Boolean
Function FormEntryChk(FormName As String) As Boolean
'This function is used as a basic check for entries with null, ZLS, spaces and zeros
'Tag all form controls that need validated with tag "validate"
'Function returns "True" if check passes and "False" if one or more controls fail.
'Flagged Controls Datasheet Captions are added to a user messagebox.

On Error GoTo ErrHandler

Dim ctl As Control
Dim FlagCtl As String

FormEntryChk = True
FlagCtl = " "
For Each ctl In Forms(FormName).Controls
    If ctl.Tag = "validate" Then
        If Trim(ctl.Value & "") = "" Or (ctl.Value) = 0 Then
            FlagCtl = FlagCtl & ctl.Properties("DatasheetCaption") & vbCrLf
            FormEntryChk = False
        End If
    End If
Next ctl

If FormEntryChk = False Then
    MsgBox ("Please complete the following fields before saving:" & vbCrLf & vbCrLf & FlagCtl), vbInformation
End If

ExitHandler:
'Add Further Code After Error Processing If Needed
Exit Function

ErrHandler:
Call ErrProcessor
FormEntryChk = False 'I don't want to save a record if there is an error.
Resume ExitHandler
    
End Function
Public Sub ErrProcessor()

'Developer mode = Yes results in descriptive errors and no logging
'Developer mode = No results in generic error message and error logging

If StandardFunctions.DeveloperMode = True Then
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Program Error"
Else
    MsgBox "There was an unexpected error.  Please contact your administrator.", vbExclamation, "Program Error"
    'ErrorLog - capture user, form, error, error description, date and time
End If
End Sub

 

[Isaac]

Awesome, glad to hear you got it working

 

[Isaac]

One tiny possible improvement. The way you have structured your logic, the first control that's bad triggers the flag, so, at that point you could also Exit For, thus possibly speeding up the code ever so slightly. But I'm nitpicking.

 

[Minty]

One tiny possible improvement. The way you have structured your logic, the first control that's bad triggers the flag, so, at that point you could also Exit For, thus possibly speeding up the code ever so slightly. But I'm nitpicking.

@issac - The whole point of doing this way is to highlight all the missing values in one go rather than repeating the error message for each attempt at saving.

 

[Isaac]

Oops, my bad. I failed to notice that a message was being slowly built up for the user too. Because of that I see your point.

 

[JMongi]

As an semi-interesting anecdote, a previous employee created (well, I think an outside consultant, plus this employee) an Excel form for our service tech field service reports. Mandatory fields were highlighted in red (there were 40-50). If someone accidentally clicked the save button early in the process, it would run a check and pop an error message dialog box...for EACH missing cell. One of the first things I altered when I reworked it in Excel. I'm looking forward to reimplementing that form in Access when the time comes.