Access Hacks

Hack #75. Shrink Your Code with Subroutines

Say goodbye to long and difficult-to-maintain code by placing repetitive processing into subroutines.

All applications live and grow. Functionality begets functionality. As users start banging away at your first delivered application, they scream for more features. As you add these features, the amount of code can grow. Often, routines get copied, and then a couple of literals, variable names, or criteria get changed. You end up with code that has a number of similar routines.

Example 8-3 shows three identical routines, with the exception that each addresses a different state.

Example 8-3. Multiple nearly identical routines

Sub get_NY_records( ) ' 'get New York customers ' Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim recset As ADODB.Recordset Set recset = New ADODB.Recordset recset.Open "Select * From Customers Where State='NY'", conn Do Until recset.EOF ''Process records here recset.MoveNext Loop recset.Close Set recset = Nothing End Sub Sub get_CT_records( ) ' 'get Connecticut customers ' Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim recset As ADODB.Recordset Set recset = New ADODB.Recordset recset.Open "Select * From Customers Where State='CT'", conn Do Until recset.EOF ''Process records here recset.MoveNext Loop recset.Close Set recset = Nothing End Sub Sub get_MA_records( ) ' 'get Massachusetts customers ' Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim recset ...