execute variable containing docmd in vba

Question

I am working in Access using VBA. I am trying to execute a statement containing a docmd clause. The statement will contain a variable. The variable will be either docmd.openform or docmd.openquery. I can't use an IF or CASE to determine which prefix.

I've tried something along the following:

tmpStr = docmd.openform "blah"
docmd.execute tmpStr

I need the full docmd statement to be in the variable. I just don't know how to execute the variable.

Thanks for all your help!

score 0 · Answer 1 · answered Jun 21 '18 at 15:58

A similar idea is discussed in this thread: Execute VBA code from string

...but I don't think this will work the way you want it to... I don't think DoCmd has an "execute"; There is Database.Execute, but that is for Database Engine actions and can't open a userform. If you are trying to either open a userform or run a query depending on some condition, it seems like there must be some other way to determine which action your code should take. Can you give some more details about why you can't use an IF or a SELECT CASE?

score 0 · Answer 2 · answered Jun 21 '18 at 16:43

I don't think what you're looking for is possible, but what about a workaround using subs and the Application.Run method?

Sub Test()
    Application.Run "OpenForm", "Form1"
    Application.Run "OpenQuery", "Query1"
End Sub

Sub OpenForm(ByVal formName As String)
    DoCmd.OpenForm formName
End Sub

Sub OpenQuery(ByVal queryName As String)
    DoCmd.OpenQuery queryName
End Sub

execute variable containing docmd in vba

2 Answers2