Set Visibility of Text Box based on Combo Box Selection

[Jo22]

Hello,

I have a combo box on a subform called cmbStyles which has four choices in the list: Regular, Angle, Arched, and Skylite. The default value is set to Regular. I also have six text boxes for measurements. The first two are wdth and lngth. The other four are called SizeA, SizeB, SizeC, and SizeD. I would like the last four to remain hidden unless "Angle" is selected in the combo box.

Questions:

Should I use the txtbox.visible = true/false, or should I use txtbox.columnHidden = true/false? What is the proper syntax for the If part of the statement?

I have tried the following:

I have tried using the OnLoad event function to set text boxes SizeA,B,C,D ColumnHidden to true and then used the AfterUpdate event in cmbStyles to set the text boxes columnHidden to False if Angle is selected and to true if Regular is selected.



The problem is that it works the first time I select Angle in the cmbStyles, but if I change the selection back to Regular or if I move to a new record these extra text boxes do not hide.

I hope this makes sense to someone. If not please let me know what information you need to assess the situation.

[Robeen]

Could you post the code you are using in your AfterUpdate event so we can see why it is not working for the second selection?

[Jo22]

I will start with the combo box first as I believe there is a problem here. I used the lookup wizard in the table to set up the combo box [cmbStyles]. I entered the values as indicated. When I return to the form and set up an unbound text box to show the value of the item selected, it is showing the list item previous, not the one I selected. For example, when I select Regular from the list, the unbound text box shows nothing. When I select Angle, the box shows Regular.

In the afterUpdate event, I use " If Me!Style.Text = "Angle" Then
SizeA.columHidden = False "

In the form onLoad event, I am using " Me!SizeA.Visible = False "

I only need these extra sizeA,sizeB, etc. columns to show if the style is Angle.

Please ignore the line in my previous post where I stated that it works the first time. I started over with a new combo box and the notes in this post is where I am right now.

[ssanfu]

I will start with the combo box first as I believe there is a problem here. I used the lookup wizard in the table to set up the combo box [cmbStyles]. I entered the values as indicated. When I return to the form and set up an unbound text box to show the value of the item selected, it is showing the list item previous, not the one I selected. For example, when I select Regular from the list, the unbound text box shows nothing. When I select Angle, the box shows Regular.

In the afterUpdate event, I use " If Me!Style.Text = "Angle" Then
SizeA.columHidden = False "

In the form onLoad event, I am using " Me!SizeA.Visible = False "

I only need these extra sizeA,sizeB, etc. columns to show if the style is Angle.

Please ignore the line in my previous post where I stated that it works the first time. I started over with a new combo box and the notes in this post is where I am right now.

Wrong: Me!Style.Text = "Angle"
Right: Me!cmbStyles = "Angle"

You shouldn't use the .TEXT property. That is where data is stored for the control before it is committed. The Value property will not be the same as the TEXT property until it is saved (committed).

This is the way I set it up:
In form design view, set the visible property of each of the text boxes SizeA, SizeB, SizeC & SizeD to NO.

Combo box: cmbStyles
Row source type - Value list
Row source - "Regular";"Angle";"Arched";"Skylite"
Bound column - 1
Limit to list - YES

In the After Update event of cmbStyles:

Private Sub cmbStyles_AfterUpdate()
   Me.SizeA.Visible = Nz(Me.cmbStyles, "") = "Angle"
   Me.SizeB.Visible = Nz(Me.cmbStyles, "") = "Angle"
   Me.SizeC.Visible = Nz(Me.cmbStyles, "") = "Angle"
   Me.SizeD.Visible = Nz(Me.cmbStyles, "") = "Angle"
End Sub

When you change to a different record, you need to set the visible property for each of the SIZE text boxes. To do that, use the form Current Event (not the load event).
In the FORM Current Event:

Private Sub Form_Current()
   Me.SizeA.Visible = Nz(Me.cmbStyles, "") = "Angle"
   Me.SizeB.Visible = Nz(Me.cmbStyles, "") = "Angle"
   Me.SizeC.Visible = Nz(Me.cmbStyles, "") = "Angle"
   Me.SizeD.Visible = Nz(Me.cmbStyles, "") = "Angle"
End Sub

[Jo22]

Thanks Ssanfu. Your explanation made a lot of sense, however, I tried the code and got a type mismatch error '13'. This form is actually a subform [Quote Query] of the main form [Quote Details].

Thinking that this error may be because I didn't reference the main form, I went back to the code and replaced all the Me. with Forms![Quote Details]![Quote Query]![cmbStyles]!

Unfortunately I still get the error. Can you explain why I am getting this mismatch error?

[ssanfu]

Thanks Ssanfu. Your explanation made a lot of sense, however, I tried the code and got a type mismatch error '13'. This form is actually a subform [Quote Query] of the main form [Quote Details].

Thinking that this error may be because I didn't reference the main form, I went back to the code and replaced all the Me. with Forms![Quote Details]![Quote Query]![cmbStyles]!

Unfortunately I still get the error. Can you explain why I am getting this mismatch error?

There is a particular way to address sub-form from a main form. See:

http://access.mvps.org/access/forms/frm0031.htm

[Jo22]

Thanks Ssanfu for the link. My subform [Quote Query] is on a tab control [QuoteTab] on the main form [Quote Details].

Since I am working on the subform and am referencing a control on the same subform, the code should be fine. I cannot understand why I am still getting the error '13' type mismatch error on runtime. I read some information on this error which talks about DAO and ADO. This is very complicated for me and I am not sure if this has anything to do with my situation.

[Jo22]

Ssanfu - all your advice with respect to the set up of this form were right on the mark, except for the code itself. I still do not understand why I got a runtime mismatch error when using your code. I only know that it accepted the following, which I entered in the [cmbStyle] update event and the On Current event of the subform [Quote Query]:

If Me.Specialty.Column(1) = "Angle" Then
Me.SizeA.ColumnHidden = False
Me.SizeB.ColumnHidden = False
Me.SizeC.ColumnHidden = False
Me.SizeD.ColumnHidden = False
Else
Me.SizeA.ColumnHidden = True
Me.SizeB.ColumnHidden = True
Me.SizeC.ColumnHidden = True
Me.SizeD.ColumnHidden = True

End If

Once again, thank you Ssanfu as the advice you provided was very helpful. If anyone cares to elaborate on the reason for the original mismatch error I would be interested in knowing.

[ssanfu]

Ssanfu - all your advice with respect to the set up of this form were right on the mark, except for the code itself. I still do not understand why I got a runtime mismatch error when using your code. I only know that it accepted the following, which I entered in the [cmbStyle] update event and the On Current event of the subform [Quote Query]:

If Me.Specialty.Column(1) = "Angle" Then
Me.SizeA.ColumnHidden = False
Me.SizeB.ColumnHidden = False
Me.SizeC.ColumnHidden = False
Me.SizeD.ColumnHidden = False
Else
Me.SizeA.ColumnHidden = True
Me.SizeB.ColumnHidden = True
Me.SizeC.ColumnHidden = True
Me.SizeD.ColumnHidden = True

End If

Once again, thank you Ssanfu as the advice you provided was very helpful. If anyone cares to elaborate on the reason for the original mismatch error I would be interested in knowing.

I don't have A2K7, so testing the code is a problem. Also, when I created a table, form and controls, I didn't know what the field types were or how you set up the form. Having a sub-form makes a difference. the columnHidden property is not a property I see for a text box in the property dialog box.

I just looked at Help and found that the ColumnHidden property is not available in design view.

From Help:
"You can use the ColumnHidden property to show or hide a specified column in Datasheet view. For example, you might want to hide a CustomerAddress field that's too wide so you can view the CustomerName and PhoneNumber fields. Note The ColumnHidden property applies to all fields in Datasheet view and to form controls when the form is in Datasheet view."

My apologies. I missed the "a subform called cmbStyles" part. I mistook "cmb" to be prefix for "combo box". I need to read slower....
Glad you got it working..

Since the sub-form is in datasheet view, the '.ColumnHidden' property is the correct property, not the '.Visible' property. And that is why there was an "error '13' type mismatch".