Creating Pseudo Custom Controls with Class Modules (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:07
Joined
May 21, 2018
Messages
6,268
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 using a custom class you can encapsulate and hide behind the scenes all the complext functionality. You can extend the functions of a single control or more importantly have multiple controls appear to functions as if they are all working together. The trick is to right generic code so that the user never has to look or touch code "behind the scenes".
The true power of a class module is that you can trap all the events of every control of your "custom control". This allows the user to instantiate the class and get all the functionality with just a single line of code to pass in the arguments and initialize the code. There is no copying of code or building your own event procedures.

The way I use my "custom controls" is always the same. I just declare a module level variable of the custom class and initialize it in one line in the form's on load event. So an example would look like this.
Code:
Private NC As CustomNavigationControls
Private Sub Form_Load()
  Set NC = New CustomNavigationControls
  NC.Initialize Me.cmdFirst, Me.cmdPrevious, Me.cmdNext, Me.cmdLast, Me.txtCount
End Sub

That is it. Regardless of the form, or the name of the controls the above is all you need for this example navigation control. You just have to add four command buttons and one textbox. A custom class should be like a black box. There user should not need to understand how it works, and should never have to modify the code to use it. You should provide the inputs and get the outputs.


I will demonstrate building this custom navigation form in two ways.
1. The user creates and places the 4 buttons and textbox on the main form and using a CustomClass
2. The user drops a subform on the mainform that has the buttons and the code.

In the 2nd example I will try to show that a forms module is a class module no different then a custom class, just sometimes we do not think it that way.

This code is not meant to be the perfect navigation control and there are lots of bells and whistles that could be added and error checking. This is just to demonstrate the process. Further there are plenty of examples for doing this with standard modules, and it can easily be done that way. This is to show the ease of reuse and flexibility of a class and how to make a class for a custom control.

In the image there are two "navigation controls". The buttons have different names and are formatted differently, but both as instantiated and initialized as shown above.
NavButtons.png



Step1: In vba create a new class module. I like to use clean names just like vba. So I do not use and prefix like "cls". Also many of my custom classes have custom collections just like vba (Control, Controls, TableDef / TableDefs) so I use that paradigm for naming as well. I called this "CustomNavigationControl"

Step2: For the controls that make up the custom navigation control, I have to trap their events and the parent form events. At the top of the class I will add a class variable for all controls involved in the custom control and prefix them withevents so that the class can trap their events.

Code:
Private WithEvents m_FirstButton As Access.CommandButton
Private WithEvents m_PreviousButton As Access.CommandButton
Private WithEvents m_TextRecordCounter As Access.TextBox
Private WithEvents m_NextButton As Access.CommandButton
Private WithEvents m_LastButton As Access.CommandButton
Private WithEvents m_ParentForm As Access.Form

Class variables are almost always private. These are the "properties" of the class. If you want to expose them (which I will not demonstrate here) then you would normally use Let, Get, and Set procedures. However, yes you can make these public and expose but for several reasons that is not normally done. These private variables can be used throughout the class

3. Create an event to pass in your arguments. Normally in a "custom control" I am passing in the controls from the form and other starting arguments. I always call it "initialize", but it can be whatever you want. In this procedure you set you class variables to the arguments.

Code:
Public Sub Initialize(FirstButton As Access.CommandButton, _
                      PreviousButton As Access.CommandButton, _
                      NextButton As Access.CommandButton, _
                      LastButton As Access.CommandButton, _
                      TextRecordCounter As Access.TextBox)

   Set m_FirstButton = FirstButton
   Set m_PreviousButton = PreviousButton
   Set m_NextButton = NextButton
   Set m_LastButton = LastButton
   Set m_TextRecordCounter = TextRecordCounter
   Set m_ParentForm = m_FirstButton.Parent

   'Ensure that events are raised
   ConfigureEvents

End Sub

4. In the initialize ensure that the controls you are referencing will raise the events you need. For a control to raise event it has to have the word "[event procedure]" in the event property. This is the same whenever you build a form. You can have an event procedure, but if the property is blank that procedure will never happen. In this example I know that each button needs to raise the onClick event and the form needs to raise the onCurrent event. I usually have a procedure called configure events, but you could do it all in the initialize. You can trap as many events as you want for each control.
Code:
Private Sub ConfigureEvents()
   m_FirstButton.OnClick = "[Event Procedure]"
   m_PreviousButton.OnClick = "[Event Procedure]"
   m_NextButton.OnClick = "[Event Procedure]"
   m_LastButton.OnClick = "[Event Procedure]"
   m_ParentForm.OnCurrent = "[Event Procedure]"
End Sub
You could also do this manually in the controls. However if you forget to do this or do not do it manually in the controls, you will be very frustrated because nothing will happen and it will be hard to figure out why.

5. If the controls are defined using with events they appear in the drop down and you can easily build event procedures.
I have not yet built the procedures but I know that I want to move in a direction based on the trapped event for a button, and update the record count on the current event of the form
Code:
Private Sub m_FirstButton_Click()
  Move "First"
End Sub
Private Sub m_PreviousButton_Click()
  Move "Previous"
End Sub
Private Sub m_NextButton_Click()
  Move "Next"
End Sub
Private Sub m_LastButton_Click()
  Move "Last"
End Sub
Private Sub m_ParentForm_current()
  UpdateRecordCounter
End Sub

6. The rest of the code really is not related to the techniques of building a class module for a custom control. The rest is just standard code. If you understand the above them you can build a "custom control"
The only thing that is really class related is the use of the "m_ParentForm". Since this is a class level variable set in the initialize procedure it can be used within the class. FYI, a Form is the parent to a control on the form. So in the initialize I just picked one of the buttons to set the m_ParentForm. (Note this will fail if the button is on a tab control and that is not handled in this code)
But here is the code
Code:
'---------------------------------------------------------- Procedures -----------------------------------------------------------
Private Sub Move(Direction As String)
  Dim rs As Recordset
  Set rs = m_ParentForm.Recordset
  Select Case Direction
    Case "First"
       rs.MoveFirst
    Case "Next"
       If GetRecordCount = (rs.AbsolutePosition + 1) Then
         MsgBox "Last Record"
       Else
         rs.MoveNext
       End If
    Case "Previous"
       If rs.AbsolutePosition = 0 Then
         MsgBox "First Record"
       Else
         rs.MovePrevious
       End If
    Case "Last"
      rs.MoveLast
  End Select
  UpdateRecordCounter
End Sub

Private Function GetRecordCount() As Long
  Dim rs As DAO.Recordset
  Set rs = m_ParentForm.RecordsetClone
  If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    GetRecordCount = rs.RecordCount
  End If
End Function
Private Sub UpdateRecordCounter()
  Dim rs As DAO.Recordset
  Dim pos As Long
  Dim reccount As Long
  Set rs = m_ParentForm.Recordset

  reccount = GetRecordCount
  pos = rs.AbsolutePosition + 1
  m_TextRecordCounter.Value = pos & " of " & reccount
End Sub

To use this code on any form drop your buttons on the form. At the top of the form's code declare a CustomNavigationControl variable and call the initialize procedure in the Form's load event.
Code:
Private CustNavCont As New CustomNavigationControls
Private Sub Form_Load()
  CustNavCont.Initialize Me.cmdF, Me.cmdP, Me.cmdN, Me.cmdL, Me.txtCount
End Sub

I will demo the subform in next installment.
 

Attachments

  • DemoCustomControl.accdb
    764 KB · Views: 224
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:07
Joined
May 21, 2018
Messages
6,268
The complete class for the above
Code:
Option Compare Database
Option Explicit

Private WithEvents m_FirstButton As Access.CommandButton
Private WithEvents m_PreviousButton As Access.CommandButton
Private WithEvents m_TextRecordCounter As Access.TextBox
Private WithEvents m_NextButton As Access.CommandButton
Private WithEvents m_LastButton As Access.CommandButton
Private WithEvents m_ParentForm As Access.Form




Public Sub Initialize(FirstButton As Access.CommandButton, _
                      PreviousButton As Access.CommandButton, _
                      NextButton As Access.CommandButton, _
                      LastButton As Access.CommandButton, _
                      TextRecordCounter As Access.TextBox)

   Set m_FirstButton = FirstButton
   Set m_PreviousButton = PreviousButton
   Set m_NextButton = NextButton
   Set m_LastButton = LastButton
   Set m_TextRecordCounter = TextRecordCounter
   Set m_ParentForm = m_FirstButton.Parent
  
   'Ensure that events are raised
   ConfigureEvents

End Sub


Private Sub ConfigureEvents()
   m_FirstButton.OnClick = "[Event Procedure]"
   m_PreviousButton.OnClick = "[Event Procedure]"
   m_NextButton.OnClick = "[Event Procedure]"
   m_LastButton.OnClick = "[Event Procedure]"
   m_ParentForm.OnCurrent = "[Event Procedure]"
End Sub


'------------------------------------------------------------ Trap Events ------------------------------------------------------------------------------------------
Private Sub m_FirstButton_Click()
  Move "First"
End Sub
Private Sub m_PreviousButton_Click()
  Move "Previous"
End Sub
Private Sub m_NextButton_Click()
  Move "Next"
End Sub
Private Sub m_LastButton_Click()
  Move "Last"
End Sub
Private Sub m_ParentForm_current()
  UpdateRecordCounter
End Sub

'---------------------------------------------------------- Procedures -----------------------------------------------------------
Private Sub Move(Direction As String)
  Dim rs As Recordset
  Set rs = m_ParentForm.Recordset
  Select Case Direction
    Case "First"
       rs.MoveFirst
    Case "Next"
       If GetRecordCount = (rs.AbsolutePosition + 1) Then
         MsgBox "Last Record"
       Else
         rs.MoveNext
       End If
    Case "Previous"
       If rs.AbsolutePosition = 0 Then
         MsgBox "First Record"
       Else
         rs.MovePrevious
       End If
    Case "Last"
      rs.MoveLast
  End Select
  UpdateRecordCounter
End Sub

Private Function GetRecordCount() As Long
  Dim rs As DAO.Recordset
  Set rs = m_ParentForm.RecordsetClone
  If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    GetRecordCount = rs.RecordCount
  End If
End Function
Private Sub UpdateRecordCounter()
  Dim rs As DAO.Recordset
  Dim pos As Long
  Dim reccount As Long
  Set rs = m_ParentForm.Recordset
 
  reccount = GetRecordCount
  pos = rs.AbsolutePosition + 1
  m_TextRecordCounter.Value = pos & " of " & reccount
End Sub
You can now add features to the class and just replace the class inside you application. All forms using this class would be updated.
So for example you could add the feature the if at the end or beginning the appropriate buttons are greyed out. You can add to the textbox where you can input a record number and navigate to it.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:07
Joined
May 21, 2018
Messages
6,268
Part 2: Using a subform as a "custom control"
The advantage of using a subform is that you can format all the controls in a consistent manner and then just insert the subform. This can be a disadvantage if you want a different look on different forms.

1. Step 1 create and format the subform.
Here is an image of the subform (in back) and embedded in the form in front.
NavSubform.png


2. Since the buttons and textbox are properties of the subform there is no need to pass them in as arguments. The only class variable needed is for the parent form. You can trap the click event directly and use withevents just for the parent forms current event. You do not have to call an initialize event just use the subforms onLoad event.


Code:
Private WithEvents m_ParentForm As Access.Form
'------------------------------------------------------- Subforms Load Event
Private Sub Form_Load()
  Set m_ParentForm = Me.Parent
  UpdateRecordCounter
End Sub
'------------------------------------------------------------ Trap Events ------------------------------------------------------------------------------------------
Private Sub cmdFirst_Click()
  MoveRecord "First"
End Sub

Private Sub cmdLast_Click()
  MoveRecord "Last"
End Sub

Private Sub cmdNext_Click()
  MoveRecord "Next"
End Sub

Private Sub cmdPrevious_Click()
  MoveRecord "Previous"
End Sub


Private Sub m_ParentForm_current()
  UpdateRecordCounter
End Sub
'---------------------------------------------------------- Procedures -----------------------------------------------------------
Private Sub MoveRecord(Direction As String)
  Dim rs As Recordset
  Set rs = m_ParentForm.Recordset
  Select Case Direction
    Case "First"
       rs.MoveFirst
    Case "Next"
       If GetRecordCount = (rs.AbsolutePosition + 1) Then
         MsgBox "Last Record"
       Else
         rs.MoveNext
       End If
    Case "Previous"
       If rs.AbsolutePosition = 0 Then
         MsgBox "First Record"
       Else
         rs.MovePrevious
       End If
    Case "Last"
      rs.MoveLast
  End Select
  UpdateRecordCounter
End Sub

Private Function GetRecordCount() As Long
  Dim rs As DAO.Recordset
  Set rs = m_ParentForm.RecordsetClone
  If Not rs.EOF Then
    rs.MoveLast
    rs.MoveFirst
    GetRecordCount = rs.RecordCount
  End If
End Function
Private Sub UpdateRecordCounter()
  Dim rs As DAO.Recordset
  Dim pos As Long
  Dim reccount As Long
  Set rs = m_ParentForm.Recordset
 
  reccount = GetRecordCount
  pos = rs.AbsolutePosition + 1
  Me.txtCount.Value = pos & " of " & reccount
End Sub
 

ysdai

Member
Local time
Today, 10:07
Joined
Nov 28, 2019
Messages
46
This is fabulous stuff. I have bookmarked this page.
Thank you so much!(y)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:07
Joined
May 7, 2009
Messages
16,116
this is nothing New.
there are "Old" thread that i have made similar Demo.
and it is more versatile that it can be used in any Form/Subform or sub-subform.
see this demo using subform and the navigation control is in the Main form.
 

Attachments

  • customNavigationButton.accdb
    644 KB · Views: 137

Sun_Force

Active member
Local time
Tomorrow, 02:07
Joined
Aug 29, 2020
Messages
397
@MajP
I really appreciate putting this thread up. I had written several classes, but never withEvents. It solved a million questions I had and never dared to ask.
Thanks again.

@arnelgp
I started learning classes using several of your demos a while back. Thanks for sharing your experience. But I had to fight with your code to understand how you've end up with that result. Here, @MajP started from a blank database and explained how to achieve the goal step by step. It's a great help for beginners like me to understand why a class has been setup this way.

Million thanks to both of you.
 

Sun_Force

Active member
Local time
Tomorrow, 02:07
Joined
Aug 29, 2020
Messages
397
I was overwhelmed with what I learned here, and as a practice I started writing my own classes. I didn't even noticed I was up till morning.

In both suggested methods above, specific objects are passed to the class. So their events are available.
What if I need to write a class and manipulate every object on a form? Different forms have different count of objects.

As an example let's imagine I need to show a messagbox when any label that its name starts with lblSort is clicked?
How does such a class look like?

Any kind of advice is much appreciated.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:07
Joined
May 21, 2018
Messages
6,268
I didn't even noticed I was up till morning
I hope you do not celebrate Christmas. if not that is kind of sad to be playing with Access instead of doing Christmas Eve activities.
Remember a form's module is a class. A form's class has a Controls collection as a property of the form. You can loop this collection. You can check the type of control returned and do something based on what type. There are a ton of examples out there. Normally people will tag the controls to group them or identify which ones to manipulate. See @isladogs example, but you can search for many more.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:07
Joined
May 21, 2018
Messages
6,268
As an example let's imagine I need to show a messagbox when any label that its name starts with lblSort is clicked?
That sounds like you are trying to do the "pseudo control array". If you read the thread on Events that I referenced this is easily done using a common function to handle multiple control events.

But to summarize. If you have lots of controls and want each to open a messagebox. Instead of "[Event Procedure]" in the onClick event and writing lots of event procedures, you would put the name of the common function.
=PopUpMsgBox()
That single function traps all the click events

Code:
Public Function PopUpMsgBox()
  'If the controls where labels they could be the active control so simply
  msgbox "you selected " & activecontrol.name
end function

If you are using labels they can not be the active control. Some how you have to pass that in to the function. Each control would need in its event some parameter
=PopUpMsgBox("lblOne")
=PopUpMsgBox("lbltwo") etc.
The technique is demoed here.
 
Last edited:

Sun_Force

Active member
Local time
Tomorrow, 02:07
Joined
Aug 29, 2020
Messages
397
@MajP
Thanks for the replies and worrying about my Christmas. I don't want to make it a social/politic conversation, so I don't go into details. But as a brief reply, we who live in east side of Asia, are used to limit our activities because of the Pandemic. we believe the health of ourselves and the society is much more important than a night drinking with friends. So staying at home and having no event for Xmas is not a pain. Maybe that's why if you add the daily new infection of the whole 10 countries, it's still less than 1/1000 of any single country in Europe or America.

I still haven't read your links in details, but jumping from one to another, tells me it's not what I meant. I will go through them and will come back if they didn't satisfy me need.
Maybe my messagebox question was a stupid example. I have a lot of Continuous Forms that are used to show search results of different tables.
The labels for each field are instructed of three parts. lbl + Srch + Fieldname (lblSrchOrdersPK or lblSrchDelivery etc)
Clicking on each label fires a function that receives the label name and and identifies which field to be sorted, sorts the field Asc Or Desc and adds a triangle to its caption to show which field and which direction is sorted. Just like windows explorer. Reading your article above, I tried to change it to a class as a test. But apparently since label names are different in each form, I asked how to pass a set of objects which each time differs.

As I said I haven't read your links yet. I take a look and will come back again.

Thanks again for sharing your wisdom.
 
Last edited:

Users who are viewing this thread

Top Bottom