Referencing Multiple Forms from a Function in a Standard Module

[tonyrusin]

I'm building an app that has a combo box with different options
depending on which form the user is working from. That part is working
fine. I created a function that is supposed to open a specific form
depending on which option the user had selected. Here is my code:
---
Function CommandMenu() As String
On Error GoTo Err_CommandMenu

Dim strFormName As String
Dim strFormExpression As String

strFormName = Application.CurrentObjectName
strFormExpression = "Forms!" & strFormName & "!cboCommandMenu"

If strFormExpression = "Login" Then

DoCmd.OpenForm "frmLogin", acNormal

ElseIf strFormExpression = "CCN" Then

DoCmd.OpenForm "frmCCN", acNormal

ElseIf strFormExpression = "CCN - What's New" Then

DoCmd.OpenForm "frmCCNWhatsNew", acNormal

ElseIf strFormExpression = "CCN - Report" Then

DoCmd.OpenForm "frmCCNReportOptions", acNormal

ElseIf strFormExpression = "Database Config" Then

DoCmd.OpenForm "frmDatabaseConfig"

ElseIf strFormExpression = "DOC - Control" Then

DoCmd.OpenForm "fromDocControl"

ElseIf strFormExpression = "DOC - Library" Then

DoCmd.OpenForm "frmDocLibrary"

ElseIf strFormExpression = "DOC - What's New" Then

DoCmd.OpenForm "frmDocWhatsNew"

End If

Exit_CommandMenu:
End Function

Err_CommandMenu:
MsgBox Error$
GoTo Exit_CommandMenu

End Function
---
When I step through the process, it appears that
"strFormExpression" evaluates correctly but it doesn't find a
match when it steps through the "if" statements. Anyone know what
I'm missing here? Thanks.

- Tony

 

[doodle]

You should be using a Select Case for this.

-doodle

 

[doodle]

You should be using a Select Case for this.

-doodle

 

[tonyrusin]

You should be using a Select Case for this.

-doodle

Thanks for the tip and I updated the code.
---
Function CommandMenu() As String

Dim strFormName As String
Dim strFormExpression As String

strFormName = Application.CurrentObjectName
strFormExpression = "Forms!" & strFormName & "!cboCommandMenu"

Select Case strFormExpression

Case Is = "Login"

DoCmd.OpenForm "frmLogin", acNormal

Case Is = "CCN"

DoCmd.OpenForm "frmCCN", acNormal

Case Is = "CCN - What's New"

DoCmd.OpenForm "frmCCNWhatsNew", acNormal

Case Is = "CCN - Report"

DoCmd.OpenForm "frmCCNReportOptions", acNormal

Case Is = "Database Config"

DoCmd.OpenForm "frmDatabaseConfig"

Case Is = "DOC - Control"

DoCmd.OpenForm "fromDocControl"

Case Is = "DOC - Library"

DoCmd.OpenForm "frmDocLibrary"

Case Is = "DOC - What's New"

DoCmd.OpenForm "frmDocWhatsNew"

End Select

End Function

 

[Gerwin Berentschot]

I looked over your code, and I am quite confinced that strFormExpression will
not ever evaluate right. You are creating a string which for example always
starts with 'forms!' (try debugging it and you will see).

Try something like this:

function CommandMenu (ComboboxValue as string)
select case ComboboxValue
case "Login"
DoCmd.OpenForm "frmLogin", acNormal
case "x"
DoCmd.OpenForm "x"
case "y"
DoCmd.OpenForm "y"
case "...."
DoCmd.OpenForm .....
end select

You call the function after the user has changed the selected value in the
combobox by typing CommandMenu(MyComboboxNamen.value) in the afterupdate
event of the combobox.

 

[Albert D. Kallal]

strFormName = Application.CurrentObjectName

At this point, then strFormName = the name of your form

(lets assume frmMain).

So, strFormName is now

"frmMain"

then, we go:

strFormExpression = "Forms!" & strFormName & "!cboCommandMenu"

The above is thus

"Forms!" + "frmMain" + "Logon"

thus, this evaluates to:

strFromExpression = "forms!frmMain!Lognon"

hence, it is now:

"Forms!frmMain!Logon"

thus,

if strFormExpression = "Forms!frmMain!Logon" = true


if strFormExpression = "Logon" Then

becomes:

if "Forms!frmMain!Logon" = "Logon" then


Clearly the string of "Forms!fromMain!LogOn" is NOT = "Logon"

I am VERY much confused as to why you have:

strFormName = Application.CurrentObjectName

For what purpose do you have the above? what does it do? I don't see the
need?

Gerwin seems to have posted a solution that would work for your needs....

 

[tonyrusin]

Gerwin,

It works! Here is my resulting code:
---
Function CommandMenu(cboCommandMenuValue As String)

Select Case cboCommandMenuValue

Case Is = "Login"

DoCmd.OpenForm "frmLogin", acNormal

Case Is = "CCN"

DoCmd.OpenForm "frmCCN", acNormal

Case Is = "CCN - What's New"

DoCmd.OpenForm "frmCCNWhatsNew", acNormal

Case Is = "CCN - Report"

DoCmd.OpenForm "frmCCNReportOptions", acNormal

Case Is = "Database Config"

DoCmd.OpenForm "frmDatabaseConfig"

Case Is = "DOC - Control"

DoCmd.OpenForm "fromDocControl"

Case Is = "DOC - Library"

DoCmd.OpenForm "frmDocLibrary"

Case Is = "DOC - What's New"

DoCmd.OpenForm "frmDocWhatsNew"

End Select

End Function
---
I appreciate your time, thanks for your help on this.

Regards,

- Tony