Subform dependent on combobox result

Question

Hi, I would like the option in my combo box to determine which subform is displayed. I am trying the code below. 

Dim strForm As String
Select Case Me!cboVerificationType
  Case "Single"
    strForm = "SubFormSingle"
  Case "Dual"
    strForm = "SubFormDual"
  Case "Kitchen"
    strForm = "SubFormKitchen"
  Case "Scales"
    strForm = "SubFormScales"
  Case Else
    MsgBox "select the search type"
    Exit Sub
End Select
DoCmd.OpenForm strFormName, acNormal, , , , acDialog

However it is giving me an error on the "Me" part on the second line. the error is "invalid outside procedure". Please can someone assist. 

Answer 1

This code should be part of the After Update event procedure of cboVerificationType, and this procedure should be in the code module of the form that contains this combo box:

Private Sub cboVerificationType_AfterUpdate()
    Dim strForm As String
    Select Case Me!cboVerificationType
        Case "Single"
            strForm = "SubFormSingle"
        Case "Dual"
            strForm = "SubFormDual"
        Case "Kitchen"
            strForm = "SubFormKitchen"
        Case "Scales"
            strForm = "SubFormScales"
        Case Else
            MsgBox "select the search type"
            Exit Sub
    End Select
    DoCmd.OpenForm strFormName, acNormal, , , , acDialog
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

I think HV's reply opens a Form, rather than a sub form.

The sub form approach will depend on whether you want to attempt to dynamically switch the source of 1 sub form object - or - more simply put multiple separate sub forms on the main form and dynamically control the visibility of the multiple sub forms.

If the structure of the subforms and overall size are identical you may be able to get away with a single sub form control...but if they differ significantly it may be easier to do the latter.  The latter is pretty easy but messy and somewhat difficult to manage as the sub form controls will be on top of each other probably.