Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2012

    Lightbulb Unanswered: Data entry validation rule in Form

    Hello all,

    I need help with a data entry validation rule in Access Forms.

    I'm using Access 2010 on windows

    What I have:
    Table named: clients table
    Form named: clients data form

    What I'm trying to accomplish:
    The form has the client information. The client sends me a document. I review the document. If the document has the correct info needed, then I have a field named "Incomplete" that is a Yes/No field in the clients table.

    If the field is clicked, then I want the next date field to appear and then I can fill in the date that it was sent back to them.

    If the field "incomplete" field is not checked, then I don't want that date field to appear nor do I want to fill it out.

    I have searched many threads on the forum and I can't seem to figure it out still.
    The best thread/most similar thread I found was this one:

    After reading around, I think could will be the best option but I'm not that good with access and havne't been able to solve it.

    I have attached a screen shot to give you an idea of what I mean.

    Any help is greatly appreciated!
    Attached Thumbnails Attached Thumbnails incomplete.jpg  

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Quote Originally Posted by AccessN00b View Post

    ...If the document has the correct info needed, then I have a field named "Incomplete"...If the field is clicked, then I want the next date field to appear and then I can fill in the date that it was sent back to them.
    The first requirement for successful programming is attention to detail! Don't you mean "If the document doesn't have the correct info needed" you'll tick the "Incomplete" Checkbox?

    This is simple enough to do, but you need to tell us what you want to happen later, when the information is completely in hand; do you want the date field emptied and the Control to disappear, or do you want it to remain, for historical/tracking reasons?

    If you want the Control to remain, once the SentBack Field has a date entered, you can use something like this:
    Private Sub Incomplete_AfterUpdate()
     If Me.Incomplete = -1 Then
      Me.SentBack.Visible = True
      Me.SentBack.Value = Null
      Me.SentBack.Visible = False
     End If
    End Sub

    Private Sub Form_Current()
     If Nz(Me.SentBack.Value, "") = "" Then
      Me.SentBack.Visible = False
      Me.SentBack.Locked = False
      Incomplete.Locked = False
      Me.SentBack.Visible = True
      Me.SentBack.Locked = True
      Incomplete.Locked = True
     End If
    End Sub

    If the user mistakenly ticks the Incomplete Checkbox, and enters a date, they can untick it, and all of that will be reversed, until the Record is Saved! Once the Checkbox is ticked, a date entered, and the Record is Saved, the date is there to stay and Incomplete cannot be unticked.

    If Incomplete and SentBack are not the actual names of your Controls, replace them in the code above with the actual names.

    And let us know if your requirement isn't met by this example.

    Linq ;0)>
    Last edited by Missinglinq; 12-07-12 at 18:49.
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Dec 2012
    Thanks a ton for the quick reply.

    Also, sorry about the confusing in my description. I went back and reread it. You are correct.

    If the incomplete box is checked, then I want the "sent back" section to appear. Logic: if it's incomplete or formatted incorrectly, then I'm going to send it back for them to fix and then they will send it back to me.
    So if the "incomplete" or "wrong format" boxes are NOT checked, then I don't want the gray box below to appear.

    The received back date field is when they send it back after they had corrected the format or sending it back after completing what wasn't complete.

    So I tried the code, but I'm having some issues.
    I opened the form in design view. Then "Database tools" ribbon. Then I opened "visual basic".

    Couple of questions. What is the ".me"? I was confused on that when I saw it in multiple threads.

    Additionally, do I type the code that provided in the same area? Or do I use either or?

    The "attempted" and "wrong format" field are both set to "True/False" field properties in the actual table. That is the correct format, yes?


    I got it to work. However, I have an issue.

    When I open the form form, everything is good. The "sent back" is not visible.
    Once I click "incomplete", the sent back field shows up.

    However, if I try to un-check the incomplete (say it was clicked accidently), i can't un-click it and the sent backfield becomes "12:00:00AM" and I can't change it.
    Any idea what to do here?

    I also attempted to modify the code to if either or incomplete or wrong formatted is checked, then make the gray box content appear/disappear but I haven't gotten it to work.
    I did this by copying by doubling the IF/ELSE statement. Ones for the Incomplete and ones for the wrong formats.
    I'm assuming there's a way to use an "OR" instead of using more IF statements? Any help is greatly appreciated! Thanks again
    Last edited by AccessN00b; 12-09-12 at 21:33.

Posting Permissions

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