Form Controls as Pseudoarrays (1 Viewer)

Solo712

Registered User.
Local time
Today, 01:10
Joined
Oct 19, 2012
Messages
775
Hi everyone,
Every so often I see requests on Access boards inquiring about a way to streamline events for multiple controls of the same kind, so they are executed as a single function. Surprisingly, most answers are negative in the sense that VBA (unlike VB) doesn't know control arrays. Then someone may come and say, no it's quite possible and offers a solution managing the controls and events associated with them via a class module. This of course works (in theory), but such approach is costly in terms of overhead.
There is another solution which strikes me as much simpler. It is based on the observation that VBA controls on a form come with a numeric suffix which can be managed so they become a set of ordinals 1 to x, with no breaks. Such collection of controls can easily easily be utilized to provide an identifying argument to a function called by any event. So, one function to many, many controls of a kind. No formal collection needs to be created and controls encapsulated, because the controls have a numeric identifier already, which can be used to control the associated events individually.
A classical example of a form with a considerable number of controls would be a monthly calendar. You need 37 rectangles, and within them 37 combo/listboxes if you want to track multiple activities within that day. To implement the controls as pseudoarrays and link them to the event functions all you need is something like this:

Code:
Private Sub InitializeEventsforControlArrays()
   Dim ctl As Control, i As Long

   For i = 1 To 37
     Set ctl = Me.Controls("List" & i)
     ctl.OnClick = "=GetList(" & i & ")"
     ctl.OnMouseDown = "=DEmpty(" & i & ")"
     '
     Set ctl = Me.Controls("Rct" & i)
     ctl.OnClick = "=GetDay(" & i & ")"
     '
   Next i

   Set ctl = Nothing

The event functions then identify the calling control and relate the event to it exclusively binding it (via workday offset) to the particular day of the month.

Few notes on editing the listboxes:

1) You need to establish the active day in the month. (By default, current month is displayed and today is the active day. It is marked red on top of box.) You can establish another day as active in one of two ways:
a) by clicking on any part of the listbox that is not an entry. The active day will be displayed in the right bottom corner and the top of the box. Then you click the Edit Day Entries button at the bottom. All the day's entries will be in the recordset.
b) by clicking directly on an entry of the listbox, in which case you will be allowed to access that record only.
In both cases, the single calendar entry form will appear.
2) Relocation of entries to another day (even in another month) is permitted except for new entries. They must be made in the "active day".
3) The editing form for a single calendar entry will tell you when it gets dirty by displaying an pencil icon in the upper right corner. While the form is in edit mode, you may not exit. You either have to "Save" the record or "Undo" the changes.

Have fun with it.

Jiri
 

Attachments

  • CalendarTest.zip
    171.2 KB · Views: 76
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:10
Joined
Jul 9, 2003
Messages
13,568
This idea was explained by ChrisO a few years back. Chris provided a very useful thread which I revisit on occasions.

Chris also demonstrates a module version of the code:-


 

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:10
Joined
May 21, 2018
Messages
5,690
I think you may confuse people with the term "pseudo array" since that is not really what you are doing. Because a real control array has other attributes.
What you are really doing is creating a common parameterized function to serve as an event handler instead of building individual event handling procedures.
I explain this in detail here, how a single function can trap multiple events. I do agree that many people are not aware how easy this is to have a single function handle multiple events.
I would edit the thread and do away with the "pseudo array' term and explain this more as creating a common function to serve as an event handler. I would rename the thread "Assigning a Common Event Handler to a Group of Controls.
 

isladogs

CID VIP
Local time
Today, 06:10
Joined
Jan 14, 2017
Messages
15,569
As stated above, this approach has been around for a long time. It can indeed simplify repetitive coding.
I have used a similar numbering system for many years in several of my own applications including my revamped version of the Better Date Picker originally created by Brendan Kidwell back in 2003
 
Last edited:

Solo712

Registered User.
Local time
Today, 01:10
Joined
Oct 19, 2012
Messages
775
Thanks, Colin.

Guys, I made no claim to a historical discovery in the OP. It's just that a number of posters at AWF seem unaware that this coding style is available and very effective for a task like creating an event calendar. Take e.g. the collection of calendars and date pickers that MajP posted last November. https://www.access-programmers.co.u...endar-controls-and-calendar-resources.315041/
None of the respected authors featured - not even the venerable Allen Browne - availed themselves of the possibility of. creating the event links by initializing code - surely a big labour saver.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:10
Joined
Jul 9, 2003
Messages
13,568
creating the event links by initializing code - surely a big labour saver.

I prefer the Multi Select Approach

I make and sell MS Access products. One particular product failed when Microsoft removed their calendar control from MS Access. I did consider replacing the Microsoft calendar control with someone else's calendar control, but I couldn't justify including someone else's work in a product I was profiting from. I shelved my "Between Dates Selection Form", and it is still on the shelf!

It shouldn't be "on the Shelf" because I did go ahead and create my own calendar control from scratch. It's just getting the time and inclination to introduce it to the "Between Dates Selection Form".

The first step in creating your own calendar control is to find and explore other people's examples, and I did notice as Solo712 indicates, that some of the coding in the existing calendar controls is a bit repetitive.

Although I'm not as brilliant as many other coders on this site, I do pride myself on being able to loop through a control collection. That's the approach I used with my calendar control. As indicated by Solo712 it's a laborious process, creating numerous similar subroutines, it's much more rewarding if you can loop through.

The "ordinal" method shown by Solo712 intrigues me, but I'm not sure I can figure out how to use it in my calendar control. I do prefer (I'm happier with) using multiple select (See Here):-

multiple select
to add a particular function to multiple event procedure in one fell swoop.

In an early version of my calendar control / date picker I identified the command buttons holding the date values (42 off) with an X in their tag properties. I had this 42 button grid. I like to see all 42 filled in with dates, include the previous and next month Dates. It makes for a better looking calendar. I changed the design to use the first part of the button name to identify it as a calendar button, thus leaving the tag property free to identify the button as being a date in the previous, current or next month. I can tell this because the the buttons still have an X in their tag properties, left over from this first iteration of the code!

Code in the next post as I ran in to the 10,000 character limit!

There's a video here:-

Nifty Date Picker for ALL Versions of Access - Nifty Access​

showing how you can build your own calendar control based on this code, ideal for past and future proofing. It should work in any version of MS Access...


Access World Forum Members can download a Free copy of the "Nifty Date Picker" Here:-


Nifty Date Picker - MS Access - Use Coupon Code:- bqu488f - to get a free copy...
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 06:10
Joined
Jul 9, 2003
Messages
13,568
Code:
Option Compare Database
Option Explicit

'This is the latest version of the code, Updated Jan the 27th 2020
'Fixed a bug with highlighting the date, removed a bit of redundant code.

'I have removed all my custom error handling code from this free sample.

'######## -------------- Variables for the Property Statements ------- ########
Private mctrlInitialDate As Control
Private mTempDate As Date 'Temporary Date

'############## ---------------- Property Statements ----------------- ########
Property Set prpCtrlInitialDate(ctrlInitialDate As Control)
    Set mctrlInitialDate = ctrlInitialDate
End Property      'prpCtrlInitialDate Let

Property Get prpCtrlInitialDate() As Control
   Set prpCtrlInitialDate = mctrlInitialDate
End Property      'prpCtrlInitialDate Get

Property Let prpTempDate(ByVal datTempDate As Date)
    mTempDate = datTempDate
End Property      'prpTempDate Let

Property Get prpTempDate() As Date
    prpTempDate = mTempDate
End Property      'prpTempDate Get

'############## ------------------ Control Events -------------------- ########
Private Sub btnToday_Click()
    prpTempDate = Date
    Me.txtMth = prpTempDate
    Call fDisplayMth(prpTempDate)
    Call fSetAndClose(True)
End Sub      'btnToday_Click

Private Sub btnYrPlus1_Click()
    prpTempDate = DateAdd("yyyy", 1, prpTempDate)
    Me.txtMth = prpTempDate
    Call fDisplayMth(prpTempDate)
End Sub      'btnYrPlus1_Click

Private Sub btnYrMinus1_Click()
    prpTempDate = DateAdd("yyyy", -1, prpTempDate)
    Me.txtMth = prpTempDate
    Call fDisplayMth(prpTempDate)
End Sub      'btnYrMinus1_Click

Private Sub btnMthMinus1_Click()
    prpTempDate = DateAdd("m", -1, prpTempDate)
    Me.txtMth = prpTempDate
    Call fDisplayMth(prpTempDate)
End Sub      'btnMthMinus1_Click

Private Sub btnMthPlus1_Click()
    prpTempDate = DateAdd("m", 1, prpTempDate)
    Me.txtMth = prpTempDate
    Call fDisplayMth(prpTempDate)
End Sub      'btnMthPlus1_Click

Private Sub Form_Load()
    Me.Caption = Me.Name & " Ver B_1a "
End Sub      'Form_Load

Private Sub txtMth_AfterUpdate()
     prpTempDate = Me.txtMth
    Call fDisplayMth(prpTempDate)
End Sub      'txtMth_AfterUpdate

'############## ----------------- Primary Routines ------------------ ########
Public Function fSetUp()

        If Not IsDate(prpCtrlInitialDate) Then  'Handle No Date Supplied
            prpTempDate = Date
            prpCtrlInitialDate = Date
        Else
            prpTempDate = prpCtrlInitialDate    'Separate the Object Date from the Processed Date
        End If

        Me.txtMth = prpTempDate

        Call fDisplayMth(prpTempDate)

End Function      'fSetUp

Private Function fHighlightCurDate(ByVal dTempDate As Date, ByVal dInitialDate As Date)

Dim intInitialDay As Integer
intInitialDay = DatePart("d", dInitialDate)

    'If the Day matches the month and year, then highlight it
    If (DatePart("m", dTempDate) = DatePart("m", dInitialDate)) And (DatePart("yyyy", dTempDate) = DatePart("yyyy", dInitialDate)) Then
        Dim Ctrl As Control
           For Each Ctrl In Me.Controls
               Select Case Ctrl.ControlType
                   Case acCommandButton
                       If Ctrl.Caption = intInitialDay And Ctrl.Tag = "C" Then
                           Ctrl.FontSize = 11
                           Ctrl.ForeColor = RGB(255, 255, 255)
                           Ctrl.BackColor = RGB(30, 144, 255)
                       End If
               End Select
           Next Ctrl
    End If
End Function      'fHighlightCurDate

Private Function fDisplayMth(dInitialDate As Date)

Dim Ctrl As Control
Dim dFirstDayOfMth As Date

Dim intInitialDay As Integer
Dim intLastDayPrevMth As Integer

Dim intBtnTag As Integer            'Increment from 1 through to 42
Dim intPrevMth As Integer           'Increment the Previous Months Dates
Dim intInitialMth As Integer        'Increment the Alpha Months Dates (Current, Passed in Mth)
Dim intFollowMth As Integer         'Increment the Following Months Dates

dFirstDayOfMth = DateSerial(Year(dInitialDate), Month(dInitialDate), 1)

intLastDayPrevMth = DatePart("d", dFirstDayOfMth - 1)
intInitialDay = DatePart("d", dInitialDate)

intInitialMth = 1
intFollowMth = 1

intPrevMth = intLastDayPrevMth - Weekday(dFirstDayOfMth, vbMonday) + 2   'fWeekDayOffSet(dFirstDayOfMth) + 2

    For Each Ctrl In Me.Controls
        Select Case Ctrl.ControlType
            Case acCommandButton

                'Check to see if it is a Command Button in the 1 to 42 Grid (Its Prefix will be btnD)
                    If Left(Ctrl.Name, 4) = "btnD" Then

                    'Setup Form
                        Ctrl.BackColor = RGB(147, 202, 221)
                        Ctrl.ForeColor = RGB(16, 37, 63)

                            '1 through 42 Counter
                                intBtnTag = intBtnTag + 1

                                    'Fill the Previous Month
                                        If Weekday(dFirstDayOfMth, vbMonday) > 0 And intPrevMth <= intLastDayPrevMth Then
                                            Ctrl.Tag = "P"
                                            Ctrl.BackColor = RGB(219, 238, 244)
                                            Ctrl.Caption = intPrevMth               'Set the Captions of the Command Buttons to the Previous month
                                            intPrevMth = intPrevMth + 1
                                        End If

                                    'Fill the Initial Month
                                        If intBtnTag >= Weekday(dFirstDayOfMth, vbMonday) Then
                                            Ctrl.Caption = intInitialMth              'Set the Captions of the Command Buttons to the Alpha month
                                            'intInitialMth
                                            Ctrl.Tag = "C"

                                                intInitialMth = intInitialMth + 1 'this is a poor name! it's a DAY not a month (Incremented = Incr)
                                        End If

                        'Fill the Following Month
                            If intInitialMth > fLastDayOfMth(dFirstDayOfMth) + 1 Then
                                Ctrl.Tag = "F"
                                Ctrl.BackColor = RGB(219, 238, 244)
                                Ctrl.Caption = intFollowMth                     'Set the Captions of the Command Buttons to the Following month
                                intFollowMth = intFollowMth + 1
                            End If
                    End If
      End Select
    Next Ctrl

    Call fHighlightCurDate(prpTempDate, prpCtrlInitialDate)

End Function      'fDisplayMth

Private Function fGetCaption() As String

'The Calendar displays the  3 dates sections, previous month, current month and Following month.
'A value is placed in the Tag property of the command button to identify which date section the button belongs to

Dim intday As Integer
intday = Screen.ActiveControl.Caption

    Select Case Screen.ActiveControl.Tag
        Case Is = "P"   'Previous Month
            prpTempDate = fDateBuilder(prpTempDate, -1, intday)
        Case Is = "C"   'Current Month
            prpTempDate = fDateBuilder(prpTempDate, 0, intday)
        Case Is = "F"   'Following Month
            prpTempDate = fDateBuilder(prpTempDate, 1, intday)
    End Select

Call fSetAndClose(True)

End Function      'fGetCaption

Private Function fDateBuilder(dTempDate As Date, intOffSet As Integer, intday As Integer) As Date
'Used in Function fGetCaption()

    Dim intYear As Integer
    Dim intMth As Integer

            dTempDate = DateAdd("m", intOffSet, dTempDate)
            intYear = DatePart("yyyy", dTempDate)
            intMth = DatePart("m", dTempDate)

    fDateBuilder = intYear & "/" & intMth & "/" & intday

End Function      'fDateBuilder

Private Function fSetAndClose(blnSetDate As Boolean)
'This is the Exit Routine
    If blnSetDate Then
        prpCtrlInitialDate = prpTempDate 'Set the Object Date to the Processed Date
        DoCmd.Close acForm, Me.Name
    End If
End Function     'fSetAndClose

'############## ----------------- Helper Routines ------------------ ########
Private Function fLastDayOfMth(dDate As Date) As Byte

'                                 DateSerial( year, month, day )
    fLastDayOfMth = DatePart("d", DateSerial(Year(dDate), Month(dDate) + 1, 1) - 1)

End Function      'fLastDayOfMth

''''CALL IT Like THIS
'''Dim strFrmName As String
'''    strFrmName = "NiftyDatePicker"
'''        DoCmd.OpenForm strFrmName
'''            With Forms(strFrmName)
'''                Set .prpCtrlInitialDate = Me.txtEndDate
'''                .fSetUp
'''                '.Caption = "I CAN CHANGE THE CAPTION"
'''            End With

This code is also displayed on my Website HERE:- https://www.niftyaccess.com/nifty-date-picker/ where it is highlighted nicely and easier to read...
 

GaDauBac

Member
Local time
Today, 13:10
Joined
Mar 22, 2021
Messages
42
Hi everyone,
Every so often I see requests on Access boards inquiring about a way to streamline events for multiple controls of the same kind, so they are executed as a single function. Surprisingly, most answers are negative in the sense that VBA (unlike VB) doesn't know control arrays. Then someone may come and say, no it's quite possible and offers a solution managing the controls and events associated with them via a class module. This of course works (in theory), but such approach is costly in terms of overhead.
There is another solution which strikes me as much simpler. It is based on the observation that VBA controls on a form come with a numeric suffix which can be managed so they become a set of ordinals 1 to x, with no breaks. Such collection of controls can easily easily be utilized to provide an identifying argument to a function called by any event. So, one function to many, many controls of a kind. No formal collection needs to be created and controls encapsulated, because the controls have a numeric identifier already, which can be used to control the associated events individually.
A classical example of a form with a considerable number of controls would be a monthly calendar. You need 37 rectangles, and within them 37 combo/listboxes if you want to track multiple activities within that day. To implement the controls as pseudoarrays and link them to the event functions all you need is something like this:

Code:
Private Sub InitializeEventsforControlArrays()
   Dim ctl As Control, i As Long

   For i = 1 To 37
     Set ctl = Me.Controls("List" & i)
     ctl.OnClick = "=GetList(" & i & ")"
     ctl.OnMouseDown = "=DEmpty(" & i & ")"
     '
     Set ctl = Me.Controls("Rct" & i)
     ctl.OnClick = "=GetDay(" & i & ")"
     '
   Next i

   Set ctl = Nothing

The event functions then identify the calling control and relate the event to it exclusively binding it (via workday offset) to the particular day of the month.

Few notes on editing the listboxes:

1) You need to establish the active day in the month. (By default, current month is displayed and today is the active day. It is marked red on top of box.) You can establish another day as active in one of two ways:
a) by clicking on any part of the listbox that is not an entry. The active day will be displayed in the right bottom corner and the top of the box. Then you click the Edit Day Entries button at the bottom. All the day's entries will be in the recordset.
b) by clicking directly on an entry of the listbox, in which case you will be allowed to access that record only.
In both cases, the single calendar entry form will appear.
2) Relocation of entries to another day (even in another month) is permitted except for new entries. They must be made in the "active day".
3) The editing form for a single calendar entry will tell you when it gets dirty by displaying an pencil icon in the upper right corner. While the form is in edit mode, you may not exit. You either have to "Save" the record or "Undo" the changes.

Have fun with it.

Jiri
Hi everyone,
Every so often I see requests on Access boards inquiring about a way to streamline events for multiple controls of the same kind, so they are executed as a single function. Surprisingly, most answers are negative in the sense that VBA (unlike VB) doesn't know control arrays. Then someone may come and say, no it's quite possible and offers a solution managing the controls and events associated with them via a class module. This of course works (in theory), but such approach is costly in terms of overhead.
There is another solution which strikes me as much simpler. It is based on the observation that VBA controls on a form come with a numeric suffix which can be managed so they become a set of ordinals 1 to x, with no breaks. Such collection of controls can easily easily be utilized to provide an identifying argument to a function called by any event. So, one function to many, many controls of a kind. No formal collection needs to be created and controls encapsulated, because the controls have a numeric identifier already, which can be used to control the associated events individually.
A classical example of a form with a considerable number of controls would be a monthly calendar. You need 37 rectangles, and within them 37 combo/listboxes if you want to track multiple activities within that day. To implement the controls as pseudoarrays and link them to the event functions all you need is something like this:

Code:
Private Sub InitializeEventsforControlArrays()
   Dim ctl As Control, i As Long

   For i = 1 To 37
     Set ctl = Me.Controls("List" & i)
     ctl.OnClick = "=GetList(" & i & ")"
     ctl.OnMouseDown = "=DEmpty(" & i & ")"
     '
     Set ctl = Me.Controls("Rct" & i)
     ctl.OnClick = "=GetDay(" & i & ")"
     '
   Next i

   Set ctl = Nothing

The event functions then identify the calling control and relate the event to it exclusively binding it (via workday offset) to the particular day of the month.

Few notes on editing the listboxes:

1) You need to establish the active day in the month. (By default, current month is displayed and today is the active day. It is marked red on top of box.) You can establish another day as active in one of two ways:
a) by clicking on any part of the listbox that is not an entry. The active day will be displayed in the right bottom corner and the top of the box. Then you click the Edit Day Entries button at the bottom. All the day's entries will be in the recordset.
b) by clicking directly on an entry of the listbox, in which case you will be allowed to access that record only.
In both cases, the single calendar entry form will appear.
2) Relocation of entries to another day (even in another month) is permitted except for new entries. They must be made in the "active day".
3) The editing form for a single calendar entry will tell you when it gets dirty by displaying an pencil icon in the upper right corner. While the form is in edit mode, you may not exit. You either have to "Save" the record or "Undo" the changes.

Have fun with it.

Jiri
I see an error when I execute
 

Attachments

  • upload(8).png
    upload(8).png
    84.9 KB · Views: 25

Users who are viewing this thread

Top Bottom