Clearing text boxes based on combo selections

[Bellyjeans]

Hi all,

I have a situation that I'm sure is easy to fix, but I'm having a
moment and my brain won't work. I have a combo box. I have the
following code on the after update event of the combo box:

If Forms!frmStatus.cboStatusName = "Selection 1" Then
Me.txtBox1.Visible = True
Me.txtBox 2.Visible = False
Else
Me.txtBox1.Visible = False
Me.txtBox2.Visible = True
End If

If Forms!frmStatus.cboStatusName = "Selection 2" Then
Me.txtBox3.Visible = True
Me.txtBox4.Visible = True
Me.txtBox2.Visible = False
Else
Me.txtBox3.Visible = False
Me.txtBox4.Visible = False
Me.txtBox2.Visible = True
End If

If Forms!frmStatus.cboStatusName = "Selection 3" Then
Me.txtBox5.Visible = True
ElseIf Forms!frmStatus.StatusName = "Selection 4" Then
Me.txtBox5.Visible = True
Else
Me.txtBox5.Visible = False
End If

If Forms!frmStatus.cboStatusName = "Selection5" Then
Me.cboBox6.Visible = True
Else
Me.cboBox6.Visible = False
End If

Basically, in a nutshell, the coding makes a number of fields visible
or invisible, based on what is selected in the combo box
"cboStatusName". My problem is that if somebody changes the selection
in cboStatusName, the value in certain combo boxes remains there, even
if that field is made invisible. How do I make it so that if the
value in cboStatusName is changed, the fields that become invisible
also clear their values?

Thanks!

 

[Jeff Boyce]

One approach would be to set the value of the textbox to Null, something
like (untested):

Me!txtBox1 = Null

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Set the textbox value to Null

If Forms!frmStatus.cboStatusName = "Selection 1" Then
Me.txtBox1.Visible = True
Me.txtBox 2.Visible = False
Me.txtBox 2 = Null
Else
Me.txtBox1.Visible = False
Me.txtBox1 = Null
Me.txtBox2.Visible = True
End If

Or you could just cycle through all the controls and if the visible property
is false then set them to null

The easy way to do that is to assign the relevant controls a tag property like
"HiddenToNull"

Then add this to the bottom of your current procedure
Dim ctl as Control
For each ctl in Me.Controls
If ctl.Tag = "HiddenToNull" THEN
IF ctl.Visible = False THEN
ctl.value = null
End If
End If
Next ctl

The advantage of doing it with the loop is that if you edit the controls you
are showing/hiding based on the combobox, the loop should automatically take
care of the changes. Some disadvantages:
== you must remember to tag any controls that you want to get this behavior
== the looping process is less efficient than specifically coding each item
(although with most computers is will not be noticeable to the human.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Bellyjeans]

Awesome, the loop seems to be working. Thanks!