Continuous Form Validation Issue

[thebionicredneck]

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]

See the other forum you posted this to for an answer.

Dale