Making a text box visible conditionally based on a combobox selection

Question

_{Wednesday, May 9, 2012 6:07 PM}

I've used this technique successfully in other places, but here I can't make it work. There's one difference that I'm aware of: in other places, I've made one text box visible if another text box has a value entered. Here, I'm trying to make a text box visible if a specific value is chosen from a combo box. (If the user seelcts "other" I want a text field to open up for them to type in an explanation of "other")

the combobox draws its values from this table:

Goals

The code I've used is below. It's identical (as nearly as I can tell) to code used elsewhere that works, with 2 exceptions: (a) it identifies a specific coulumn of a cbo, rather than simply looking for Not IsNull in a text box, and (b) it identifies the value to look for.

Private Sub Form_Current()
      
      Me.GoalDetail.Visible = Not IsNull(Me.cboGoal.Value)

End Sub
______________________________________________________
Private Sub cboGoal_AfterUpdate()

    If Me.cboGoal.Column(0) = 5 Then
        Me.GoalDetail.Visible = True
    Else
        If Me.cboGoal.Column(0) <> 5 Then
            Me.GoalDetail.Visible = False
        End If
    End If
        
End Sub

What happens is that the control GoalDetail is not visible by default. When I browse to a student where "other" is chosen, it it still not visible. If I reselect "other," then GoalDetail becomes visible (as desired). However, if I then browse to another student where a different goal is selected, the text box is still visible, and remains so till I close the form. When I reopen the form, the text box is again not visible, including in those records where "other" is selected.

BTW, I tried changing the Current() to " = Not IsNull(Me.cboGoal.Column(0))" or to an IF statement where Me.cboGoal.Column(0) <> 5. Either of these statements produced the same result.

Can anyone see what it is I'm doing wrong here?  Thanks!

—nick

All replies (23)

_{Wednesday, May 9, 2012 10:12 PM ✅Answered}

So, I'm not sure if there was any real problem with the code I started with or not. My system began acting wierder and wierder this afternoon, Then I had a computer crash, and after I got running again, had to decompile and re-compile my DB. In between I stripped out the code I'd written and edited and re-edited, and wrote new code by hand. This is what it looks like:

Private Sub Form_Current()
    If Me.cboGoal.Column(0) = 5 Then
        Me.GoalDetail.Visible = True
    Else
        Me.GoalDetail.Visible = False
    End If
    
End Sub
__________________________________

Private Sub cboGoal_AfterUpdate()

    If Me.cboGoal.Column(0) = 5 Then
        Me.GoalDetail.Visible = True
    Else
        If Me.cboGoal.Column(0) <> 5 Then
            Me.GoalDetail.Visible = False
            Me.GoalDetail = Null
        End If
    End If
    
End Sub

And now it works fine. I'm so sorry to have taken up your time, Chris and Imb. But for whatever reason, it now works. Thank you once again.

—nick

_{Wednesday, May 9, 2012 6:19 PM | 1 vote}

Hi Nick,

Seems like the codes are fighting here. You have 1 code saying if it is not Null then make it visible where the other code says only make it visible when this value is true.

Chris Ward

_{Wednesday, May 9, 2012 6:21 PM}

Maybe this will work?

Private Sub Form_Current()            Me.GoalDetail.Visible = Me.cboGoal.Column(0) <> 5End Sub

Chris Ward

_{Wednesday, May 9, 2012 6:22 PM | 1 vote}

You can add a refresh to the AfterUpdate Event if needed

Ahem, I think your might have to in Design View set the Control's Property / Format / Visible to No first.

Chris Ward

_{Wednesday, May 9, 2012 6:57 PM | 1 vote}

The code I've used is below. It's identical (as nearly as I can tell) to code used elsewhere that works, with 2 exceptions: (a) it identifies a specific coulumn of a cbo, rather than simply looking for Not IsNull in a text box, and (b) it identifies the value to look for.

Private Sub Form_Current()            Me.GoalDetail.Visible = Not IsNull(Me.cboGoal.Value)End Sub______________________________________________________Private Sub cboGoal_AfterUpdate()    If Me.cboGoal.Column(0) = 5 Then        Me.GoalDetail.Visible = True    Else        If Me.cboGoal.Column(0) <> 5 Then            Me.GoalDetail.Visible = False        End If    End If        End Sub

What happens is that the control GoalDetail is not visible by default. When I browse to a student where "other" is chosen, it it still not visible. If I reselect "other," then GoalDetail becomes visible (as desired). However, if I then browse to another student where a different goal is selected, the text box is still visible, and remains so till I close the form. When I reopen the form, the text box is again not visible, including in those records where "other" is selected.

Hi Nick,

If I understand it right, you want the GoalDetail control visible, whenever cboGoal.Column(0) = 5.

In that case you could try:

Private Sub Form_Current()            Me.GoalDetail.Visible = Me.cboGoal.Column(0) = 5End Sub______________________________________________________Private Sub cboGoal_AfterUpdate()      Me.GoalDetail.Visible = Me.cboGoal.Column(0) = 5End Sub

Imb

_{Wednesday, May 9, 2012 7:01 PM}

Hi, Chris

Yes, I do have the Control's Property / Format / Visible set to No.

I believe I did try Me.GoalDetail.Visible = Me.cboGoal.Column(0) <> 5 as my first attempt. But perhaps I had a typo in it, I'll try it again and let you know.

Thanks!

—nick

_{Wednesday, May 9, 2012 7:04 PM}

Hi, Imb

As with Chris's suggestion, I think I tried this. But again. it's possible I had a typo or similar error. I will methodically retry each option.

Thanks!

—nick

PS: Oh!  I just realized that when I did the code as you suggest, I had "Me.cboGoal.Column(0) = 5"  in parentheses, because I thought I'd get in trouble with two equals in the same line. I should have left the parens out?

_{Wednesday, May 9, 2012 7:08 PM}

Also you might have to go with Me! instead of Me.

Chris Ward

_{Wednesday, May 9, 2012 7:25 PM}

Chris, and Imb

So, no. Both these suggestions— which make perfect sense to me— produce the same result: the text box is invisible until I select "other" on some record; then the text box appears on all records regardless what is selected. The reverse is true also: if I then select something other than "other" on any record, the text box disappears on all records. I also tried Chris's suggestion of adding a refresh to the Form_Current(). But still no go.

It seems to me there must be something obvious elsewhere that's gumming it up, but I just can't see it.

Thanks!

—nick

(I also tried "Me!" instead of "Me." still no go.)

_{Wednesday, May 9, 2012 7:42 PM}

Chris, and Imb

So, no. Both these suggestions— which make perfect sense to me— produce the same result: the text box is invisible until I select "other" on some record; then the text box appears on all records regardless what is selected. The reverse is true also: if I then select something other than "other" on any record, the text box disappears on all records. I also tried Chris's suggestion of adding a refresh to the Form_Current(). But still no go.

It seems to me there must be something obvious elsewhere that's gumming it up, but I just can't see it.

Thanks!

—nick

(I also tried "Me!" instead of "Me." still no go.)

Hi Nick,

I see what happens. cboGoal is a control on the form, and is independant of any record.
What you need is per record a field with the chosen value from cboGoal, e.g. StudentGoal.

StudentGoal is filled with the selection form cboGoal, and the visibility of GoalDetail is controlled by StudentGoal.

Imb.

_{Wednesday, May 9, 2012 7:49 PM}

So the fact that cboGoal is bound to the ServicePLans table (field: GoalID) makes no difference?

Should the reference look like this: ServicePlans.GoalID?

—nick

_{Wednesday, May 9, 2012 7:56 PM}

Is your Form a single record form or datasheet view or split form?

Chris Ward

_{Wednesday, May 9, 2012 7:57 PM}

Single record

—nick

_{Wednesday, May 9, 2012 8:09 PM}

Well then maybe

Private Sub Form_Current()      Me.GoalDetail.Visible = Me.GoalDetail = 5End Sub

Private Sub cboGoal_AfterUpdate()      Me.GoalDetail.Visible = Me.GoalDetail = 5End Sub

Chris Ward

_{Wednesday, May 9, 2012 8:17 PM}

So the fact that cboGoal is bound to the ServicePLans table (field: GoalID) makes no difference?

Should the reference look like this: ServicePlans.GoalID?

—nick

Hi Nick,

I do not know all the relations in your database. But what I suppose is that the Goal must have a relation to the individual records in the form.

What is the RecordSource of the form?

Imb.

_{Wednesday, May 9, 2012 8:37 PM}

Hi, Imb

I do not know all the relations in your database. But what I suppose is that the Goal must have a relation to the individual records in the form.

Yes, it's related through GoalId

What is the RecordSource of the form?

It's a subform. Its RecordSourse is the Table ServicePlans.

I can't figure out how to reference the field in that table. On another forum I found a post saying I should use this format:

            GoalID.Fields("ServicePlans")

but my VBA editor would not accept this.

I'm also having a hard time understanding how, if I go straight to the table, the system will know which record to look at. Do I need an additional line of code to identify the record? The identifier would be PlanID, which is in the form, bound to a field by the same name in the table.

—nick

_{Wednesday, May 9, 2012 9:25 PM}

It's a subform. Its RecordSourse is the Table ServicePlans.

I can't figure out how to reference the field in that table. On another forum I found a post saying I should use this format:

            GoalID.Fields("ServicePlans")

but my VBA editor would not accept this.

I'm also having a hard time understanding how, if I go straight to the table, the system will know which record to look at. Do I need an additional line of code to identify the record? The identifier would be PlanID, which is in the form, bound to a field by the same name in the table.

Hi Nick,

So you have a subform with RecordSource = "ServicePlans".

Is there a field in ServicePlans that has information about the Goal, such as GoalID?

If not, how is the relation to Goals?

Is GoalID a visible control on the subform?

If yes, use that control to regulate the visibility of GoalDetail.

In no, make an invisible control GoalId on the subform and use that control.

Or have I a complete wrong view on your system?

About "to go straight to the table": you should not want to do that. It shortcuts all your relations, disturbs the referential integrity and violates all rules that are build in before_update and after_update events.

Imb. 

_{Wednesday, May 9, 2012 9:31 PM}

Hi Nick,

cboGoal and GoalDetail are both on the same subform?

Chris Ward

_{Wednesday, May 9, 2012 9:51 PM}

If you are trying to have a field in a subform change the format of a control on the parent form I think you have to return the focus to the Parent and then refresh.

Chris Ward

_{Wednesday, May 9, 2012 9:59 PM}

Hi Chris — Yes, they are

I got it working—  seperate comment below

Thanks so much for your time and help

—nick

_{Wednesday, May 9, 2012 10:01 PM}

Oh, Okay— I misunderstood. I htought you were telling me to go straight to the table, which was confusing me.

I got it working—  seperate comment below

Thanks so much for your time and help

—nick

_{Thursday, May 10, 2012 1:36 PM}

Good Morning Nick,

I am happy you have your answer. By the way you should mark YOUR post as answer since you answered it and it is your answer.

Just a note: the diff between the codes are in the OnCurrent

Me.GoalDetail.Visible = Not IsNull(Me.cboGoal.Value)

If Me.cboGoal.Column(0) = 5 Then Me.GoalDetail.Visible = True
Else Me.GoalDetail.Visible = False

The two paths of evaluation are using the same method now. I don't know if that is a difference maker but maybe that is why it works now compared to your original setup. Makes me wonder if you could just have the code in the OnCurrent and have a Me.Refresh in the AfterUpdate of the cboGoal control vs. having the code in multiple places.

Chris Ward

_{Thursday, May 10, 2012 9:42 PM}

Hi Chris, and thanks.

Yes I think the difference in "paths of evaluation," as you so perfectly describe it, is an issue. At the very least, doing it in this parallel manner helped me to see what I was doing more clearly and avoid mistakes. The original code was adapted from something else that worked elsewhere, and that in turn was adapted from something I either found on line, or somebody here steered me to—I can't even remember now. Too many transmutations.

Thanks again for your help!

—nick