DoCmd.OpenForm

Question

I get a Type mismatch error with this code but if I remove frmview from DoCmd then it works fine and form loads in form view.

Dim myform, frmview as string

myform = "Customers"

frmview="acFormDS"

DoCmd.OpenForm myform, frmview

Is it possible to specify acFormDS parameter with a variable in DoCmd ?

Answer 1

The reason why you don't need quotes characters around acFormDS is because it is not a string expression, but a constant.  Its value is an integer number, in this case 3.  You can see this in a couple of ways:

1.  Enter the following in the immediate window:

? acFormDS

 3

2.Open the Object Browser and search for acFormDS.  Down at the bottom of the dialogue you'll see:

Const acFormDS = 3

    Member of Access.AcFormView

So if you wish to assign it to a variable, you could do so like this:

    Dim frmview As Integer

    frmview = acFormDS

but it would be better to do as Tom described:

    Dim frmview As AcFormView

    frmview = acFormDS

either way:

    Debug.Print frmview

would return 3 in the immediate (aka Debug) window.

Answer 2

Hans is correct. How did he know that?

The help page for DoCmd.OpenForm is here: 

https://docs.microsoft.com/en-us/office/vba/api/Access.DoCmd.OpenForm

Under "Parameters" it says:

View	Optional	AcFormView

So you should have written:

Dim frmview as AcFormView

frmview = 

And then Intellisense would have shown you the list of the AcFormView enumeration.

A few more comments:

> Dim myform, frmview as string

What is the datatype of myform? You think string; I know it is variant. You can check with the VarType function.

If you want to declare multiple variables on one line you have to specify the type for each:

Dim myform as string, frmview as AcFormView

>frmview as string

"frm" is a very poor prefix for a string. By convention it refers to a form object:

dim frmCustomer as Form

set frmCustomer = Forms!frmCustomer   'assumes the form is open

Answer 3

Thank you for helping out.  I have changed the datatype and now all works well.  I would never have figured it out without your help.

Answer 4

Remove the quotes from around acFormDS:

frmview = acFormDS