Skip to content

Refactoring code in a form

October 11, 2013

I know, not a very exciting title, and probably not a very exciting topic to most people, but to me, this is a little exciting.  But first, a little backdrop story.

As a programmer, one of the things that provides pleasure and satisfaction is looking at code and improving it.  Now improving is subjective, which believe it or not, much of programming *improvement is subjective.  One of the not so subjective markers of good programming is re-using code.  In other words, when code is replicated, over and over and there is very little difference in it, that is prime fodder for refactoring or improvement.

I have been working on an Access DB that is about 13 years old and has a lot of refactoring potential….

My customer emailed me and said “James, the kits are not showing on the check in form, can you look at?”.  So, I get to play investigator, because most of the time, my customers do not provide good detailed explanations about problems in their code or application.

When I opened the form in question, I noticed the SQL statement executed in the Form_Load() event was missing the current year identifying in the WHERE clause.  Nothing to complex here.  Update the WHERE clause, check the result, as my customer to verify and i am done.  Just before I was about to email my customer for the check, I noticed the sort buttons on the bottom of the form.  There were 3 of them.  When I clicked any of them, they all resulted in the same way, no records.  When I looked a little harder, I saw the same SQL repeated 3 additional times, in each of the sort buttons click event, like btnDistrict_sort_click() or btnKit_sort_click().  Well, I started to change the WHERE clause in the additional code, but my inner programmer was yelling at me, maybe even a whisper like this “James, it does not matter if this is MS-SQL, MySQL, coldfusion, php or any other language, the code is still stinky and needs to be streamlined.  James, there are 4 separate versions of almost identical SQL statements, the only difference is the sort statement., you know you should refactor that.”

So, I set off to create one master routine that was called by each of the events in the form.  The events would be

  1. form load
  2. district sort
  3. kit sort
  4. teacher sort
  5. date sort ( I added this one)

I created a master function called getRecs and added parameters for the sort option to be passed in from the calling function. Like this

In the event for the button click or form load, I removed all the redundant SQL and replaced it with a couple lines like this:

Private sub btnDate_sort_click()

     Dim stype as string

     stype = “date”

     Call getRecs(stype)

End Sub

And then in the next button click event, did the same, removing all the lengthy SQL and replacing with a couple statements, like this:

Private sub btnKit_sort_click()

     Dim stype as string

     stype = “kit”

     Call getRecs(stype)

End Sub

In the getRecs() function, I added arguments to accept the parameters being passed in from the calling functions and used them in the SQL statement like this:

Private Sub getRecs(ByType sortval as String)

 Dim x As String

    x = “SELECT DISTINCTROW tblBookings.BookingID, [District] & ‘ ‘ & [Building_Name] AS Expr1, “
    x = x & “[Teacher_LN] & ‘, ‘ & [Teacher_FN] AS Expr2, [Kit_Number] & ‘ ‘ & [Kit_Title] AS Expr3, “
    x = x & “tblBookings.Booking_Box, tblBookings.Booking_Returned, tblBookings.Booking_BeginWeek, “
    x = x & “tblBookings.Booking_EndWeek, tblSchoolYears.SchoolYear, tblSchoolYears.SchoolYearID”
    
    x = x & ” FROM tblSchoolYears”
    x = x & ” INNER JOIN (tblTeachers INNER JOIN ((tblDistricts”
    x = x & ” INNER JOIN (tblBuildings INNER JOIN tblTeacher_Building ON tblBuildings.BuildingID = tblTeacher_Building.BuildingID) ON tblDistricts.DistrictID = tblBuildings.DistrictID)”
    x = x & ” INNER JOIN (tblKits INNER JOIN tblBookings ON tblKits.KitID = tblBookings.KitID) ON tblTeacher_Building.Teacher_BuildingID = tblBookings.Teacher_BuildingID) ON tblTeachers.TeacherID = tblTeacher_Building.TeacherID)”
    x = x & ” ON tblSchoolYears.SchoolYearID = tblBookings.SchoolYearID”
    
    x = x & ” WHERE (((tblBookings.Booking_Returned) = No) AND ((tblBookings.Deleted) = No) “
    x = x & ” AND ((tblSchoolYears.SchoolYearID) IN(16) AND tblBookings.Booking_BeginWeek < ” & whichrecs & ” ))  “
    
    ‘what order – sortval,
    If sortval = “k” Then
        x = x & “ORDER BY [Kit_Number] “
    ElseIf sortval = “t” Then
        x = x & “ORDER BY [Teacher_LN] “
    ElseIf sortval = “date” Then
        x = x & “ORDER BY tblBookings.Booking_BeginWeek “
    Else
        x = x & “ORDER BY [District] “
    End If

End Sub

In the future, like next year, or when someone else looks at this code, they will only have to update 1 SQL statement, not 4 or 5.

Here is a little snapshot of the controls I added to the form and what the form result looks like.

Image

Advertisements

From → MS Access, VBA

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: