Subform Combo set controls visible and invisible?

[misscrf]

On a subform I have a combo (cboType)

Based on the selection of that combo, I have certain controls on the form that should be visible and the rest false. I created a Case Select statement on the after update of that combo, to set the subform controls visible and invisible.

A couple of questions I have -

1) besides for after update of the combo, where else should I put this, to make sure that the form is always looking appropriately?

2) how do I handle null, i.e there is no record yet (so only the cboType combo should be visible)

3) How do ensure that during main form load, and navigation, this is form's controls visible status is updated?

4) While separate, and more of a layout thing, one of my choices only uses 1 date field and the comments field, while the others have a bunch of fields in use. How do I set the layout, so that when it is just the date field and comments field it doesn't have a huge space in the form (reserved for the invisible fields).


This is my current case select code, so you can see it:

Select Case Me.cboType

Case 1

Me.cboSubType.Visible = True
Me.dtDate1.Visible = True
Me.txtComments.Visible = True
Me.cboPerson1.Visible = False
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = False
Me.dtDate3.Visible = False
Me.txtNameSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1Type.Visible = False
Me.cboPerson2Type.Visible = False
Me.cboPerson3Type.Visible = False

Case 2
Me.cboSubType.Visible = True
Me.dtDate1.Visible = False
Me.txtComments.Visible = True
Me.cboPerson1.Visible = True
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = True
Me.dtDate3.Visible = False
Me.txtNameSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1Type.Visible = True
Me.cboPerson2Type.Visible = False
Me.cboPerson3Type.Visible = False

Case 3
Me.cboSubType.Visible = False
Me.dtDate1.Visible = False
Me.txtComments.Visible = True
Me.cboPerson1.Visible = False
Me.cboPerson2.Visible = True
Me.cboPerson3.Visible = True
Me.dtDate2.Visible = False
Me.dtDate3.Visible = True
Me.txtNameSpec.Visible = True
Me.dtDate4.Visible = True
Me.cboPerson1Type.Visible = False
Me.cboPerson2Type.Visible = True
Me.cboPerson3Type.Visible = True

Case 4
Me.cboSubType.Visible = True
Me.dtDate1.Visible = False
Me.txtComments.Visible = True
Me.cboPerson1.Visible = True
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = True
Me.dtDate3.Visible = False
Me.txtNameSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1Type.Visible = True
Me.cboPerson2Type.Visible = False
Me.cboPerson3Type.Visible = False

Case 5
Me.cboSubType.Visible = True
Me.dtDate1.Visible = False
Me.txtComments.Visible = True
Me.cboPerson1.Visible = True
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = True
Me.dtDate3.Visible = False
Me.txtNameSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1Type.Visible = True
Me.cboPerson2Type.Visible = False
Me.cboPerson3Type.Visible = False

End Select

Thanks for any help!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

1) & 3) the Current event procedure of the subform.

2) Test the Me.NewRecord property in the Current event procedure of the subform.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Your code to change the properties should be in its own function like:

Function ShowHide()
    Select Case Me.cboType
        Case 1

You can call this function from the after update of cboType and also in the On Current of the form containing cboType.

You can add some code like:

    If IsNull(Me.cboType) Then

I would probably write all of this a bit differently since I don't care for lots of repeating code blocks.

You could use the tag property of each of the controls to enter the cboType values that should make the control visible. For instance the tag property of cboSubType would contain 1245. dtDate1's tag would be just 1. Then your code might look like:

Function ShowHide()
    Dim ctl as Control
    Dim intType as Integer
    intType = Nz(Me.cboType,0)
    For Each ctl in Me.Controls
        If Len(ctl.Tag & "") > 0 Then
            ctl.Visible = Instr(ctl.Tag,intType)> 0
        End If
    Next
End Function

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks for the responses. I like the idea of putting it in a function. I also like the idea of your function using tags, but I have never done that before.

Here are my follow-up questions, as I am considering going this path.
1) I will go to each control and set it's tag property to a value. Yes?

2) Does it matter what value each control is?

3) It looks like you are saying if the combo has no value, then all controls with a tag > 0 are not visible. Is that true? (ctl.Visible = Instr(ctl.Tag,intType)> 0)

4) After that IF statement, am I adding more else statements for the len of the controls that should be visible and should not? Per Q 3, is this -

ctl.Visible = Instr(ctl.Tag,intType)> 0

the controls are not visible? if so, what would make them visible?

ctl.Visible = Instr(ctl.Tag,intType)[COLOR=red]< [/color] 0

That?

3) How do I call this function from an event, like after update, form load? Sorry for the stupid question, but I don't know that I have made many functions and then called them, so I am not even sure how to look up an example of this.

Thanks!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Answers

1) I will go to each control and set it's tag property to a value. Yes?
[blue]Yes[/blue]

2) Does it matter what value each control is?
[blue]No[/blue]

3) It looks like you are saying if the combo has no value, then all controls with a tag > 0 are not visible. Is that true? (ctl.Visible = Instr(ctl.Tag,intType)> 0)
[blue]If cboType has no value then any control having a tag value not containing 0 will be hidden[/blue]

4) After that IF statement, am I adding more else statements for the len of the controls that should be visible and should not? Per Q 3, is this -
[blue]what?[/blue]

[blue]This statement evaluates to either true/yes or false/no to set the visible property[/blue]

Instr(ctl.Tag,intType)> 0

3) How do I call this function from an event, like after update, form load? Sorry for the stupid question, but I don't know that I have made many functions and then called them, so I am not even sure how to look up an example of this.

[blue]Since this is a function, you can set the On Current of the form and the After Update of cboType to =ShowHide()[/blue]

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

ok, I guess I am confused at what is next. Per my original post, I have a case select, because if cboType = 1, then certain controls need to be visible and others not. If it is 2, then a different combination of visible/invisible.

How I am I setting each values set of visible/invisible rule for each field in this?

Sorry, I am confused!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Thank you for all your help. I think you are just way cooler than me, lol. I was able to get the following code up and running much more easily. It is in the onchange of the first combo and the oncurrent of the form:

Private Sub cboType_Change()
Dim ctl As Control
Me.cboType.SetFocus

Me.cboSubType.Visible = False
Me.dtDate1.Visible = False
Me.txtComments1.Visible = False
Me.txtComments3.Visible = False
Me.txtComments245.Visible = False
Me.cboPerson1.Visible = False
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = False
Me.dtDate3.Visible = False
Me.txtPersonSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1PartyType.Visible = False
Me.cboPerson2PartyType.Visible = False
Me.cboPerson3PartyType.Visible = False

Select Case Me.cboType

Case "1":

Me.cboSubType.Visible = True
Me.dtDate1.Visible = True
Me.txtComments1.Visible = True
Me.txtComments3.Visible = False
Me.txtComments245.Visible = False
Me.cboPerson1.Visible = False
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = False
Me.dtDate3.Visible = False
Me.txtPersonSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1PartyType.Visible = False
Me.cboPerson2PartyType.Visible = False
Me.cboPerson3PartyType.Visible = False
Me.cmdOpenComplaintService.Visible = False

Case "2":
Me.cboSubType.Visible = True
Me.dtDate1.Visible = False
Me.txtComments1.Visible = False
Me.txtComments3.Visible = False
Me.txtComments245.Visible = True
Me.cboPerson1.Visible = True
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = True
Me.dtDate3.Visible = False
Me.txtPersonSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1PartyType.Visible = True
Me.cboPerson2PartyType.Visible = False
Me.cboPerson3PartyType.Visible = False
Me.cmdOpenComplaintService.Visible = True
Me.cmdOpenComplaintService.Caption = "Open Form 1"

Case "3":
Me.cboSubType.Visible = False
Me.dtDate1.Visible = False
Me.txtComments1.Visible = False
Me.txtComments3.Visible = True
Me.txtComments245.Visible = False
Me.cboPerson1.Visible = False
Me.cboPerson2.Visible = True
Me.cboPerson3.Visible = True
Me.dtDate2.Visible = False
Me.dtDate3.Visible = True
Me.txtPersonSpec.Visible = True
Me.dtDate4.Visible = True
Me.cboPerson1PartyType.Visible = False
Me.cboPerson2PartyType.Visible = True
Me.cboPerson3PartyType.Visible = True
Me.cmdOpenComplaintService.Visible = False

Case "4":
Me.cboSubType.Visible = True
Me.dtDate1.Visible = False
Me.txtComments1.Visible = False
Me.txtComments3.Visible = False
Me.txtComments245.Visible = True
Me.cboPerson1.Visible = True
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = True
Me.dtDate3.Visible = False
Me.txtPersonSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1PartyType.Visible = True
Me.cboPerson2PartyType.Visible = False
Me.cboPerson3PartyType.Visible = False
Me.cmdOpenComplaintService.Visible = True
Me.cmdOpenComplaintService.Caption = "Open Form 2"

Case "5":
Me.cboSubType.Visible = True
Me.dtDate1.Visible = False
Me.txtComments1.Visible = False
Me.txtComments3.Visible = False
Me.txtComments245.Visible = True
Me.cboPerson1.Visible = True
Me.cboPerson2.Visible = False
Me.cboPerson3.Visible = False
Me.dtDate2.Visible = True
Me.dtDate3.Visible = False
Me.txtPersonSpec.Visible = False
Me.dtDate4.Visible = False
Me.cboPerson1PartyType.Visible = True
Me.cboPerson2PartyType.Visible = False
Me.cboPerson3PartyType.Visible = False
Me.cmdOpenComplaintService.Visible = True
Me.cmdOpenComplaintService.Caption = "Open Form 3"

End Select
End Sub

I made different text box controls for the comments field, showing the right one in the right spot on the form to fix the issue of having a bunch of space when there is only 1 control visible on the form besides the combo and the comments.

Thanks for all your help!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Apparently you don't mind lots of copy and paste. It looks like cboType changed from a numeric to text at some point.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

It is numeric. I only have that in 2 places. On load of the form and on change of the combo. Is this wrong?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

If cboType is numeric, then you should remove the quotes from around the numbers in your Select Case

Select Case Me.cboType
   Case 1

You have pasted the same code with lots of repeating control names into the correct two places. A somewhat better solution would be to create a SUB or FUNCTION with one copy of the code and then call the SUB or FUNCTION from those two places.


Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you. I appreciate the advice, but I don't know how to do what you are suggesting. If you think you can help me create it, I will gladly use a better method. For right now I have to develop what I can get done.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I already suggested the setup in my posting on 19 May 10 11:58. Your code in the On Current and After Update would simply be:

   Call ShowHide()

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I did read that post. Per my post on 19 May 10 13:12 I don't know how to create the ShowHide function. The sample you gave me is wonderful, but it is only for setting everything invisible initially. I still need to go through when the combo choice changes, which fields should be shown or hidden and I do not know how to do that.

Unless that can be explained, I have no choice but to stick with what I am able to create.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You would create your own sub or function by typing the following into your form's module:

Function ShowHide()
   'paste the code from the after update here
    Dim ctl As Control
    Me.cboType.SetFocus

    Me.cboSubType.Visible = False
    Me.dtDate1.Visible = False
    Me.txtComments1.Visible = False
    '... etc
    '... etc
    '... etc
        Me.cmdOpenComplaintService.Visible = True
        Me.cmdOpenComplaintService.Caption = "Open Form 3"

    End Select
End Function

Then your new code

Private Sub cboType_Change()
    Call ShowHide()
End Sub

Private Sub Form_Current()
    Call ShowHide()
End Sub

I would actually use the After Update of cboType rather than the Change event.

Duane
Hook'D on Access
MS Access MVP