Multiple controls with same event handler ... how?

[Kolyana]

At the moment I have something like this (subroutines called via the onClick event in an Access 2003 DB):

Private Sub cmdSortAsc_Click()
 On Error GoTo cmdSortAsc_Click_Err
  
      DoCmd.Echo False, "Sorting records ..."
    
         Screen.ActiveForm.AllowAdditions = False
             DoCmd.GoToControl "txtCustomerID"
             DoCmd.RunCommand acCmdSortAscending
 
                 Me![cmdSortDesc].Visible = True
                 Me![cmdSortDesc].SetFocus
                 Me![cmdSortAsc].Visible = False
                DoCmd.Echo True, ""
    
    Exit Sub
 
cmdSortAsc_Click_Exit:
             DoCmd.GoToRecord , , acFirst
   
     Exit Sub
 
cmdSortAsc_Click_Err:
     Resume cmdSortAsc_Click_Exit
 
End Sub
 
Private Sub cmdSortDesc_Click()
 On Error GoTo cmdSortDesc_Click_Err
      
       DoCmd.Echo False, "Sorting records ..."
  
     Screen.ActiveForm.AllowAdditions = False
         DoCmd.GoToControl "txtCustomerID"
         DoCmd.RunCommand acCmdSortDescending
   
             Me![cmdSortAsc].Visible = True
             Me![cmdSortAsc].SetFocus
             Me![cmdSortDesc].Visible = False
             DoCmd.Echo True, ""
    Exit Sub
 
cmdSortDesc_Click_Exit:
            DoCmd.GoToRecord , , acFirst
 
     Exit Sub
 
cmdSortDesc_Click_Err:
     Resume cmdSortDesc_Click_Exit
 
 
End Sub
Private Sub cmdSortAscComp_Click()
On Error GoTo cmdSortAscComp_Click_Err
     
     DoCmd.Echo False, "Sorting records ..."
   
        Screen.ActiveForm.AllowAdditions = False
            DoCmd.GoToControl "txtCompanyName"
            DoCmd.RunCommand acCmdSortAscending
                
                Me![cmdSortDescComp].Visible = True
                Me![cmdSortDescComp].SetFocus
                Me![cmdSortAscComp].Visible = False
                DoCmd.Echo True, ""
            Exit Sub
 
cmdSortAscComp_Click_Exit:
            DoCmd.GoToRecord , , acFirst
   
    Exit Sub
 
cmdSortAscComp_Click_Err:
    Resume cmdSortAscComp_Click_Exit
 
End Sub

I don't like this, because it's very similar code for each cmdButton placed on the form and my gut tells me that I could have one routine that handles all of the desired buttons.

This being the case, I need to be able to determine which object caused the event and I'm kinda going along these lines, but I have no idea if this is correct or not:

Private Sub cmdSort(ByVal sender As AccessObject)
    On Error GoTo cmdSort_Err
    
' do stuff
    
cmdSort_Exit:
    DoCmd.GoToRecord , , acFirst
    
    Exit Sub
    
cmdSort_Err:
     Resume cmdSort_Exit
End Sub

Each button does essentially the same thing, just sorting different columns through this type of code (which then varies on a per button/routine basis):

DoCmd.GoToControl "txtCompanyName"
            DoCmd.RunCommand acCmdSortAscending
                
                Me![cmdSortDescComp].Visible = True
                Me![cmdSortDescComp].SetFocus
                Me![cmdSortAscComp].Visible = False

Two buttons are utilized and toggled in visibility to sort in one direction or another, each calls different routines, and then this is repeated per column that can be sorted.

I can't help but feel that this is cludgey ... surely one routine could ascertain which button was pressed, therefore which column to sort and the requirement for buttons for each sort direction could be removed simply with the use of global variables that remember what's currently sorted and in what direction.

All of that probably makes little sense as I tend to use 1,000 words where 100 will do, but can anyone help me tidy this code as I feel that it can be?

[RobDog888]

Welcome to the Forums.

You can simplify the sorting by using only one button. Then if clicked you
change the caption of the button to the opposite direction of what it was
before. Then in the code you can have one routine to sort based upon the
button caption. No variables needed.

It looks like you will still need the procedure for cmdSortAscComp since it is
sorting on a different key field - txtCompanyName.


HTH

[Kolyana]

With multiple buttons (read: columns) being present, each sorting individual columns, how can I:

a) Call one routine from different buttons? (There are 14 columns in all and the buttons serve as column headers).

b) Have the routine pick up on the calling cmdButton (Ie, Know the object that caused the event).

I apologize for my ignorance, but my strengths are in C# and web programming, so while I may feel that this is possible, it is somewhat frustrating being foiled by my lack of simplistic knowledge.

[RobDog888]

Ok, you can have all your comand buttons click events make another call
to a general sort procedure, passing the command buttons name. Then in the
general sort procedure you can have variables for sort field and asc or desc.

[Kolyana]

So you have to pass a name? There isn't a reference to the object passed as part of the event? (I think here particularly of those languages that allow you to determine event type, the object that caused it and so forth).

If I have a general procedure to call from all buttons, is this then placed as a module?

Again, apologies, but using the interface and placing - say - cmdSort() as the property of the OnClick event produces the error "No such module", so what is the syntax to put here so that it will call a general subroutine?

[Calibra]

Just create an public function something like :

Public Function SomeFunc(button As String)

MsgBox button

End Function

and enter something like this where you normally select youre macro/vb code for youre onclick event :

=Somefunc(variable etc etc)

[Calibra]

So you have to pass a name? There isn't a reference to the object passed as part of the event? (I think here particularly of those languages that allow you to determine event type, the object that caused it and so forth).

If I have a general procedure to call from all buttons, is this then placed as a module?

Again, apologies, but using the interface and placing - say - cmdSort() as the property of the OnClick event produces the error "No such module", so what is the syntax to put here so that it will call a general subroutine?

You cannot call as SUB, it has to be an Function and it has to be an Public function (location can be an module or the class of the calling form)

syntax for calling see my previous post

[RobDog888]

I just had to add a comment.

's Calibra on the great explaination!

[Calibra]

Thanx

[salvelinus]

Or use vb.Net, where you can add any control to an event handler that takes the same arguments, a/o use delegation.

[TheOnly]

hi togheter,

have another interesting approach to the problem.

If you catch the event of the commandbuttons then you have only one Eventhandler for all or for which commandbutton you want.

The Example below is for all Commandbutton in a Form. In the attached ACCESS DB you will find also an approach for commandbuttons which you want.

Happy new Year

TheOnly

'---- put this in a Form
 
'define a collection
Private col As New Collection
'initialise the Commandbutton event class
Private NewCommandButton As New clsEvents
 
 
 
Private Sub Form_Close()
    'delete all references
    Set col = Nothing
    Set NewCommandButton = Nothing
End Sub
 
Private Sub Form_Load()
 
'load all the commandbutton of the form to one Eventhandler
 
    Dim ctl As Control
    'loop through all controls
    For Each ctl In Me.Controls
        'check if control is a commandbutton
        If ctl.ControlType = acCommandButton Then
            'set a new reference to the new Eventclass
            Set NewCommandButton = New clsEvents
            'store the commandbutton to the eventclass
            Set NewCommandButton.CmdBtn = ctl
            'store the eventclass in a collection to keep them alive
            col.Add NewCommandButton, ctl.Name
        End If
    Next ctl
 
End Sub
 
'--- put this in clsEvent class module
 
'catch the event of Commandbuttons
Private WithEvents m_CommandButton As CommandButton
 
'which commandbutton should have a common Eventhandling
Public Property Set CmdBtn(ctl As CommandButton)
    Set m_CommandButton = ctl
    'set the commandbutton event
    m_CommandButton.OnClick = "[Event Procedure]"
End Property
 
Private Sub Class_Terminate()
    Set m_CommandButton = Nothing
End Sub
 
 
Private Sub m_CommandButton_Click()
'place here the code
MsgBox "You clicked: " & m_CommandButton.Name & " with Caption " & m_CommandButton.Caption
 
End Sub