Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 20th, 2005, 03:31 AM
Authorized User
 
Join Date: Aug 2004
Location: , , Belgium.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Form Validation

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.




 
Old December 20th, 2005, 08:45 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old December 20th, 2005, 01:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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
 
Old December 20th, 2005, 01:31 PM
Authorized User
 
Join Date: Aug 2004
Location: , , Belgium.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Greetings from Belgium

S.

 
Old December 20th, 2005, 07:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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


 
Old December 20th, 2005, 08:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Standalone validation + web form validation morbo Struts 0 August 19th, 2008 04:02 AM
form validation EmWebs Dreamweaver (all versions) 6 January 23rd, 2007 10:01 AM
Validation of Form qazi_nomi Javascript 5 August 2nd, 2004 03:49 PM
Form not refreshing after form validation Mimi Javascript How-To 0 August 25th, 2003 03:20 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.