Solved Is it possible to create custom controls?

[ysdai]

Let's say I have a textbox that allows only numeric input, or OK/Cancel buttons with preset formats and Default/Cancel properties, any kind of reusable controls that I will have on several of my forms. Is it possible to save those controls somewhere, along with its formatting and code, so that I can just pull them out when I need them instead of creating the same controls from scratch every time?
I remember doing that in Excel before, but can you do it in Access?

 

[oleronesoftwares]

i doubt it, but lets wait for inputs from other members of the forum

 

[sonic8]

Unfortunately, it's not possible to create custom controls with Access. There a couple of options towards that, but none is exactly what you ask for.
Just some rough ideas...
- I believe it is possible to define template settings for controls. E.g. you define a button template and whenever you put a button somewhere it will have the properties defined in that template. (I'm not sure if this is correct and don't know how to do it. I hope someone else can clarify.)
- You can create ActiveX controls in another development environment, such as C++ or .Net and then use these controls in Access. These would satisfy your requirements, but they cannot be created with Access alone and there are limitations where you can use them in Access (e.g. not on continuous forms).
- You can create a wizard (like those for option groups or combo boxes), that configures the properties of a certain control the way you want them. This just helps with settings standard properties, it does not provide additional functionality on its own.
- You can build composite controls. You write your own (class) module that provides additional functionality for certain types of controls. Whenever you add a standard control to a form, you need to write a couple of lines of code to hook it up with the extended functionality in your (class) module.

 

[CJ_London]

Easy enough to have a form with a bunch of controls on it where you preset the properties such as colour/font/border/validation/etc. The problem comes when you have code behind the control events. A way to overcome this is in a standard module include a function that does what you need

function txtDoSomethingStandardOnClick()
    msgbox "textbox clicked"
end function

and in your control textbox click event properties where you would normally see [Event Procedure] put

=txtDoSomethingStandardOnClick()

your function can usually use things like screen.activecontrol to identify where the call came from

This only works if your code does not need to reference the parameters which comes with the standard event such as those associated with mouse events or the cancel parameter with the double click event.

If it does need those parameters then you need to use a class module and assign the class to the control. This works better if you have a standard form rather than individual controls (such as a search form) and assign the form to the class. The class can have preset properties and you can use the let/set properties to make modifications if required.

See this link for an example of how a class module is used to provide any continuous form with the ability to allow the users to move and resize columns in much the same way as you can in a datasheet. Include it as a library and you can try it on your own projects, just needs one line of code

continuous form that allows users to resize and reorder columns like a datasheet

I developed this because datasheets have some useful functionality, but limitations as well - among them Datasheets can't show controls such as buttons, images, attachments, etc Datasheets can't show a record over multiple rows within the detail section Datasheets don't have header and footer...

www.access-programmers.co.uk

 

[arnelgp]

based on your description on post#1, it can be accomplished using Class module.

 

[MajP]

You cannot build a real custom control like you can in other development environment, but you can fake it.
One way is to build a subform with different controls on it. You write the code so it can be dropped on any form and provide functionality. Here is a recent one. It is a time line showing failures. That time line subform can be reused in any application with a little modification.

Visualising of machine status/log

if you can give a mock-up sample of the graph.

www.access-programmers.co.uk

This one simulates the control of a multi value field and again can be reused anywhere

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

Or you can write a class module that captures all the events and can give functionality to a group of controls. This method is a little different in that you do not provide the subform, instead the user adds the generic controls and passes them to the class.
This one allows you to drop a listbox on a form and a few other buttons. It creates a sortable listbox where you can move items up, down, to the top, to the bottom, drag and drop, and move with a double click

MS Access: custom control built in another language

the code is old and there is a small change you need to make in several functions, e.g. fIsScrollBar, there is a line which is used to identify the scrollbar for a window If fGetClassName(hWnd_VSB) = "scrollBar" Then this needs to be changed to If fGetClassName(hWnd_VSB) = "NUIscrollBar"...

www.access-programmers.co.uk

Or one that has been real popular are find as you type comboboxes and listboxes. This class then turns any combobox or listbox into the ability to find the record and filter as you type.

Solved - FAYT for Multi Column combo code from MajP question

I have used the great code from MajP FAYT on a few forms and some work perfectly and a few have an issue. I read the post Populating Combobox with a lot of data and the link in there. The issues I am experiencing are When I refresh the underlying table (I click a button on my form and it...

www.access-programmers.co.uk

There may be hundreds of lines of code in these classes or subforms, but to reuse them takes no modification or new code. You simply instantiate the class and pass new arguments.

 

[isladogs]

It really depends on what you mean by custom reusable controls. For example I have the following reusable items (some of which I got from other developers). None of them were done using ActiveX or class modules

• progress bar
• slider control
• speedometer type dashboard control
• toggle on/off slider button (I got that from CJ-L)...etc

You can also reuse themes which may be what @sonic8 was alluding to.
Or you can take the process further and create your own template databases (ACCDT) or reusable application parts which can be plugged in to other databases as required

 

[MajP]

The most extreme example of this is a complete tree view made out of only MS Form controls

Excel, Access: An MSForms (all VBA) treeview

Custom VBA Treeview control for Access, Excel, and Word, compatible with Mac and 64-bit Office, unaffected by ActiveX security settings, free to use and modify.

jkp-ads.com

I use it here and other threads

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...

www.access-programmers.co.uk

 

[The_Doc_Man]

In the sense of "any shape, any kind of interaction with user, any arbitrary event not defined by Access" - no. You've gotten some feedback on this from others. Here's my two cents' worth.

You were talking about "reusable." If your putative controls have only ordinary Access abilities, just uniquely selected, I used the idea of a template form that had samples of particular controls pre-defined on it and I simply copied the template to make a new form and copied each control. Using the concept of template forms, I estimate I saved about 40% to 60% (depending on complexity). My forms had predictable controls for functions like SAVE (record), CANCEL (Undo record), CREATE (new record), DELETE (current record), and a few other functions. The event code behind each button was built but not "fleshed out completely" so I still had to customize each event a little bit. I had a bunch of other things partly automated as well, such as highlighting & formatting code that I could use to make particular fields REALLY stand out when they were in focus.

 

[MajP]

One more. Here is a "speedometer" graph. You pass in a percentage and it displays as graph. You can even animate it.

Speedometer Dashboard

Hi All, I am wanting to add a dashboard form with multiple speedometers to display progress and project health. Is there an example that someone could share with me. Cheers Mick

www.access-programmers.co.uk

 

[ysdai]

Wow, thanks for all your responses. It will take me a while to digest.
For now, I guess I'm more inclined with Doc's idea of a template form, because it's something I can work on right away. I imagine I will be creating a blank form, populate it with my reusable controls, properly formatted and coded, and when I create a new form, I will have this template opened alongside and just copy/paste. Sort of like a toolbox. I'm going to try this first and see how it goes.

I will also read all the posted links to see what other alternatives I have.

In the long run, however, I think the most efficient and professional approach to this would probably be control classes, as some of you have suggested. But I have very little experience with classes, and haven't the slightest idea how to begin. Can some elaborate on this (custom control class) a little bit, like maybe point me to a working sample so I can study how it works?

 

[The_Doc_Man]

If you take my approach, you can build in the scaffold of a lot of form-level events, too. Using the template, you fill in everything that is generically likely to be used. And if you find that oddball form or two, you can delete parts you don't need. But out of about 40-45 forms, maybe 2 or 3 didn't need full event management. I'm talking, of course, of having Form_Load, Form_Current, _BeforeUpdate, _AfterUpdate, and Form_Error just in case I had an unhandled trap in my code.

My templates had a lot of baggage, but it was mostly helpful.

The pitfalls? Takes a while to get settled into a good template. The benefits? The more you put into the template, the easier it gets to customize the forms. The first one is a pain in the patootie. The second one isn't quite as bad. By the fifth one, you know where to look for things that need to be done.

 

[ysdai]

Don't forget, when you copy and paste the control, the event procedures do not come along for the ride. You have to copy them separately and then to make sure they connect to the new form controls, open each event procedure.

Yes, I was just coming to that. The funny thing, though, is that sometimes just copying code over from template to new form doesn't automatically register the event, and the code won't be invoked at all. For example, if you copy the code from the line Private Sub btn_Click() all the way to End Sub and paste it over, sometimes the code in there will be ignored and nothing will happen when btn is clicked. The proper way would be to go to the [Events] page in the control properties, find the [On Click] even, click the [...], and paste just the inner code when the code editor opens and generates the procedure for you. Really took me a lot of time to figure out why the code wouldn't run. I don't know if this is a bug or what, but it's kind of annoying.

I read somewhere that if you use macros instead of VBA, then they will always be attached to the controls wherever you copy. But I'm really clumsy when it comes to macros. They're just confusing.

Other than that, things look pretty good for now, and I'm still working on it in my Access time.

In the meantime, I also find MajP's FAYT quite interesting. In my project I have a lot of those FAYT combo boxes and textboxes myself, not created in the most efficient ways of course, and certainly not instantiated out of class modules. This will be great learning material for me. Some of those other implementations from the above links are beyond my level at this point, so I'll start with this one first to learn about class modules. Thanks MajP.

 

[ysdai]

You can also register them the way I said in #14 which is probably easier if you have controls with code in multiple events. Copy and paste the code for all the control events. Then click the build button for the event and it will open to the code you pasted.

Reusable code is very important but I find that too many procedures that people implement through classes can more simply be coded using a function into which you pass a form or control object or possibly a recordset. depending on whether the function operates on a control or a form. There is no inherent value of coding a class unless it is actually going to be reusable without modification.

Yes, common procedures and functions with parameters is also the approach I use most of the time. I have quite a few universal procedures stored in modules for various tasks in many of my forms, something like yours. This works fine for me for now, but I keep getting recommendations to use class modules with methods and properties instead. But I haven't yet seen a class module example that I can implement in my reality. It's something I have to learn anyway, so this is my opportunity.

 

[CJ_London]

Or use the =eventname() as suggested in post#4

e.g.

=FAYT("tblName","fldName")

all you have to do then is change the parameters to the relevant table and field names for your new form

 

[MajP]

The class module demo creating a custom navigation button can be easily done using a standard module. The big difference is the user cannot simply do this

Private CustNavCont As New CustomNavigationControls
Private Sub Form_Load()
  CustNavCont.Initialize Me.cmdF, Me.cmdP, Me.cmdN, Me.cmdL, Me.txtCount
End Sub

Anyone can do the above and pass in the button and txtbox. In a standard module you will have to copy paste and code a the event procedures. For this example you can easily do this with a standard module, and maybe I will demo the same thing. The big issue is not in writing the class or standard module it is the ease of reuse and flexibility. However, if you understand building class modules and object oriented programming it is actually far easier to write the class then do the same in a standard module for these kind of things.

 

[ysdai]

Here's an example of a useful Class module created by MajP

Creating Pseudo Custom Controls with Class Modules

This demo is in response to a recent thread on creating "custom controls" https://www.access-programmers.co.uk/forums/threads/is-it-possible-to-create-custom-controls.320996/ Unlike other development environments access does not have a way to make a user defined or custom control. However, but...

www.access-programmers.co.uk

This is great stuff! Thanks to MajP for opening my eyes to a new dimension.
Now I have a few questions:

1. This class module defines the functions of the controls. So before I use this class module I need to manually places all of the controls into the form, format them to my liking, and name them to match those initialized in the code, before I run this code, right?
2. In addition to defining the functions, I can also refine the code to include formatting properties of the controls such as color, effect, font, or even location (in this case, where in the form to place those navigation buttons). Then all I'd have to do during form design is throw in the required controls anywhere in the form, and when the code initializes them, they will know where to go and how they should look. Am I right?
3. If I initialize a group of controls in a form to a single custom control class, then I have something like a control array?

I'm sorry if they sound silly, but I'm really new to this.

 

[MajP]

1. This class module defines the functions of the controls. So before I use this class module I need to manually places all of the controls into the form, format them to my liking, and name them to match those initialized in the code, before I run this code, right?

In the first example you do need to add and format your controls on each form. In the subform example you do it once. You can name your controls whatever you want. If you look at the two examples the controls have different names. You pass in your four controls by reference not by a name.

In addition to defining the functions, I can also refine the code to include formatting properties of the controls such as color, effect, font, or even location (in this case, where in the form to place those navigation buttons). Then all I'd have to do during form design is throw in the required controls anywhere in the form, and when the code initializes them, they will know where to go and how they should look. Am I right?

You could, but now you are kind of defeating the purpose of having generic code. So now all instantiations would always get that look and feel you would not have a way to customize. If I was going to do that I would probably have properties as well to be configurable. So I could have a location property that puts the navigation control in upper right or lower left. A font property that would be the font for all buttons and the textbox, a back color. In truth I think it would be simpler to format the buttons once and past as necessary.

However there are some classes that I build, that most definitely do all the formatting. Especially if the sizing and moving is tedious and time consuming.
This timeline example is a basically a custom class (using the subform Idea) and creates it by moving, showing, and sizing many controls.

Visualising of machine status/log

if you can give a mock-up sample of the graph.

www.access-programmers.co.uk

If I initialize a group of controls in a form to a single custom control class, then I have something like a control array?

Kind of, but not really. In this navigation button example I would say no. It is almost the opposite. In a control array each item of the array is indexed and its event is handled by a common event procedure. It is a lot of controls doing one event. More like an option group where each option does one thing, return a value. With this navigation buttons it is as if a single navigation control causes multiple events (move first, move previous, ...)

However, you could build a custom class that would more closely mimic a control array.
However, in Access the easiest way to mimic a control array is with a common function serving as an event handler. See long discussion here.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

 

[ysdai]

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

I will definitely go through this entire thread.

Thank you so much for your examples. Now I think I have my two-fold approach to this problem: a template form that defines their looks (for copy & paste) and class modules/common procedures for their behaviors.

I wonder if I could also do the same with forms, like a form class modules or something, to create common forms of different functions. But I'll worry about that later.

 

[MajP]

For forms you are probably talking templates and not classes.