CODEWITHSUNDEEP
formsms-accessreferencevbams-access-2013
TimesMoney
TimesMoney

Reputation: 13

MS Access Reference Form by string variable

I have a combobox that obtains its rowsource from MySysObjects and shows a complete list of Forms in the DB. A form name is selected from the combobox and passed to a function as a string, using VBA (since don't know how to convert that string to Form). So, once the string value of the form is passed to the function I now have the string variable of the form I wish to do stuff to... I would normally use:

Function MyFunction()
    Dim frmForm as Form
    Set frmForm = form("MyForm")
    DoCmd.OpenForm frmForm.Name, acDesign, , , , acHidden
    With frmForm
        Do stuff...
    End With
End Function

But! As I have passed a string variable to the function - such as:

Function MyFunction(strFormName as String)
    Dim frmForm as Form
    Set frmForm = Form(strFormName)    'THIS DOESN'T WORK
    DoCmd.OpenForm frmForm.Name, acDesign, , , , acHidden
    With frmForm
        Do stuff...
    End With
End Function

My question is how do I use the Set statement to return a Form object? I can either convert that combobox value on the form and pass a Form Object to the function or convert the combobox value to the function and convert it using Set or whatever is required. I am hoping that it is a simple syntax question, but as I've tried everything I know, it must that elusive answer I have not tried!

Upvotes: 1

Views: 3917

Answers (1)

HansUp
HansUp

Reputation: 97101

There are 2 problems at this line:

Set frmForm = Form(strFormName)

First the collection's name is Forms, not Form. And second is that Forms is the collection of forms which are currently open. So Forms(strFormName) will throw an error if strFormName is not open at the time.

If you just do DoCmd.OpenForm strFormName, acDesign, , , , acHidden, the form will be opened in Design View and hidden regardless of whether or not it was already open. And then you can do the Set frmForm without triggering an error.

Function MyFunction(strFormName As String)
    Dim frmForm As Form
    DoCmd.OpenForm strFormName, acDesign, , , , acHidden
    Set frmForm = Forms(strFormName)
    With frmForm
        'Do stuff...
    End With
End Function

Upvotes: 1

Related Questions