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


VB Code:
  1. Private Sub cmdSortAsc_Click()

  2.  On Error GoTo cmdSortAsc_Click_Err

  3.   

  4.       DoCmd.Echo False, "Sorting records ..."

  5.     

  6.          Screen.ActiveForm.AllowAdditions = False

  7.              DoCmd.GoToControl "txtCustomerID"

  8.              DoCmd.RunCommand acCmdSortAscending

  9.  
  10.                  Me![cmdSortDesc].Visible = True

  11.                  Me![cmdSortDesc].SetFocus

  12.                  Me![cmdSortAsc].Visible = False

  13.                 DoCmd.Echo True, ""

  14.     

  15.     Exit Sub

  16.  
  17. cmdSortAsc_Click_Exit:

  18.              DoCmd.GoToRecord , , acFirst

  19.    

  20.      Exit Sub

  21.  
  22. cmdSortAsc_Click_Err:

  23.      Resume cmdSortAsc_Click_Exit

  24.  
  25. End Sub

  26.  
  27. Private Sub cmdSortDesc_Click()

  28.  On Error GoTo cmdSortDesc_Click_Err

  29.       

  30.        DoCmd.Echo False, "Sorting records ..."

  31.   

  32.      Screen.ActiveForm.AllowAdditions = False

  33.          DoCmd.GoToControl "txtCustomerID"

  34.          DoCmd.RunCommand acCmdSortDescending

  35.    

  36.              Me![cmdSortAsc].Visible = True

  37.              Me![cmdSortAsc].SetFocus

  38.              Me![cmdSortDesc].Visible = False

  39.              DoCmd.Echo True, ""

  40.     Exit Sub

  41.  
  42. cmdSortDesc_Click_Exit:

  43.             DoCmd.GoToRecord , , acFirst

  44.  
  45.      Exit Sub

  46.  
  47. cmdSortDesc_Click_Err:

  48.      Resume cmdSortDesc_Click_Exit

  49.  
  50.  
  51. End Sub

  52. Private Sub cmdSortAscComp_Click()

  53. On Error GoTo cmdSortAscComp_Click_Err

  54.      

  55.      DoCmd.Echo False, "Sorting records ..."

  56.    

  57.         Screen.ActiveForm.AllowAdditions = False

  58.             DoCmd.GoToControl "txtCompanyName"

  59.             DoCmd.RunCommand acCmdSortAscending

  60.                 

  61.                 Me![cmdSortDescComp].Visible = True

  62.                 Me![cmdSortDescComp].SetFocus

  63.                 Me![cmdSortAscComp].Visible = False

  64.                 DoCmd.Echo True, ""

  65.             Exit Sub

  66.  
  67. cmdSortAscComp_Click_Exit:

  68.             DoCmd.GoToRecord , , acFirst

  69.    

  70.     Exit Sub

  71.  
  72. cmdSortAscComp_Click_Err:

  73.     Resume cmdSortAscComp_Click_Exit

  74.  
  75. 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:

VB Code:
  1. Private Sub cmdSort(ByVal sender As AccessObject)

  2.     On Error GoTo cmdSort_Err

  3.     

  4. ' do stuff

  5.     

  6. cmdSort_Exit:

  7.     DoCmd.GoToRecord , , acFirst

  8.     

  9.     Exit Sub

  10.     

  11. cmdSort_Err:

  12.      Resume cmdSort_Exit

  13. 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):

VB Code:
  1. DoCmd.GoToControl "txtCompanyName"

  2.             DoCmd.RunCommand acCmdSortAscending

  3.                 

  4.                 Me![cmdSortDescComp].Visible = True

  5.                 Me![cmdSortDescComp].SetFocus

  6.                 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?