Display a memo field only if checkbox is "yes"

[bellemmar]

I have a yes/no field called Action Required. I want the following field (a memo box called Action Details) to only show if the checkbox is set to yes (IE: you can't access the memo box at all if the checkbox is not checked). Is this possible?

 

[boblarson]

A few points for you.

1. Fields exist in tables and queries. Controls exist on forms and reports and can be BOUND to a field.

2. To have your TEXT BOX CONTROL, which is bound to the memo field, display when the checkbox is checked - add a function to your form's module:

Function ShowMemo()
   Me.MemoControlNameHere.Visible = Me.CheckBoxNameHere
End Function

And then call that in both the checkbox's AFTER UPDATE event AND in the Form's ON CURRENT event.

Do you want to clear the memo data if it is showing and has data and then the checkbox is unchecked?

 

[bellemmar]

Thank you so much for the reply.

I'm hung up on "add a function to your form's module" but I understand the part underneath the code you provided.

Yes, I would like to clear the memo data if the box becomes unchecked.

 

[boblarson]

Thank you so much for the reply.

I'm hung up on "add a function to your form's module"

Just copy the whole code I posted into your form's module (when you open the form in design view you should be able to go to VIEW CODE.

And paste it there.

change the names to your actual text box that has the memo field bound to it and change the checkbox name to your checkbox name. To clear the memo, I would change the function I gave you to this:

Private Function ShowMemo()
   If Me.CheckBoxNameHere Then
      Me.MemoControlNameHere.Visible = True
   Else
      Me.MemoControlNameHere.Visible = False
      Me.MemoControlNameHere = Null
   End If
End Function

Oh, and you would only need to use

ShowMemo

in the events (in VBA Window) After Update (for the checkbox) and On Current (for the form).

 

[bellemmar]

I must be doing something wrong. I've attached a screenshot here. The top view is the code on the form and the bottom view is the code on the check box. It still won't work.

My checkbox field is: External Environment
My memo box field is: External Environment Memo1

I'm sure it's going to be something silly that's hung me up

 

[boblarson]

You haven't typed the word

ShowMemo

into the After Update event for External Environment Checkbox like:

Private Sub External_Environment_AfterUpdate()
   ShowMemo
End Sub
 
Private Sub Form_Current()
   ShowMemo
End Sub

 

[bellemmar]

YAY thank you.

 

[bellemmar]

If I had other fields specific to External Environment that I also wanted to appear with the checkbox, can I just add the coding into the module in the same fashion?

IE: could clicking on the checkbox show/hide several different fields or just one?

 

[boblarson]

yes, you can add whatever to the function as needed.

 

[hardhitter06]

How would this code change if instead of the checkbox, i used a Yes/No field with the Yes/No options...And If Yes, I'd want these 3 additional fields to show?

 

[gemma-the-husky]

i am sure bob will answer, but basically, test the combo box, depending on the values it can take.

if mycombo = "whatever" then etc.

---
I just wanted to point out an alternative. instead of hiding controls (visible = false) you can deactivate them (grey them out) by setting 2 properties

enabled= no
locked = no

these two taken together offer 4 alternatives

normal field for data entry
enabled= yes
locked = no

field locked, but can be entered, eg for searching
enabled= yes
locked = yes

field locked, and cannot be entered
enabled= no
locked = yes

field locked, and cannot be entered - but also greyed out (like greyed menu items)
enabled= no
locked = no

 

[hardhitter06]

Thanks Gemma, I'd like to hide them if No is selected since they are not needed or even need to be looked at.

I can't figure this out tho so if Bob could weigh in that'd be great.

 

[hardhitter06]

I have:

Private Function ShowFields()
      If Me.OtherUnivEmployeesInvolved Then
      Me.OtherUnivEmployeeFullName1.Visible = True
  Else
      Me.OtherUnivEmployeeFullName1.Visible = False
      Me.OtherUnivEmployeeFullName1 = Null

    End If
End Function

"OtherUnivEmployeesInvolved" is the Yes/No field
"OtherUnivEmployeeFullName1" is the field I want to hide if "No" is selected. (side question - when I start to fill in the first field of the record, my Yes/No boxes flood to "No"s which I guess works in this senario but i find it odd)

Then I also have:

Private Sub Form_Current()
ShowFields
End Sub

Private Sub OtherUnivEmployeesInvolved_AfterUpdate()
ShowFields
End Sub

The additional fields I want to hide (although I only listed one) are all text fields.

 

[gemma-the-husky]

hard hitter

the code looks correct - but true and false are values for a checkbox - not necessarily for a combo box. if you have created the comob box with text values "yes" AND "no" then you will need to test for "yes" and "no"

i presume your y/n boxes are bound to fields, if so, when you edit a new record, no doubt they are all getting initiallised to a default value of false. - hence No.

 

[hardhitter06]

When looking at the Data Type in my table, there is no "CheckBox" option, only Yes/No field so i selected that.

And yes, they are bound to fields. Could you help me with that Yes/No validation?

About the new record thing, is there a way to keep those Yes/No boxes blank until the user gets to them? Although if I get the first thing to work, the default to No's might be okay so the first thing is more important.

Thanks for your info so far...

 

[boblarson]

Sorry, I have been unable to visit the forum recently. I am kind of confused as to what you actually have and it would probably work best if you can post a copy of the database with fake data. I think we could clear this up quite quickly if we could see what you have.

 

[hardhitter06]

Hi Bob thanks for the response:

All I'm focusing on right now is the frmSunlightInput form.

1. My Yes/No boxes default to No when I open this form (is that normal)?

2. For the question "Were other university employees involved?", if the user selects "Yes" - the (3) text boxes will open for the name fields 1, 2 and 3. If the user selects "No", I want to hide the "If Yes - Provide Names" along with the 3 text boxes and the 3 "Full Name" Labels.

3. For the question "Did outside Entity Represent the Vendor?", if the user selects "Yes" - I want all of the remaining fields to show below. If the user selects "No", I want to hide everything under the question. If you can show me how to do #2, I could probably do this myself.

4. Lastly, any fields showing I want them to be required other than Full Name 2 and Full Name 3. I have 2 and 3 being skipped in the validation currently.

Sorry if this is a lot but i appreciate your help,

Josh

 

[boblarson]

Here you go. Take a close look at the function, the after update procedures of both combo's and the Current event of the form. You don't need the code in the form's AFTER UPDATE event so I deleted that.

 

[hardhitter06]

Bob, that is great! exactly what i wanted.

Now I just have to work on what is required because even with No selected my Save feature is still prompting me to fill in those fields.

For the first Yes/No section, if Yes is selected, only "OtherUnivEmployeeFullName1" is required.

For the second Yes/No section, if Yes is selected they are all required.

Now for the first section, what I normally would do is create some sort of code like this

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If (Me.OtherUnivEmployeesInvolved) = True And IsNull(Me.frmSunlightInput.Form.OtherUnivEmployeeFullName1) Then
  MsgBox Me.OtherUnivEmployeeFullName1 & " Cannot Be Left Empty!"
  Cancel = True
  End If
 End Sub

I don't want the other two to ever be validated (FullName2, FullName3) and I would usually use "skip" in the control with my current validation (see below) but you are using that field to show/hide fields.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim ctrl As Control

    For Each ctrl In Me.Controls
        If ctrl.Tag <> "skip" Then
            If ctrl.ControlType = acTextBox Then
                If IsNull(ctrl) Then
                    MsgBox ctrl.Name & " Cannot Be Left Empty!"
                    Cancel = True
                    ctrl.SetFocus
                    Exit Sub
                End If
            End If
        End If
    Next
    MsgBox "Record Saved!"
End Sub

And then for the second Yes/No, I would just use the same code from above and keep doing If statements for each field.

Am I on the right track?

And like i mentioned before I don't know how to skip those 2 fields. Or skip any of them if No is selected for that matter.

Thanks

 

[boblarson]

Different problems really should have their own thread.