In response to recent questions on when to use class modules in Access VBA,
here's a real-world scenario of mine.
I had some code that needed to build a Where clause for a Select statement
based on 0 or more criteria (0 criteria means no Where clause). My initial
stab at the code looked something like this...
<Code>
Private Sub Foo()
Dim strSql As String
Dim strWhere As String
If Not IsNull(Me!txtTypeId) then
strWhere = "[TypeID]=" & Me!txtTypeId
End If
If Not IsNull(Me!txtNameContains) then
If Len(strWhere) > 0 Then strWhere = strWhere & " AND "
strWhere = "[Name] Like *" & Me!txtTypeId & "*"
End If
If Not IsNull(Me!txtDescripContains) then
If Len(strWhere) > 0 Then strWhere = strWhere & " AND "
strWhere = "[Description] Like *" & Me!txtDescripContains & "*"
End If
If Len(strWhere) > 0 Then strWhere = " WHERE " & strWhere
strSql = "SELECT * FROM tblFoo" & _
strWhere & " " & _
"ORDER BY [Name];"
...
End Sub
</Code>
I see that in each case, I have to check to see if there's already something
in strWhere, so I know if I have to append an " AND ", so I factor that out
into a function.
<DemoCode>
Private Sub Foo()
Dim strSql As String
Dim strWhere As String
If Not IsNull(Me!txtTypeId) then
AppendDelimited _
strWhere, _
"[TypeID]=" & Me!txtTypeId, _
" AND "
End If
If Not IsNull(Me!txtNameContains) then
AppendDelimited _
strWhere, _
"[Name] Like ""*" & Me!txtNameContains & "*""", _
" AND "
End If
If Not IsNull(Me!txtDescripContains) then
AppendDelimited _
strWhere, _
"[Description] Like ""*" & Me!txtDescripContains & "*""", _
" AND "
End If
If Len(strWhere) > 0 Then strWhere = " WHERE " & strWhere
strSql = "SELECT * FROM tblFoo" & _
strWhere & " " & _
"ORDER BY [Name];"
...
End Sub
Private Sub AppendDelimited( _
ByRef strComboundExpr As String, _
strAppendExpr As String, _
strDelimiter As String _
)
If Len(strComboundExpr) > 0 Then
strComboundExpr = strComboundExpr & strDelimiter
End If
strComboundExpr = strComboundExpr & strAppendExpr
End Sub
</DemoCode>
This is a big improvement, but there are still 2 obvious bits of duplication
we'd like to be able to remove, passing the delimiter, and passing the
variable to append to. Furthermore, if more than one procedure uses this
pattern, there is duplication of the bit about checking to see whether the
clause has any content to see whether to prepend the Where keyword. Later, in
some other code, I also had the need to build clauses with suffices such as "
IN(1,2,3,...) "
Here's the Class module solution I came up with:
<clsClauseText>
Option Compare Database
Option Explicit
' This class assists in the construction of clause strings
' such as the clauses in SQL Statements.
Public Prefix As String
Public Delimiter As String
Public Suffix As String
Public Value As String
' -- AddArgument --
' Adds a string to the list of arguments included in the clause being
' built.
'
' strArgument : The new argument string to add
'
Public Sub AddArgument(strArgument As String)
If Len(Value) > 0 Then
Value = _
Left$(Value, Len(Value) - Len(Suffix)) & _
Delimiter
Else
Value = Prefix
End If
Value = Value & strArgument & Suffix
End Sub
</clsClauseText>
And here's the example above, now using this new class module.
<DemoCode>
Private Sub Foo()
Dim strSql As String
Dim objWhereClause As New clsClauseText
' Always think before using "New", but it's fine here.
With objWhereClause
.Prefix = " WHERE "
.Delimiter = " AND "
If Not IsNull(Me!txtTypeId) then
.AddArgument "[TypeID]=" & Me!txtTypeId
End If
If Not IsNull(Me!txtNameContains) then
.AddArgument "[Name] Like ""*" & Me!txtNameContains & "*"""
End If
If Not IsNull(Me!txtDescripContains) then
.AddArgument _
"[Description] Like ""*" & Me!txtDescripContains & "*"""
End If
End With
strSql = "SELECT * FROM tblFoo" & _
objWhereClause.Value & " " & _
"ORDER BY [Name];"
...
End Sub
</DemoCode>
Next, if we take advantage of the fact that using + instead of & to
concatenate strings will result in Null if any of the arguments are Null, we
can take this yet another step.
<clsClauseText>
Option Compare Database
Option Explicit
' This class assists in the construction of clause strings
' such as the clauses in SQL Statements.
Public Prefix As String
Public Delimiter As String
Public Suffix As String
Public Value As String
' -- AddArgument --
' Adds a string to the list of arguments included in the clause being
' built.
'
' strArgument : The new argument string to add
'
Public Sub AddArgument(varArgument As Variant)
If IsNull(varArgument) Then Exit Sub
If Len(Value) > 0 Then
Value = _
Left$(Value, Len(Value) - Len(Suffix)) & _
Delimiter
Else
Value = Prefix
End If
Value = Value & strArgument & Suffix
End Sub
</clsClauseText>
And here's the new usage example.
<DemoCode>
Private Sub Foo()
Dim strSql As String
Dim objWhereClause As New clsClauseText
' Always think before using "New", but it's fine here.
With objWhereClause
.Prefix = " WHERE "
.Delimiter = " AND "
.AddArgument "[TypeID]=" + Me!txtTypeId
.AddArgument "[Name] Like ""*" + Me!txtNameContains & "*"""
.AddArgument _
"[Description] Like ""*" + Me!txtDescripContains & "*"""
End With
strSql = "SELECT * FROM tblFoo" & _
objWhereClause.Value & " " & _
"ORDER BY [Name];"
...
End Sub
</DemoCode>
Now, here's a question. Which of these would you rather write? A better
question, then is which one of these would you rather read!?
