Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2017
    Posts
    2

    Unanswered: Make a field visible based on another field

    Hello. I'm working in Microsoft Access 2010. I have a form with a field called On Time/Late. This field is auto calculated based on two dates that the user enters.
    If the Date Completed field > the Date Needed By field, the On Time/Late field shows "Late". Otherwise it will show "On Time". When the form is first opened, the On Time/Late field is blank. I have a Late Comment field that the user will fill out if the On Time/Late field shows "Late".

    I need to do three things:
    1. When the form first opens, I want the Late Comment field to be invisible.
    2. If the On Time/Late field is updated and shows "Late" (after the date fields have done their thing), then the Late Comment field will become visible so the user can provide a comment.
    3. If the Late Comment field is activated and becomes visible, I need it to be a mandatory field so the user can't save the record unless they enter a comment.

    I've done some research and have come up with the following code that partially works for #1 and #2. However, the Late Comment field only shows up after the record is saved. Which defeats the purpose of the user having to enter a comment if the On Time/Late field shows "Late". I'm not sure what I've done wrong.

    I don't know how to address #3.

    I set the Late Comment field's Visible property under Format is "Yes"

    Private Sub Form_Current()
    If Me.On_Time_Late = "Late" Then
    Me.Late_Comment.Visible = True
    Else
    Me.Late_Comment.Visible = False
    End If
    End Sub


    Private Sub On_Time_Late_AfterUpdate()
    If Me.On_Time_Late = "Late" Then
    Me.Late_Comment.Visible = True
    Me.Late_Comment.Enabled = True
    Else
    Me.Late_Comment.Visible = False
    End If
    End Sub



    Thank you in advance!
    Tina
    Last edited by tina1029; 11-09-17 at 12:52.

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    The form's Current event fires when you move to a record. I don't think that the AfterUpdate event fires at all if you update a control using VBA, although I could be wrong.

    I would bundle the process of comparing the dates and showing the reason control into the completion date control's AfterUpdate event:
    Code:
    If Date_Completed > Date_Needed_By Then
         On_Time_Late = "Late"
         Late_Comment.Enabled = True
         Late_Comment.Visible = True
    Else
         On_Time_Late = "On Time"
         Late_Comment.Enabled = False
         Late_Comment.Visible = False
    End If
    Then use the form's BeforeInsert event to make the field mandatory:
    Code:
    If On_Time_Late = "Late" And Late_Comment & "" = "" Then
         MsgBox "You must explain why this was late!", vbCritical, "Incomplete Record"
         Late_Comment.SetFocus
         Cancel = True
    End If
    HTH!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Nov 2017
    Posts
    2

    Make a field visible based on another field

    Weejas-
    Thank you for the response. The first part of the code works. I put the code in the Completion Date's After Update event and it works as expected. The On Time/Late field updates to "On Time" or "Late" as it should. And if it's "Late", then the Late Comment box appears. This is perfect!

    However, I noticed that the Late Comment box is now visible for all the records regardless if the On Time/Late field says "Late" or "On Time". It's also visible for any new records I create within the form even before I begin entering data. I tried re-setting the Late Comment field's Visible property to No but it changes it back to Yes. I would like for the Late Comment field to be visible only if the On Time/Late field = "Late". Is this possible?

    Also, regarding making the Late Comment field a mandatory field if it = "Late" is not working. I'm able to save with or without a comment and I never see the error message pop up if I don't enter a comment. Please advise. Thank you very much for all your help so far!

  4. #4
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,102
    Provided Answers: 17
    Sounds like you need to try stepping through the code, and seeing what it's doing at each stage. Set a break point at the start of the AfterUpdate event by clicking in the grey margin of the VBA editor, then try editing the value in the control and use F8 to execute a line at a time.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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