Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: data entry validation rule in form

    I have two fields ("OtherRequestor" and "Requestor") in my main form (frmChangeOfControlForm). This form is to put data entry in the table "tblChangeControlForm".


    My goal is that only one of these two fields can be filled in the form. That is if the "OtherRequestor" field is filled (Not Null) then "Requestor" field cannot be filled (is null) and vice verse.


    Question:

    1) Should I put a restriction on the " Event before update" on both fields or just one of them?
    2) Neverthenless, I know I have to put a restriction on the "Event before update" in at least one of the fields I think. Can anyone help me with the Event procedure code?

    Thank you very much in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Best, imho, to use the AfterUpdate event for both fields. The idea is to make the "other" field .Locked = Yes, and .Enabled = No. Or, just the .Enabled = No. That way, both fields are available until one of them is used, then the other field is set to where the user can not enter anything into it. The AfterUpdate code would look like this:
    "Other"Field.Enabled = No
    "Other"Field.Locked = Yes
    The "Other"Field would be the name of the "other" field. That is, in the AfterUpdate event for OtherRequestor, the field name would be "Requestor". In the AfterUpdate event for Requestor, the field name would be "OtherRequestor"

    You should also put the following code into the OnCurrent event for the form.
    Code:
      Requestor.Enabled = Yes
      OtherRequestor.Enabled = Yes
      If IsNull([Requestor]) And IsNull([OtherRequestor]) Then
    'Do nothing
      Else
         If IsNull(Requestor]) Then
            OtherRequestor.Enabled = No
         Else
            Requestor.Enabled = No
         End If
      End If
    The way I have written this code assumes that when you read an existing record, you do not want to allow the user to change from Requestor to OtherRequestor, or from OtherRequestor to Requestor while editing an existing record. But, if neither Requestor or OtherRequestor has been filled in yet, then both fields will be available for the user to choose. A new record would be handled the same way, that is both fields available for the user to choose.

    If you are using the .Locked property along with the .Enabled property, you will need to add that to the code I've shown above.

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This might work

    Code:
    Private Sub Form_Current()
      Me.FirstField.Enabled = IsNull(Me.SecondField)
      Me.SecondField.Enabled = IsNull(Me.FirstField)
    End Sub
    
    Private Sub FirstField_AfterUpdate()
      Me.SecondField.Enabled = IsNull(Me.FirstField)
    End Sub
    
    Private Sub SecondField_AfterUpdate()
      Me.FirstField.Enabled = IsNull(Me.SecondField)
    End Sub
    Paul

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Sweetmail,
    Although Paul and I said the same thing, his is much easier to follow, in that his is more straight forward.

    Thanks for the great example Paul!

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As you say, they both should work. I like the simplicity of boolean logic, but some people don't like it. I figured I'd throw out the option.

    Now quit sending bad weather over the hill so I can play this weekend!
    Paul

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I'm sending great weather to you for tomorrow, hope you hit them long, straight, and seldom!

    Have a good one!

  7. #7
    Join Date
    Sep 2007
    Posts
    148
    Thank you Vic and Paul.

    I will try it out as soon as possible.

    Hope you both have great weather for the weekends.

    We have enough great weather in Georgia, we need storms badly!!!

  8. #8
    Join Date
    Sep 2007
    Posts
    148
    I use Paul's method and it works beautifully. May I ask one more question. I need at least one of the field be filled in this form. That is the form cannot be saved if both of the fields are blank.

    Thanks

  9. #9
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Use the form's BeforeUpdate event. If both fields are still blank,
    Code:
    If IsNull(Me.FirstField) and IsNull(Me.SecondField) Then
      Cancel = Yes
      Exit Sub
    End If
    I would suggest putting in an error message here informing the user that one of these two fields needs a value. You should also allow them the cancel the whole record entry from this message.

  10. #10
    Join Date
    Sep 2007
    Posts
    148
    I typed in the following

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.OtherContactID) And IsNull(Me.CobbCountyContactID) Then
    Cancel = yes
    Exit Sub
    End If
    End Sub


    The form still allow me to save with both bland entries. I try to change the

    Cancel = No to Save =no

    It works but it go into this Run-time error messge before it give the Microsoft Office Access error message.

    How can I by-pass the Visual basic run-time error message and go directly into the Microsoft Office Access error message window?

    Thanks

  11. #11
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Code:
    If IsNull(Me.FirstField) and IsNull(Me.SecondField) Then
      Cancel = Yes
      Exit Sub
    End If
    This is what I suggested, and it looks like you copied it well, but now I'm thinking I should have said True rather than Yes. Plus, just in case the field is a zero-length string, rather than a null, let's make one other change, in two locations. My new suggestion is this:
    Code:
    If len(Nz(Me.FirstField)) = 0 and Len(Nz(Me.SecondField)) = 0 Then
      Cancel = True
      Exit Sub
    End If
    The code Len(Nz(field)) will return a 0 if the field is either a Null or a zero-length string. Either way, you want to know that nothing has been input by the user. If this does not work, please let us know.

  12. #12
    Join Date
    Sep 2007
    Posts
    148
    It works.

    Thank you much.

  13. #13
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You're welcome. Glad I could help.

  14. #14
    Join Date
    Aug 2012
    Posts
    19
    I am new and facing problem for validation. I am using Access 2003 mdb. On form I want to validate 3 field
    1.Name
    2.Designation
    3.Salary
    I put the following lines. Msg box appeared but my cursor to go next field and record is saved.
    I want that cursor should stay in the box until the condition is met.
    Please help me

    Private Sub Form_AfterUpdate()
    Dim Name As String
    Dim designation As String
    If Name <> pervez Then
    MsgBox "wrong"
    Else
    MsgBox "right"
    End If
    Cancel = True
    End Sub

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why use the after update event?
    surely its better to do validation in the before update events
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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