Form Validation

Stanny · December 20th, 2005, 03:31 AM

Hi Guru's,

I want to add a simple validation to a form, using VBA to be sure all mandatory fields are filled,
Mandatory fields in my form are dynamic,.... lets explain
if field a is entered, field b is no longer mandatory
if field b is entered, field a is no longer mandatory
........

before the form updates, I want to raise a msgbox, telling the user 'Not all mandatory field (a or b or.... ) is not entered, with possibility for the user to overrule (yes / no)
is this possible ??

Thx in advance
S.

mmcdonal · December 20th, 2005, 08:45 AM

In Table Design, select Required = Yes for each field. Then Access will take care of validation for you. If you have already created the table and not selected Required, AND you have already created the form, if you go to the table and change the field to Required, you may need to delete the effected fields from the form, and add them again from the fields list. You MAY need to do that, I am not sure.

If you need code to do this, then we can help.

HTH

mmcdonal

Bob Bedell · December 20th, 2005, 01:17 PM

Hi S,

How are your text boxes laid out on the form? Are they bound or unbound?

This is kinda' just for sport. It uses unbound textboxes laid out left to right, moving down the form. Since both fields aren't required, the code scans the form for all text box controls, loads them into a collection, evaluates one pair at a time (top two collection items) for Null, removes the first pair of items from the collection, and loops through the collection till all pairs of text boxes have been evaluated. Then displays a message like:

The following required entries were left blank:

txtA
txtB

Please fill in one of these values.

The following required entries were left blank:

txtC
txtD

Please fill in one of these values.

Code:

Option Compare Database

Private m_col As Collection
Private m_strMsg As String

Private Sub cmdOK_Click()
    Dim ctl As Control
    Dim i As Integer

    Set m_col = New Collection

    For Each ctl In Me.Controls
        With ctl
            If .ControlType = acTextBox Then
                m_col.Add (.Name)
            End If
        End With
    Next

    Do Until m_col.Count = 0
        ValidateControls
    Loop

    If Len(m_strMsg) <> 0 Then
        MsgBox (m_strMsg)
        m_strMsg = ""
    End If

End Sub

Private Sub ValidateControls()

    Dim strMsg As String
    Dim intIndex As Integer

    Call NullCheck(Me, m_col(1), m_col(2))

    For intIndex = 1 To 2
        m_col.Remove (1)
    Next

End Sub

Public Sub NullCheck(frm As Form, _
    ParamArray varListTextBoxes())

  Dim intI As Integer
  Dim ctl As Access.Control
  Dim strMsg As String
  Dim strFirstNullControl As String
  Dim blnNullFound As Boolean
  Dim blnMultipleNullsFound As Boolean

  For intI = LBound(varListTextBoxes) _
    To UBound(varListTextBoxes)

    Set ctl = frm(varListTextBoxes(intI))

    If ctl.ControlType = acTextBox Then
        blnNullFound = IsNull(ctl)
    End If

    If blnNullFound Then
      strMsg = strMsg & varListTextBoxes(intI) & vbCrLf
      If Len(strFirstNullControl) = 0 Then
        strFirstNullControl = varListTextBoxes(intI)
      Else
        'More than one null was found
        blnMultipleNullsFound = True
      End If
    End If
  Next intI

  If blnMultipleNullsFound Then
      'There was more than one null Control found.
      m_strMsg = m_strMsg & "The following required entries were left blank:" _
       & vbCrLf & vbCrLf & strMsg & vbCrLf _
       & "Please fill in one of these values." & vbCrLf & vbCrLf
   End If

End Sub

Thanks for the puzzle.

Bob

Stanny · December 20th, 2005, 01:31 PM

Thx for both replies, I'll try to figure out the code

Greetings from Belgium

S.

Bob Bedell · December 20th, 2005, 07:59 PM

Hereâs another approach that might be worth looking at. You could create a form class property (Property Get and Let procedures) for each textbox. The AfterUpate event of each textbox stores the textboxâs Text property in the module-level property variable assigned to it. Some logic in a Save buttons click event evaluates each property variable and displays a message if two paired textboxâs are empty.

Option Compare Database

Private m_TextBoxA As String
Private m_TextBoxB As String

Public Property Let TextBoxA(strText As String)
     m_TextBoxA = strText
End Property

Private Property Get TextBoxA() As String
    TextBoxA = m_TextBoxA
End Property

Public Property Let TextBoxB(strText As String)
     m_TextBoxB = strText
End Property

Private Property Get TextBoxB() As String
    TextBoxB = m_TextBoxB
End Property

Private Sub TextA_AfterUpdate()
   TextBoxA = TextA.Text
End Sub

Private Sub TextB_AfterUpdate()
   TextBoxB = TextB.Text
End Sub

Private Sub cmdSave_Click()
   Dim strTextBoxA As String
   Dim strTextBoxB As String

   strTextBoxA = TextBoxA
   strTextBoxB = TextBoxB
   If Len(strTextBoxA) = 0 Then
      If Len(strTextBoxB) = 0 Then
         MsgBox "You must supply a value for eithe TextBoxA or TextBoxB"
      End If
   End If

End Sub

HTH,

Bob

Bob Bedell · December 20th, 2005, 08:13 PM

And the easieat way yet...plain old module level variable:

Private m_TextBoxA As String
Private m_TextBoxB As String

Private Sub TextA_AfterUpdate()
   m_TextBoxA = TextA.Text
End Sub

Private Sub TextB_AfterUpdate()
   m_TextBoxB = TextB.Text
End Sub

Private Sub cmdSave_Click()
   If Len(m_TextBoxA) = 0 Then
      If Len(m_TextBoxB) = 0 Then
         MsgBox "You must supply a value for eithe TextBoxA or TextBoxB"
      End If
   End If

End Sub

The user override part depends on the type of form set up you are using: bound or unbound.

Bob