Active fields based on dropdown choice

[vangogh228]

I have been asked this question and do not know the answer. All help is appreciated!!!

Can a choice on a dropdown cause fields in a form to become active/inactive? In the alternative, can a subform be automatically shown in a new window based on the choice in a dropdown?

My client has to decide what kind of service plan is appropriate for his clients when entering ther data, but then the series of following fields significantly changes based on that choice. I was sinply going to create tabs with subforms for each of his options, but that seems unwieldy.

Thanks for any help!!!

Tom

 

[Legion]

Right...I'm no programmer just a technical user (like yourself obviously!) but I used the following attached to an After Update Event of a Combo Box control to make a selection from my combo box open up a new form matching records within both forms.

‘ replace x with your number (or text entry of combo!)
If [ComboName].Value = "x" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "SecondForm", wherecondition:="[PrimKey]= " & Me![PrimKey]
End If

Does this help......



Legion.
Fresh victims for my ever growing army of the dark.........

 

[SaraMegan]

Tom,

How many choices are there for types of service?

I've come across a similar situation and have used option buttons with a select case in the event sub which renders fields (and subforms) visible, invisible, abled, disabled, and all that.

So if you only have a few choices, that may be a decent method of going for it. There may also be a way to do it using a combo box but I haven't done it myself.

If this looks good to you or you want a sample, I'll see what I can do. I'm still kind of a beginner here, but have some experience with what I think you're looking for.

Let me know.

--Sara

 

[BukHix]

Here is one way to do what you want with a combobox and this will work for controls or subforms. In my example I am just going to use text boxes.

First of all create a new module and put this in it.

Public Function HideControls()
Dim ctl  As Control

For Each ctl In Screen.ActiveForm.Controls
    If TypeOf ctl Is TextBox Then
        With ctl
            .Visible = False
        End With
    End If
Next ctl

End Function
' *******************************************

Public Function ShowControls()
Dim ctl  As Control

For Each ctl In Screen.ActiveForm.Controls
    If TypeOf ctl Is TextBox Then
        With ctl
            .Visible = True
        End With
    End If
Next ctl

End Function

Now create a combo box (drop down list) and add two values to it. One Show and the other Hide. For simplicities sake I am only going to use two options but if you need more options you would use a Select Case instead.

Now on the OnChange Event of your combo box put this in it.

Private Sub cboControlStatus_Change()

If Me.cboControlStatus = "Show" Then
    ShowControls
Else
    HideControls
End If

End Sub

Each time the value in the combobox is changed the code will be run. If the current value is show the controls will be shown otherwise they will not. Again if you have more then two options you are going to need to use a Select Case.

Post back if you have more questions.

 

[vangogh228]

All: Thanks for your replies. I apologize for not getting back on here right away, but I spent four days in the hospital after a "mini-stroke." It happened just a few hours after I posted the thread. Doc says I'm fine for now, but need to take anti-coag for a while. Weird weird experience, and scary. And with blood pressure or 105/65, you'd think I'd avoid some of this stuff!

SaraMegan: I am definitely interested in any examples! There are only 3-4 choices on this issue. I am trying to get them to reevaluate their choices... there may only be 2 in the end.

Legion: Thanks for the help! I will try that.

BukHix: Oooooohhh... that may be a little beyond me. I will have to do quite a bit of reading before feeling comfortable with your code there. I promise to practice with it though! Thank you! I think I see where it's headed... a little. It appears there will be two choices, each requiring a different set of controls.