how to get intellisense for form controls when typing in module

Posted on 2014-02-15
Last Modified: 2014-02-24
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!
Question by:developingprogrammer
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 39862411
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.

LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 39862572
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.
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 50 total points
ID: 39863094
"However, if you use the syntax:"
Of course, it's possible that might accidentally open another instance of the Form ...

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 39863147

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.
LVL 35

Assisted Solution

PatHartman earned 100 total points
ID: 39863465
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.
LVL 24

Accepted Solution

Bitsqueezer earned 150 total points
ID: 39882132

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.



Author Comment

ID: 39884590
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 = )

Author Comment

ID: 39884600
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 = )

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question