Data Validation issue on a continuous form

[thebionicredneck2003]

Hi everyone,

I would like to find out if someone has encountered this problem before or if someone knows how to solve the problem I have.

I have a continuous subform that displays lots of questions. This specific form has 10 questions on it. Each question has one answer and the users are required to select one answer for each question. I am trying to do some data validation before the data gets saved to my table to ensure that users have answered all the questions. I found some very useful code online and tweaked it to suit my needs. The validation works perfectly on subforms with only one question, as I have some other tabs that use it.

On the specific form that has the issue, the validation works as expected when no question has been answered, but when a user answers only one question and leaves the remaining nine questions blank, the validation does not work. I guess it is as a result of the continuous form repeating the different questions and ms access thinking that the data entered for one question alone is complete when it actually isn't.

I would be happy to hear from anyone who has any ideas or who has overcome this problem before. I have included the code I used below

Private Sub Ctl4_frm_Staff_Exit(Cancel As Integer)
Dim ctrl As Control, EmptyStr$
   For Each ctrl In Form_subFrm_staffQuestion.Controls
    If InStr(1, ctrl.Tag, "Required") > 0 Then
       If IsNull(ctrl.Value) Or ctrl.Value = vbNullString Or Len(ctrl.Value) = 0 Then
         ctrl.SetFocus
         EmptyStr = EmptyStr & ctrl.Name & ";"
         ctrl.BorderColor = vbRed
      End If
    End If
  Next
  If EmptyStr <> vbNullString Then
    EmptyStr = Left(EmptyStr, Len(EmptyStr) - 1)
    MsgBox "Mandatory fields incomplete: " & vbCrLf & EmptyStr & vbCrLf & "Please complete them before saving!", vbCritical, "Staff Form"
    Cancel = True
  End If
End Sub

Many thanks

[rzw0wr]

This worked for me.

Private Sub Ctl4_frm_Staff_Exit(Cancel As Integer)
Dim ctrl As Control
Dim EmptyStr As String
   For Each ctrl In Forms!Form1
        'Debug.Print ctrl.Name
        If IsNull(ctrl) Then
            ctrl.SetFocus
            'Debug.Print ctrl.Name
            MsgBox "This control is required", vbOKOnly
        End If
  Next
  If EmptyStr <> vbNullString Then
    EmptyStr = Left(EmptyStr, Len(EmptyStr) - 1)
    MsgBox "Mandatory fields incomplete: " & vbCrLf & EmptyStr & vbCrLf & "Please complete them before saving!", vbCritical, "Staff Form"
    'Cancel = True
  End If

End Sub

Change the form name in the code to your form name.

Dale

[thebionicredneck2003]

Thanks for the reply. I will try it out and will let you know how it goes

[thebionicredneck2003]

Many thanks for your solution as it works in part. My subform has two questions on one row

i.e Question 1 - Answer Comment
Question 2 - Answer Comment
Question 3 - Answer Comment

and so on.

The code works fine if there is only a question and an answer, but because there is also a comment control, the validation does not work really well as the focus always goes to the comment column after a value for an answer is selected. If the cursor is on answer 2, then the validation works if you try to save or exit.

I put this bit to force the cursor to move to the next answer

DoCmd.GoToRecord acActiveDataObject, , acNext

It works, but when it reaches the last record answer in the subform, it throws up an error message.

Just wondering if you have any more useful pointers to help me out.


Many thanks

[Missinglinq]

Your explanation is somewhat confusing, but if I understand your intent and you're validating more that one Control, and doing it by looping through all Controls, you need to exit the sub each time you find a Control that needs populating, so that the code stops looping. So whereas you had

If IsNull(ctrl) Then
    ctrl.SetFocus
    'Debug.Print ctrl.Name
    MsgBox "This control is required", vbOKOnly
 End If

you need

If IsNull(ctrl) Then
    ctrl.SetFocus
    'Debug.Print ctrl.Name
    MsgBox "This control is required", vbOKOnly
    Exit Sub
 End If

This temporarily stops the looping, allowing you to fill in the empty Control. Then when you try leaving the Ctl4_frm_Staff, again, it restarts the loop, stopping at the next empty Control, and repeats until all empty Controls have been handled.

Linq ;0)>

[rzw0wr]

Thanks Linq. I missed that.

Dale

[Missinglinq]

It's easy to do! Don't need it, of course, if you're only validating one Control, but with more than one Control, if you don't exit the sub, it just keeps running through all of them! Because of that, I usually include the command in all of my validation code; doesn't hurt anything and it's one less thing for me to have to think about!

Linq ;0)>

[thebionicredneck2003]

Thank you both for your patience and help so far. I apologise for not being to articulate my problem well enough, as I basically inherited this from someone else and I have only recently started using MS Access in this capacity and I am just coming to terms with it.

I have attached two images. Hopefully this will help to articulate the problem better. I will also try the solutions you have posted today and will let you know how it goes.


Many thanks once again for your contributions and your patience.

[thebionicredneck2003]

Just to say that with some help I found a way to fix my problem using DAO.Recordset and a loop. Thanks to everyone that has contributed in some way to helping me resolve my problem

Regards