Open Form From ComboBox Help (1 Viewer)

[johnob]

Hi all,

Im using the below code to open a from depending on what option is selected on a combobox.

Private Sub DirectPCombo_Click()

Dim cboVal As String
Dim stDocName As String

cboVal = Me.DirectPCombo.Value
Select Case cboVal
Case "One"
    stDocName = "TestOneFrm"
Case "OneRpt"
    stDocName = "TestOneRpt"
Case "OneName"
    stDocName = "TestOneByNameRpt"
Case "OneCode"
    stDocName = "TestOneByCodeRpt"
 
End Select

DoCmd.OpenForm stDocName

End Sub

If you select the first item [One] it will open the correct form [TestOne]. But when you select any of the others an error comes up saying that the name is no a form. So I added

DoCmd.OpenReport stDocName

But the no items would select.

I know If split the reports form the forms and make two comboboxes it would work, but it would be better it both could be in the same combobox.

Can anyone help.

Thank you

 

[pr2-eugin]

You have to encode the Open methods in the case statements and also change the event from Click to AfterUpdate like,

Private Sub DirectPCombo_AfterUpdate()
    Select Case Me.DirectPCombo
    Case "One"
        [COLOR=Red][B]DoCmd.OpenForm[/B][/COLOR] "TestOneFrm"
    Case "OneRpt"
        [COLOR=Blue][B]DoCmd.OpenReport [/B][/COLOR]"TestOneRpt"
    Case "OneName"
        DoCmd.OpenForm "TestOneByNameRpt"
    Case "OneCode"
        DoCmd.OpenForm "TestOneByCodeRpt"
    End Select
End Sub

 

[Addyman]

A number of ways of doing it. If your naming convention is always used you could try:

Private Sub DirectPCombo_Click()

Dim cboVal As String
Dim stDocName As String

cboVal = Me.DirectPCombo.Value
Select Case cboVal
Case "One"
    stDocName = "TestOneFrm"
Case "OneRpt"
    stDocName = "TestOneRpt"
Case "OneName"
    stDocName = "TestOneByNameRpt"
Case "OneCode"
    stDocName = "TestOneByCodeRpt"
 
End Select

Select Case Right(stDocName,3)

Case "Frm"

DoCmd.OpenForm stDocName

Case "Rpt"

DoCmd.OpenReport stDocName

End Select

End Sub

Alternatively, you could have a multi column combo box with one column being an ID field which indicates the type of choice. So, you could have a table called tblObjectType for example with data such as:

ID Type
1 Form
2 Report


etc. etc.

Then, include the ID field in the combo but have it as a hidden column so in the case above it would have values:

1
2
2
2

This way as you add more objects to your combo, you can give them an object type ID and use this value to determine whether a form should be opened, or a report or query or.....