MS Access Reference Form by string variable

Question

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!

HansUp · Accepted Answer · 2016-08-21 16:20:30Z

1

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

answered Aug 21, 2016 at 16:20

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TimesMoney Over a year ago

Thanks for the reply! Yes, I see that I have made a couple of typos!! I found it easier to just type freehand and obviously missed a few uppercase issues and missing letters (particularly an 's')! Fundamentally, it was the ORDER of the Set part that I've known and read many times before (until a form is opened you can't Set anything!), but since 've never actually used it ever before, I chose to ignore a fundamental point! Problem fixed! And to think I spent several hours researching it too! Once you know; you know!

TimesMoney Over a year ago

And... THANKS! THANKS!

Collectives™ on Stack Overflow

MS Access Reference Form by string variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related