Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validation on form (2003 sp2)

    I don't know if I'll be able to describe this properly, but here goes.

    I have this form with a variety of data displayed from a main table, plus a sub-form. There are a few fields ready for user input (some are null, others are zero).

    What I've been asked for (and don't know how to even start on) is that if any ONE of the input fields is changed, by a user, then two of the fields (name & date) must be completed (i.e. the record cannot be saved unless they are).

    Ideas please. (Stripped down version attached)
    Attached Files Attached Files

  2. #2
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation on form (2003 sp2)

    You could use the BeforeUpdate event on each input field to see if the Name and Date have data in them. If they don't cancel, then the update.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Validation on form (2003 sp2)

    Use the BeforeUpdate event of the form to check if these fields are present or not, if not set the Cancel variable.

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation on form (2003 sp2)

    Thanks for that guys. I understand what you're saying, but I don't even know where to start, with the code. Could you give an example of the code please?

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Validation on form (2003 sp2)

    When your form opens the focus is set to the Date control so for example, in the Lost Focus event of the Date control you could put the code:
    Private Sub Date_LostFocus()
    If IsNull(Me.Date) Then
    MsgBox "You must enter a date!"
    Requery
    End If
    End Sub

    If the user opens the form and clicks another control or presses the tab key the Lost Focus event is triggered and the message ot enter a date is displayed. Requery resets the form and places the focus back in the Date control, thus forcing the user to enter a date before they can enter data anywhere else on the form.

    Then on the AfterUpdate event of ExitTemp you might enter this code:
    Private Sub ExitTemp_AfterUpdate()
    If IsNull(Me.Name) Then
    MsgBox "Please select a name."
    Me.ExitTemp = 0
    Forms!frmMain!Name.SetFocus
    End If
    End Sub

    When attempting to save the data in the ExitTemp control we check to see if the Name control has a value. If it does not we display the message and reset the ExitTemp value to zero, thus not allowing data entry until the name field is populated. You can place this code or in the AfterUpdate event of all but the Date control.
    Thanks
    chuck

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation on form (2003 sp2)

    First of all, apologies for the tardy reply (family issues [img]/forums/images/smilies/sad.gif[/img] ).

    Thank you Chuck, your solution isn't exactly what I had in mind, but it's certainly given me plenty to work with, though it has raised another question.

    The first part (lost focus on the [data] field) does not work how I would have hoped. I want the users to have the form open & then search for & go to specific records. With the code you've supplied, no record can be viewed without entering a date. However this can be left blank unless the record is changed in any way. So I'll leave that bit out for the mo'.

    I liked the idea of the AfterUpdate, but it doesn't seem to work. I did try using

    If Me.Name = "" Then

    But had the same result.

    Sorry if this sounds bad (I'm more used to doing stroppy letters at the moment!)


    Update, the code works fine with the [Date] field instead <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation on form (2003 sp2)

    Put this code in the before update event of the form :
    <pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.Date) Or IsNull(Me.Name) Then
    MsgBox "Enter Date and name please"
    Cancel = True
    End If
    End Sub</pre>

    Francois

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation on form (2003 sp2)

    Cheers François. Unfortunately the part relating to whether the [name] field is null does not work. This can still be left null & the code does not pick it up.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation on form (2003 sp2)

    Try the following :
    <pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.Date) Or IsNull(Me.Controls("Name")) Then
    MsgBox "Enter Date and name please"
    Cancel = True
    End If
    End Sub</pre>


    P.S. You should not use Date and Name as field or control names as they are reserved words.
    Francois

  10. #10
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validation on form (2003 sp2)

    Ah, that's done it mate. Thank you.

    Good point about the field names, not even sure why I did them that way.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •