[AccessD] Re Max of 20 values

The below works much (MUCH) faster by not binding the max-time display
textbox to a function (after looping to find it) and using requery. If you
know the control's name, just put the variable's value there directly.


So

For Each Ctrl In aTxt.Parent.Controls
  If TypeOf Ctrl Is Access.TextBox Then
    If Ctrl.Tag = "Max Time Value" Then
      Ctrl.Requery
      Exit For
    End If
  End If
Next

Becomes


aTxt.Parent.Controls("ShowMaxValueHere") = Glob_Dbl_What_Is_Current_Max


-----Original Message-----
From: William Benson (VBACreations.Com) [mailto:vbacreations at gmail.com] 
Sent: Saturday, July 23, 2011 9:27 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Re Max of 20 values

Going back to the original inquiry. John's right, class modules are the way
to go. 

Assumptions:  
Tag property on all controls which can contain a time value = "Time Values"
I have an additional textbox on my form with controlsource = "=
HighestValue()"
     -- that is a public function -- see below


'IN THE FORM'S CODE MODULE
Option Compare Database
Option Explicit
Dim colTimeValueControls As New Collection
Dim MyclsSeriesItem As clsTimeSeriesItem

Private Sub Form_Load()
Dim Ctrl As Control

Glob_Dbl_What_Is_Current_Max = 0
Glob_Str_Which_Control_Has_Max = ""

For Each Ctrl In Me.Controls
   If ControlIsTimeValue(Ctrl, TimeValueTag) Then
      Set MyclsSeriesItem = New clsTimeSeriesItem
      Set MyclsSeriesItem.Txt = Ctrl
      MyclsSeriesItem.Txt.AfterUpdate = "[Event Procedure]"
      colTimeValueControls.Add MyclsSeriesItem
    End If
Next
End Sub

'IN A Class Module named clsTimeSeriesItem
Option Compare Database
Option Explicit
Public WithEvents aTxt As Access.TextBox

Private Sub aTxt_AfterUpdate()
Dim bTestAllControls As Boolean
Dim Ctrl As Control
bTestAllControls = False
If IsNumeric(aTxt.Value) Then
  If CDbl(Nz(aTxt.Value, 0)) >= Glob_Dbl_What_Is_Current_Max Then
      Glob_Dbl_What_Is_Current_Max = CDbl(aTxt.Value)
      Glob_Str_Which_Control_Has_Max = aTxt.Name
  ElseIf Glob_Str_Which_Control_Has_Max <> aTxt.Name Then
    'DO NOTHING, some other control has the max value anyway
  Else   'It is numeric, it was the max, and it no longer is as high as the
max was
    bTestAllControls = True
  End If
ElseIf Glob_Str_Which_Control_Has_Max = aTxt.Name Then
  bTestAllControls = True
Else
  'We don't care if it went non-numeric, it didn't hold the max value prior
to now
End If

If bTestAllControls Then
  Glob_Str_Which_Control_Has_Max = ""
  Glob_Dbl_What_Is_Current_Max = 0
  For Each Ctrl In aTxt.Parent.Controls
    If ControlIsTimeValue(Ctrl, TimeValueTag) Then
      If IsNumeric(Nz(Ctrl.Value, "")) Then
        If CDbl(Nz(Ctrl.Value, 0)) > Glob_Dbl_What_Is_Current_Max Then
          Glob_Dbl_What_Is_Current_Max = CDbl(Nz(Ctrl.Value, 0))
          Glob_Str_Which_Control_Has_Max = Ctrl.Name
        End If
      End If
    End If
  Next
End If
For Each Ctrl In aTxt.Parent.Controls
  If TypeOf Ctrl Is Access.TextBox Then
    If Ctrl.Tag = "Max Time Value" Then
      Ctrl.Requery
      Exit For
    End If
  End If
Next
End Sub

Public Property Set Txt(ByVal ControlThatChanged As Control)
  If TypeOf ControlThatChanged Is Access.TextBox Then
    Set aTxt = ControlThatChanged
  End If
End Property

Public Property Get Txt() As Access.TextBox
  If Not aTxt Is Nothing Then
    Set Txt = aTxt
  End If
End Property


'IN A STANDARD MODULE
Public Glob_Str_Which_Control_Has_Max As String
Public Glob_Dbl_What_Is_Current_Max As Double

Public Function TimeValueTag() As String
  TimeValueTag = "Time Values"
End Function
Public Function ControlIsTimeValue(Ctrl As Control, strTagToLookFor As
String) As Boolean
If TypeOf Ctrl Is Access.TextBox Then
   ControlIsTimeValue = (Ctrl.Tag = strTagToLookFor)
End If
End Function

Public Function HighestValue()
HighestValue = Glob_Dbl_What_Is_Current_Max
End Function