Access 2010 VBA - Instantiating a Form

I used to be a VB5/6 guy about a million years ago.  And what I used to do back then, is what I really want to be able to do in VBA.  But I can't figure out how.

Here's what I'm trying, which fails miserably.  How do I achieve the equivalent of this code?

Private mfrmSearch As Form

Private Sub cmdSearch_Click()

   Dim nSearchResult As Integer
   Set mfrmSearch = New Form_frmSearch  '// Instantiate the form
   Set mfrmSearch.ParentForm = Me   '// Set its parent to Me (another form)
   mfrmSearch.ShowDialog   '// Show modally
   nSearchResult = mfrmSearch.SearchResult   '// Get the result of the dialog; store it.
   Call Unload(mfrmSearch)  '// Unload the form instance
End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Access Forms are objects -- but you can't really instantiate and open them like VB 6 objects, and they don't have parent/child relationships like you have there.

Your parent form is going to need a Public variable
Public nSearchResult As Integer

Then from code you'll open your search form with an OpenArgs value of the calling form's name
DoCmd.OpenForm "frmSearch",acNormal,,,,acDialog,Me.Name
It is your search form that will have the modal property assigned within it's design.
I don't think you can do that through code at open

Then, in your search form's code you are going to code a control/event that sets the public variable back on the calling form you passed in by name
Forms(me.OpenArgs).nSearchResult = SomeIntegerGeneratedByUserActionsOrCodeEvents
And then you'll need to close the search form.

Does this make sense?
Access forms are independent objects that only one instance of can be open at a time.
They are part of the AllForms collection
They do not have parent and child relationships.
You can query CurrentProject.AllForms("SomeFormName").IsLoaded to check if a form is loaded
Public variables of a form can be Get/Set by simple assignment as properties accessible through the . operator. (maybe ! operator -- but that usually is syntax for controls on a form)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Allan Browne explains it quite well here:
Managing Multiple Instances of a Form

Hope that helps :)
You will be much happier using Access if you use the "Access" methodology.  If you try to remake your Access app in your image of what VB used to be, you will be constantly frustrated.  The two environments are similar but different.  Access Forms and Reports are objects and their associated code is a class module.  But, going beyond that will just make your app harder to develop and impossible for a successor to deal with because it will be so far outside the norm that unless he has a VB background, he won't understand what you are doing or why.  Unless you have something that is truly universal, stay far, far away from class modules and go with called procedures or functions in standard modules to implement reusable code and leave the encapsulation, etc to VB.  You can go a long way by simply passing in a form/report reference to a common module.  Yes, the actual control names have to be the same on all the reports and forms that use the common code but if they aren't, then what you are doing probably isn't as common as you think.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Anders has gotten you pointed in the right direction, you can have multiple instances of forms.   However as Pat said, Access is not VB6, although the code language VBA, is close to VB 6.  But the code language Access uses is just part of what makes up "Access".

So to add on to what she said, when working with Access objects (Forms, reports, etc), it would be best to forget what you did with VB6.    When your writing code, yes think of VB6.  Almost everything applies.

Multiple form instances are a great example; routine in VB6, but a pain to work with in Access and generally most don't.

And some didn't even think you could :O
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
I personally use this approach quite often. Many times people want to be able to pull up 2 objects side-by-side, just like you can have 2 word or 2 excel documents open at a time. Maybe they want to compare notes between the 2, maybe they want to copy/paste part of the text.

I am continuously amazed at how the original Access team managed to simplify the process to the extreme, nearly without compromising on more advanced functionality such as instancing forms.
One difference when you are coding - the .value property is the VBA default property rather then the .text property.  In Access forms/reports, you can ONLY reference the .text property when a control has the focus.

Use -
rather than
Me.mycontrol.Value or mycontrol.Value

the "Me." reference gives you intellisense and the .Value is redundant.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< When your writing code, yes think of VB6.  Almost everything applies.>>

  I should clarify this statement.  What I meant was syntax and verb wise.  VB6 and VBA are almost identical expect for a few minor differences.  String Functions, Logical Operations, etc are all the same.

 When dealing with objects however, it's similar, but very different.  This is where you want to forget what you did in VB6.

 Access is made up of the Access UI and objects, VBA, and JET/ACE all talking to one another to give you "Access".  This is quite unlike VB6.

bamapieAuthor Commented:
This was fantastic and a real eye-opener.  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.