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

jkp-ads.com: Treeview control

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

 

[Pat Hartman]

Here is common code I use in lots of forms. You can call the code two ways.
1. If you don't have any custom code for the event, you can use
=funcName(Me) in the appropriate event.
=CommonClose(Me)
2. If you do have custom code that you need in addition to the common code, you have to create an event procedure for the code and then call it:

Private Sub Form_Close()
    '' your custom code
    Call CommonClose(Me)
End Sub

The common code goes into a standard code module so it is available from all forms. You pass in the form object

Option Compare Database
Option Explicit

Public Sub CommonClose(frm As Form)
On Error GoTo ErrProc
    If bForceClose = True Then
        Exit Sub        'do not run close code
    End If
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If IsNull(frm.OpenArgs) Then
        DoCmd.OpenForm "Switchboard"
    Else
        DoCmd.OpenForm frm.OpenArgs
    End If
    
    bForceClose = False     'reset variable
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2102   'bad open args form name
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub
Public Sub CommonReturn(frm As Form)
On Error GoTo ErrProc
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.Close acForm, frm.Name
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3709, 3021, 2501  'caused when save is cancelled
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub

Public Sub CommonSave(frm As Form)
On Error GoTo ErrProc
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3709, 3021, 2501, 3071  'caused when save is cancelled
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
            Resume Next
    End Select
End Sub
Public Sub CommonOpen(frm As Form)
On Error GoTo ErrProc
    If Forms!frmLogin!txtBELocation = "Production" Then
        frm.txtBELocation.Visible = False
    Else
        frm.txtBELocation.Visible = True
        frm.txtBELocation = Forms!frmLogin!txtBELocation
    End If
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2450   'trap some but that happens when app closes
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub
Public Sub CommonExit(frm As Form)

On Error GoTo ErrProc
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    
    DoCmd.Close acForm, frm.Name, acSaveNo
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3709, 3021, 2501, 3071  'caused when save is cancelled
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
            Resume Next
    End Select
End Sub

 

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

but I keep getting recommendations to use class modules with methods and properties instead.

When you get a shiny new hammer for Christmas, you always want to use it for everything. It's fun, it's cool, it makes me sound smart. There are places where a class module will make sense but if you like a lot of coding, don't wait for the right opportunity, just make EVERY module a Class module because you can. Pointless but possible. But you'll be ready if you ever run into a situation where a Class module is actually a superior solution.

I don't want to discourage you from experimenting if you have time. Someday, you might actually find that you need a Class module and you will be ready to make it. I would concentrate on using "with events". That is most likely going to be what you actually need when you need one. Before you go down the rabbit hole of teaching yourself about Class modules, make sure you understand the programming concepts of coupling and cohesion. Understanding them will keep you from making poor design choices. Think about the bug we've been talking about for a week. MS released an update that broke Access. To their credit, they came up with a fix in three days but the problem has been rippling around the country/world as various PCs get the update automatically installed and their apps can no longer work in a multi-user environment. One of my clients got hit on Monday. This is yet another case of making a change to procedure x that breaks procedure y. Understanding coupling and cohesion will keep you from making pathological connections that cause random procedures to break when you change something totally unrelated. EVERY time you reuse code you run this risk. So, although it is important to reuse code for efficiency and to prevent data anomalies, the boundaries need to be clear and clean.

Procedures must be loosely coupled - i.e. the minimum number or arguments exchanged and they must also be cohesive - i.e. they do one thing and only one thing. For example the module can produce payroll checks but just because it produces checks, doesn't mean that it makes sense to make the module also produce vendor payments.

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

 

[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.