Executing an OpenForm with a variable not working

[Steven Sutton]

I have a form (call it Form_A) that I open from several different forms. On
Form_A I have a button that closes the closes Form_A and opens the form from
whence it was called. I do this by storing the name of the form opening
Form_A in a variable. When the user clicks on the "Return" button on Form_A
it executes a this statement:

DoCmd.OpenForm strReturnForm

Obviously, strReturnForm is the name of the variable I am using. My problem
is I now need to open a form with some arguments for the OpenForm command. I
tried storing the argument string in the strReturnForm variable like this:

strReturnForm = """frmWhiteboard"", acFormDS,,,,acWindowNormal"

but when I execute the

DoCmd.OpenForm strReturnForm

I get an error that I have misspelled the form name or that I am referring
to a form that doesn't exist. I did a Debug.Print strReturnForm and the
string looks perfect to me. In fact, I copied the text from the immediate
window and pasted it into the OpenForm command:

DoCmd.OpenForm "frmWhiteboard",acFormDS,,,,acWindowNormal

and it worked just fine. So I am a bit confused. Would someone please
explain why this doesn't work and maybe suggest a solution? As always, thanks!

 

[Marshall Barton]

I have a form (call it Form_A) that I open from several different forms. On
Form_A I have a button that closes the closes Form_A and opens the form from
whence it was called. I do this by storing the name of the form opening
Form_A in a variable. When the user clicks on the "Return" button on Form_A
it executes a this statement:

DoCmd.OpenForm strReturnForm

Obviously, strReturnForm is the name of the variable I am using. My problem
is I now need to open a form with some arguments for the OpenForm command. I
tried storing the argument string in the strReturnForm variable like this:

strReturnForm = """frmWhiteboard"", acFormDS,,,,acWindowNormal"

but when I execute the

DoCmd.OpenForm strReturnForm

I get an error that I have misspelled the form name or that I am referring
to a form that doesn't exist. I did a Debug.Print strReturnForm and the
string looks perfect to me. In fact, I copied the text from the immediate
window and pasted it into the OpenForm command:

DoCmd.OpenForm "frmWhiteboard",acFormDS,,,,acWindowNormal

and it worked just fine. So I am a bit confused. Would someone please
explain why this doesn't work and maybe suggest a solution? As always, thanks!

You can not include a statement's syntacic elements in a
string. Essentially what happens is that Access/VBA thinks
the entire string is the name of your form.

Bottom line is you must specify each argument separately.
You can either use a bunch of global variables, a UDT, an
array or parse the string using the Split function. The
latter two require that your code be aware of the type of
each argument so you can convert from string to whatever's
needed. The other two techniques can use appropriately
decared variables so your code can just use them as is.

 

[Steven Sutton]

Thanks Marshall for the answer. I will start re-writing!