We write a lot of code in our Access databases. Because we write so much code, it is very helpful to have a uniform structure for all subs and functions. This helps immensely in reading code and in making sure the code runs smoothly and predictably. Every routine includes a copyright line, an exit point to clean up object variables, and error trapping using a select case structure.
Here is the code template we use:
Sub Yadda()
On Error GoTo Error
'Code goes here.
ExitPoint:
On Error Resume Next
Exit Sub
Error:
Select Case Err.Number
Case Else
Call ErrorTrap(Err.Number, Err.Description, "Yadda")
End Select
GoTo ExitPoint
End Sub
These same seventeen lines of code show up in every routine we write, and it seemed like a monumental waste of time to type this all by hand every time. Using the MySendKeys code from Dev Ashish, to work around the bugs in the SendKeys function, automated this process. You can get this code at his site.
In Access 97 you call RoutineCreator with an autokeys macro while sitting on a blank line between procedures in a code window. In 2K and XP, write a one-line function with a very short name that simply calls RoutineCreator. To generate the code template, simply go to the immediate window, type the name of your new function, and voila, there's your code.
Routine creator relies on the error trap routine that we use throughout our applications. If you do not use this error trapping method, you will have to modify the call to your error trap, and you'll definitely have to comment out the error trap line in this function to get it to compile.
Here is the code for Access 97:
Public Function RoutineCreator()
On Err GoTo Error
Dim strRoutineName As String
Dim strKey As String
Dim strRoutineType As String
strRoutineName = InputBox("Please enter the name of your routine. The public and private keywords are not" _
& " required but may be used. You may use either 'Sub' or 'Function'. Do not include any parameters" _
& " or a function's datatype.", "Input Routine Name")
If Left(strRoutineName, 7) = "public " Then
strKey = "Public "
strRoutineName = Mid(strRoutineName, 8)
ElseIf Left(strRoutineName, 8) = "private " Then
strKey = "Private "
strRoutineName = Mid(strRoutineName, 9)
End If
If Left(strRoutineName, 4) = "sub " Then
strRoutineType = "Sub"
strRoutineName = Mid(strRoutineName, 5)
ElseIf Left(strRoutineName, 9) = "function " Then
strRoutineType = "Function"
strRoutineName = Mid(strRoutineName, 10)
Else
Call MsgBox("The format of the declaration you entered was incorrect. Please try again.", _
vbOKOnly + vbExclamation)
Exit Function
End If
mySendKeys "{ENTER}{UP}{ENTER}" _
& "'{(}c{)}Copyright " & Date & " Jeremy Wallace, AlphaBet City Dataworks{ENTER}" _
& "'Contact Information: http://www.ABCDataworks.com, jeremy@ymerej.com{Enter}" _
& "on Error goto error{Enter 4}" _
& "ExitPoint:{Enter}" _
& "on error resume next{Enter 2}" _
& "exit {Enter}" _
& "Error:{Enter}" _
& "select case err.number{Enter}" _
& "case else{Enter}" _
& " call errortrap {(}err.num, err.description, """ & strRoutineName & """{)}{Right}" _
& "end select{Enter}" _
& "goto exitpoint{Enter}" _
& "end " & strRoutineType _
& "{Up 7} " _
& strRoutineType _
& "{Up 10}" _
& (strKey) & strRoutineType & " " & strRoutineName _
& "{UP}{Down 6}"
Exit Function
Error:
ErrorTrap Err.Number, Err.Description, "RoutineCreator"
End Function
Here is the code for Access 2000 and Access XP:
Public Function RoutineCreator()
On Err GoTo Error
Dim strRoutineName As String
Dim strKey As String
Dim strRoutineType As String
strRoutineName = InputBox("Please enter the name of your routine. The public and private keywords are not" _
& " required but may be used. You may use either 'Sub' or 'Function'. Do not include any parameters" _
& " or a function's datatype.", "Input Routine Name")
If Left(strRoutineName, 7) = "public " Then
strKey = "Public "
strRoutineName = Mid(strRoutineName, 8)
ElseIf Left(strRoutineName, 8) = "private " Then
strKey = "Private "
strRoutineName = Mid(strRoutineName, 9)
End If
If Left(strRoutineName, 4) = "sub " Then
strRoutineType = "Sub"
strRoutineName = Mid(strRoutineName, 5)
ElseIf Left(strRoutineName, 9) = "function " Then
strRoutineType = "Function"
strRoutineName = Mid(strRoutineName, 10)
Else
Call MsgBox("The format of the declaration you entered was incorrect. Please try again.", vbOKOnly + vbExclamation)
Exit Function
End If
mySendKeys "{END}{DOWN 2}{ENTER}" _
& "{ENTER}" _
& "'Contact Information: http://www.ABCDataworks.com, jeremy@ymerej.com{Enter}" _
& "on error goto error{Enter 5}" _
& "ExitPoint:{Enter 2}" _
& "on error resume next{Enter 3}" _
& "exit {Enter 2}" _
& "Error:{Enter 2}" _
& "select case err.number{Enter 2}" _
& "case else{Enter 2}" _
& " call errortrap {(}err.num, err.description, """ & strRoutineName & """{)}{Right}" _
& "end select{Enter 2}" _
& "goto exitpoint{Enter 2}" _
& "end " & strRoutineType _
& "{Up 7} " _
& strRoutineType _
& "{UP 10}{HOME}{BkSp 3}" & strKey & strRoutineType & " " & strRoutineName & "{ENTER 2} " _
& "{HOME}{DEL}{UP}" _
& "+{PGDN}"
Exit Function
Error:
ErrorTrap Err.Number, Err.Description, "RoutineCreator"
End Function
In addition, here's a sample of the tiny function needed to call this from the immediate window. All this does is save you the pain of having to type out RoutineCreator every time you want to create a routine. Please do not be tempted to simply rename RoutineCreator to something shorter.
Function RC()
Call RoutineCreator
End Function
