Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 914
  • Last Modified:

how to get intellisense for form controls when typing in module

hi guys,

i'm trying to follow MVC rules which states that the form and the code behind just controls the view and the controller and any action is from somewhere else - to keep the form lightweight and reuseable.

thus my code is in a module.

i pass the form byref to the module but when i am typing something like "frmImport.lstExcelWorksheets", i don't get intellisense.

what i want is when i type "frmImport.lstE" i get intellisense suggesting "frmImport.lstExcelWorksheets".

how can i do that? thanks guys!
6 Solutions
Gustav BrockCIOCommented:
I don't think you can. In the function, frmImport is just a form, and your function has no clue what controls that form will hold.

As so often before, I think you are just making life more difficult than it needs to be.

Access is optimized to act like it does, and if you can't or won't follow those rules - and there can be many good reasons for that as Access in no way is the universal tool - you are much better off using other tools - like Visual Studio which is pure pleasure to work with, and where I think your talent will feel much more at home.

Dale FyeCommented:
However, if you use the syntax:


You will get intellisense.  

Note:  in the VBE window, when you look in the Project Window and expand the Microsoft Office Access Class Objects folder, this is how the forms and reports are listed.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"However, if you use the syntax:"
Of course, it's possible that might accidentally open another instance of the Form ...

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Dale FyeCommented:

I have only seen that occur when the application (not Access, my application) is running, and when the form is not already open.

However, I believe, based on the wording of the original post, that the user is talking about taking advantage of intellisense in design mode, when editing code in the VB Editor.

I rarely, if ever leave it in that format.  I will generally run either Find & Replace or just do a simple search and replace within the selected module or procedure to replace:




when I am done editing that particular module.
i'm trying to follow MVC rules which states that the form and the code behind just controls the view and the controller and any action is from somewhere else - to keep the form lightweight and reuseable
Who is MVC?  Does this person hate Access?

Whether the code is in the form or in a standard module, it must be loaded into memory to execute it so I doubt you are actually saving enough to justify the extra effort.  Your form would have to be huge and have lots of "sometimes" code that you put in a bunch of modules so that only the little code you need at any one time gets loaded.  If this is your situation, you should probably rethink your design and do some reading on coupling and cohesion.

you can use this syntax (suggested by Dale above also) in a standard module:

Public Sub MySub(frm As Form_YourFormName)

Open in new window

That would give you IntelliSense when typing something but the Sub must be specific to use only this form and not any other. But that's the question here: If you want to use common forms and common subs then of course the form must not know anything about the sub and the sub must not know anything (including the control names) about the form so it can use the generic "Form" class.

That makes no sense here - if you would use the specific Form_YourFormName then you have IntelliSense but also a coupling between the form and the sub so it would make more sense to insert the sub into the form's code - but that would also make no sense if you want to use the same code for other form's too (maybe if they have the same controls). So what can you do to make both possible? The same as I told you earlier: You can use Interfaces or you can use Events.

In case of a general available control I would use an Interface class and implement it into the form class module so you have a defined set of methods/functions/properties which is available in ALL supporting forms. I.e., if you create an interface using a property with a name like "MyListBox" you can use the form as generic reference (frm As Form) as parameter in your sub (if you need some generic informations about the form itself) and/or you can supply the interface object which is supplied by the calling sub or you use only the generic form reference and create the interface object in your sub.

Example (interface class):
Option Explicit

Public Property Get MyListBox() As ListBox
End Property

Open in new window

Then you implement this interface into your form code using the "Implements" keyword. Then you call your sub in the module using the generic form reference:
Public Sub MySub(frm As Form)
    Dim objIF As MyInterfaceClass
    Set objIF = frm

    With objIF
        Debug.Print .MyListBox.ListCount
    End WIth
End Sub

Open in new window

Now you have full IntelliSense support: Your sub uses all properties/methods/functions whatever which the Interface allows and additionally all standards which the generic Form reference offers. Moreover the form itself can use whatever it wants as name for the ListBox and it can decide if it wants to return something when this interface is used and which ListBox is returned.

Clean and simple, and reusable for any form. Moreover the interface forces the form to offer all needed elements in the same way as any other form which has the same interface implemented.


developingprogrammerAuthor Commented:
hi everybody! thanks for helping out and apologies for my late reply!

hi gustav, thanks once again for your kind words and guidance = ) i definitely want to follow Access' rules and i think honing each skill in Access is important but knowing when to use it is true mastery - something i'm still trying to move towards = )

hey Dale, great tip! thanks as always! i didn't know that by changing the syntax i can differentiate between a generic form and a specific form so Access will know what controls are in it. yes if i use this syntax i must definitely do a replacement before going live.

hi Joe, hrmm you know somehow your name always reminds me of the famous actor Joe Anderson in Twilight ha = ) yup Joe i definitely must be careful of inadvertently opening up unwanted forms!

hi Pat, thanks for your help! MVC was an enigmatic man whom i didn't understand until recently (a little better) and now he's helping me to write more reuseable code! haha = ) you raise a good point Pat in the sense decoupling code could be for performance / memory issues instead of reuseability. i didn't think of that, only thought of the reuseability part. i noted down coupling and cohesion and will read up on that once i have the time = )
developingprogrammerAuthor Commented:
hi Christian! thanks for your kind insights once again! = )

so far what i've been doing is focusing on code delivery to the client, like what you said before, no matter how snazzy my code is, the client won't see it. so my code has been very simple and organised.

however i think that perhaps 90% of the code should be KISS, whilst 8% slightly intermediate and 2% expert level. expert level meaning interfaces / events.

i've not touched on interfaces and events for awhile now and it took me quite a few reads to rekindle the understanding of your code above - basically because the form implements the interface that's why i can set the form to the dimensioned interface object in the sub.

i think events is really something very interesting i want to try but i've not come across the need for it yet - and admittedly it's because i haven't been

1) keeping the subject as active in my head,
2) keeping the implementation technique as fresh in my head
3) thus knowing where to spot the apt uses for it

this is definitely a subject i'll revisit soon after my currently rush project is done. when i think of interfaces i keep thinking about what you said - "can interfaces contain code? of course it can!" etc etc then you go on to explain how generic code can be put in the interface class but didn't want to confuse me last time haha = )
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now