Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.




Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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
Reply With Quote
  #4 (permalink)  
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.

Reply With Quote
  #5 (permalink)  
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


Reply With Quote
  #6 (permalink)  
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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:53 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.