Form Error Checking

[Davrini]

Hi All,

I have a problem, as those that find their way here often do.

I have been in charge of administrative systems within a counselling service that I work for, and as part of that have developed a fairly complex database around confidential information, appointment diaries, statistics, reports and research activities. As the service has gotten bigger, and I have been unable to keep on top of the volume of work, other people have had to get in involved with using the database, and they aren't all that tech savvy.

I'm in the process of making it as user-friendly as possible (not that it isn't already), but on top of that trying to input error checking capabilities along the way.

We have a number of people who input data into forms for research purposes. One of these forms are below. What I am trying to do, but keep getting stuck, is for the form to check if all data fields have a value. If they do NOT all have a value, I want a MsgBox to pop up saying "Please check the following fields:" and then to list the fields that are missing. Now, there will be occasions where the data is missing on purpose where the person completing the form hasn't filled it in, and this is why I simply cannot set each field to 'Required'.

For some reason:

If ClientID Is Null Then ... Doesn't work. Nor does 'If FrameQ1 is Null Then'... Also, to be honest, I'm not sure how to apply what is missing in the MsgBox.

Any help at all would be greatly appreciated with this!

Many thanks,
Davron

P.s. Not sure if this belongs in the VBA/Module section either!

 

[vbaInet]

Like so:

If Len(Me.ClientID & vbNullString) = 0 Then
    Msgbox "Please enter a value for Client ID"

 

[burrina]

You could also add this code to your form for a global approach.

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


On Error GoTo Form_BeforeUpdate_Error

nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next

Also you would need to add to your Tag Property.
"(*)"

HTH

 

[vbaInet]

Obviously best to understand how the simple validation code works and make it work in your case first before you delve into something more complex.

 

[Davrini]

Hi Guys,

Thanks for all the suggestions! I have played around with it this morning, using vbaInet's suggestion. It's a bit lenghty, and will be more so for the other forms (this is the shortest one!) but here's the code:

Dim LResponse As Integer
Dim lngRed As Long
Dim lngBlack As Long

---------------------------------------------------------------

Private Sub ColourReset()

Me.ClientID.BorderColor = lngBlack
Me.DateCompleted.BorderColor = lngBlack
Me.FrameQ1.BorderColor = lngBlack
Me.FrameQ2.BorderColor = lngBlack
Me.FrameQ3.BorderColor = lngBlack
Me.FrameQ4.BorderColor = lngBlack
Me.FrameQ5.BorderColor = lngBlack
Me.FrameQ6.BorderColor = lngBlack
Me.FrameQ7.BorderColor = lngBlack

End Sub

---------------------------------------------------------------

Private Sub cmd_addnew_Click()

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
    
If Len(Me.ClientID & vbNullString) = 0 Then
    Me.ClientID.BorderColor = lngRed
    Else
    Me.ClientID.BorderColor = lngBlack
End If

If Len(Me.DateCompleted & vbNullString) = 0 Then
    Me.DateCompleted.BorderColor = lngRed
    Else
    Me.DateCompleted.BorderColor = lngBlack
End If

If Len(Me.FrameQ1 & vbNullString) = 0 Then
    Me.FrameQ1.BorderColor = lngRed
    Else
    Me.FrameQ1.BorderColor = lngBlack
End If

If Len(Me.FrameQ2 & vbNullString) = 0 Then
    Me.FrameQ2.BorderColor = lngRed
    Else
    Me.FrameQ2.BorderColor = lngBlack
End If

If Len(Me.FrameQ3 & vbNullString) = 0 Then
    Me.FrameQ3.BorderColor = lngRed
    Else
    Me.FrameQ3.BorderColor = lngBlack
End If

If Len(Me.FrameQ4 & vbNullString) = 0 Then
    Me.FrameQ4.BorderColor = lngRed
    Else
    Me.FrameQ4.BorderColor = lngBlack
End If

If Len(Me.FrameQ5 & vbNullString) = 0 Then
    Me.FrameQ5.BorderColor = lngRed
    Else
    Me.FrameQ5.BorderColor = lngBlack
End If

If Len(Me.FrameQ6 & vbNullString) = 0 Then
    Me.FrameQ6.BorderColor = lngRed
    Else
    Me.FrameQ6.BorderColor = lngBlack
End If

If Len(Me.FrameQ7 & vbNullString) = 0 Then
    Me.FrameQ7.BorderColor = lngRed
    Else
    Me.FrameQ7.BorderColor = lngBlack
End If

If Len(Me.ClientID & vbNullString) = 0 Or Len(Me.DateCompleted & vbNullString) = 0 Or Len(Me.FrameQ1 & vbNullString) = 0 Or Len(Me.FrameQ2 & vbNullString) = 0 Or Len(Me.FrameQ3 & vbNullString) = 0 Or Len(Me.FrameQ4 & vbNullString) = 0 Or Len(Me.FrameQ5 & vbNullString) = 0 Or Len(Me.FrameQ6 & vbNullString) = 0 Or Len(Me.FrameQ7 & vbNullString) = 0 Then
    LResponse = MsgBox("There are some fields missing." & Chr(13) & Chr(13) & "Please check all data before continuing." & Chr(13) & Chr(13) & "Do you wish to continue?", vbYesNo, "Continue")
    If LResponse = vbYes Then
        Call ColourReset
        DoCmd.GoToRecord , , acNewRec
    End If
Else

Call ColourReset
DoCmd.GoToRecord , , acNewRec

End If
End Sub

As you can see, I decided to point the user to the missing fields by changing the colour of the border as opposed to telling them directly what it is. This works really well for this form, but is there any way I could condense any of the text above (especially the Len code that triggers the MsgBox) to make it simpler as one of the forms has 39 questions on it, and that's a lot of editing!

You guys and gals continue to be an awesome font of knowledge!

Thanks,
Davron

 

[vbaInet]

Good to see you got it working!

I don't think going to a new record is a good idea. If the user has already entered some valid data into some fields, this means s/he would have to re-enter the entire data again.

 

[Davrini]

The MsgBox allows the user to select 'No' when asked if they are sure that the data they have inputted is correct. if they click 'Yes', then a new record is created. If they click no, it simply closes the MsgBox and takes them back to the form.

So:

If Data missing > 
     Highlight what is missing
     MsgBox asking if they are sure the data is correct > 
          If No, close MsgBox and return to form to allow them to continue inputting data
          If Yes, New Record and reset colours 
Otherwise (no missing data) > 
     New Record and reset colours

This data is research data completed by clients, and some of our clients don't like to answer a question such as "I like my counsellor". Maybe they don't, but don't want to write that down, so they purposefully don't answer it. The fact that they don't answer it is data in itself, so there has to be a way to check the data without forcing an answer.

Hope this clarifies the need for the above!

Again though, any way to make it less messy!?

Thanks,
Davron

 

[vbaInet]

Your message doesn't really relay this message. As a user I would have preferred to see, "Some fields are missing. Would you like to discard the changes or correct them?". Not verbatim of course but you get the idea. What vbYes response code is doing isn't "continuing", it's "discarding".

So to correct this behaviour:
1. move the validation code to the Before Update event of the form
2. ask the user if s/he wishes to correct the errors or discard the changes/new record (i.e. vbYes + vbAbort)
3. if the user selects vbAbort set the Cancel parameter of the Before Update event to True to cancel the changes
4. if the user selects vbYes highlight the controls (like you're doing) and move the focus back to the control.
5. Repeat the same process (with a different wording) in the Before Insert event of the form.

 

[Davrini]

I have tested it many times, and perhaps the word "continue" may be incorrect when I'm asking them to check the data if it is missing. But when you press the 'no' button, it just closes the MsgBox, and when you press the 'Yes' button, it saves the entry and starts a new record, so no data is lost if they select the 'No' option. I'm not at all concerned about them wanting to discard the data they have inputted. If they wanted to do that, they could simply overwrite everything on the form, though i guess a discard option would be a good idea. Hmmm...

(there are also query checks that run to check if things match, such as research date and client attendance, both information kept in separate databases but linked)

I have tested it a fair bit and at the moment, the relatively simple, if not long, code works like a charm. It does exactly what I want it to do. Just wondering if there is any way to simplify it further.

 

[vbaInet]

I see! So you're happy with the user ignoring the blanks and carrying on with entering new data? That's fine then.

To shorten your code, here's some aircode:

Dim strControls(8) As String
Dim ctl as Access.Control
Dim strCtlName as String

With Me
    strControls(0) = .ClientID.Name
    strControls(1) = .DateCompleted.Name
    [COLOR="Blue"]...etc[/COLOR]
End With

For each strCtlName in strControls
    Set ctl = Me.Controls(strCtlName)

    With ctl
        If Len(.Value & vbNullString) = 0 Then
            .BorderColor = [COLOR="blue"]vbRed[/COLOR]
        Else
            .BorderColor = [COLOR="blue"]vbBlack[/COLOR]
        End If
    End With
Next

You could create a separate function that does the validation and call that function within the loop. And you would also notice that I'm using constants for the colour.