How to open different forms based on the drop down selected..

[stalk]

I have a FORM that has one drop down field. Based on the drop down selected ( Me.cmd_selector) I would like to open the specific form? stDocName in the comment is the form name I would like to open. What changes should I make for the code below..





Private Sub cmd_form_Open_Click()


On Error GoTo Err_cmd_form_Open_Click


Dim stDocName As String
Dim stLinkCriteria As String

'stDocName = "ASSAY_Main_frm"

'DoCmd.OpenForm stDocName, , , stLinkCriteria

Me.cmd_selector = "GT Unit Info" ' stDocName = "GT_unit_Main_Frm"
Me.cmd_selector ="GT Assay Info" 'stDocName = "GT_Assay_Details_Survey_Responses_frm"
Me.cmd_selector ="PR unit Info" 'stDocName = "PR_Unit_Main"
Me.cmd_selector ="PR Assay Info" 'stDocName = "PR_Assay_details_Main"


Exit_cmd_form_Open_Click:
Exit Sub


Err_cmd_form_Open_Click:
MsgBox Err.Description
Resume Exit_cmd_form_Open_Click
End Sub


Thank you,

[Welshgasman]

Bring the formname in as another column in the combo, then use that value.
Columns start at 0.

[Micron]

That code doesn't make much sense. When you click the button, you're just assigning 4 different values to a control, one after the other. Are those supposed to be tests (i.e. IF the value is this, do this."?

Please post your code within code tags (see # on forum posting tool bar/menu bar)

[stalk]

Hello Micron, I updated my post with code tags..
If user select "GT Unit Info" value from the drop down and clicks the open button at the bottom, then I want to open "GT_unit_Main_Frm" form


Me.cmd_selector = "GT Unit Info" ' stDocName = "GT_unit_Main_Frm"

[accesstos]

Hi stalk!

At first, set those properties for cmd_selector:

Property Name	Property Value
Row Source	"GT_unit_Main_Frm";"GT Unit Info";"GT_Assay_Details_Survey_Responses_frm";"GT Assay Info";"PR_Unit_Main";"PR unit Info"
Row Source Type	Value List
Column Count	2
Column Widths	0

Then, you can write code like this:

Private Sub cmd_form_Open_Click()
    Dim stLinkCriteria As String
    
    With Me.cmd_selector
        If IsNull(.Value) Then
            Beep
        Else
            stLinkCriteria = "1=1" 'Set here the Link Criteria
            DoCmd.OpenForm .Value, , , stLinkCriteria
        End If
    End With
End Sub

Hope it helps.

[Welshgasman]

PMFJI, but what is the purpose of the stLinkCriteria ?

[stalk]

Thank you accesstos.
After making the changes to the row source. when I ran the above code I get an error:
Run-time error'2101': The form name " Name of the form" is misspelled. Even thought I gave the correct name of the form..

[Micron]

strLinkCriteria is usually what Access creates when you convert a macro to code. When I posted, I figured what the combo should have had is a column for the values the user sees and one for the actually object name. Since it doesn't, I took the code (hours ago) and modified it to this

Private Sub cmd_form_Open_Click()

On Error GoTo errHandler

Select Case Me.cmd_selector
  Case "GT Unit Info"
    DoCmd.Openform "GT_unit_Main_Frm"
  Case "GT Assay Info"
    DoCmd.Openform "GT_Assay_Details_Survey_Responses_frm"
  Case "PR unit Info"
    DoCmd.Openform "PR_Unit_Main"
  Case "PR Assay Info"
    DoCmd.Openform "PR_Assay_details_Main"
End Select

exitHere:
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

[accesstos]

Is the Bound Column of cmd_selector set to 1?

Choose Debug at error message box and hover over .Value in highlighted line to see the form name that tries to open.

[stalk]

Thank you so much for the quick solution. It works perfectly as desired

[Micron]

I'm going to guess that comment about a solution was directed to me since the code I posted was quite close to yours. In that case, you're welcome and glad I could help.

[stalk]

Yes,Micron. I thought I added the comments to you. Not sure how the comments work if multiple users helps. I know my question is the basic one for VBA coders but I am not VBA person. Thank you again.

[Micron]

@stalk, glad to help. Good luck with your project.

P.S. I just gave you a clue to one way to reference a prior poster in this forum.