Diary of a class module

[Steve Jorgensen]

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!?

[Steve Jorgensen]

Correction to last class example:

On Thu, 27 May 2004 09:59:35 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

....
<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 & varArgument & Suffix
End Sub
</clsClauseText>

[WindAndWaves]

Dear Steve

That indeed is a pretty sharp solution. Thank you for that.

It seems to me that the Class Modules are not required, but very powerful in
simplifying things.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:nd********************************@4ax.com...

Correction to last class example:

On Thu, 27 May 2004 09:59:35 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

...
<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 & varArgument & Suffix
End Sub
</clsClauseText>

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

[Steve Jorgensen]

Any language can perform any task, but some code of the types of code
organization to remove duplication, keep code and its data together, etc., are
not possible without class modules. Many other desirable organizational tasks
are not possible without the full compliment of OOP features that VB/VBA does
not have, but it's good to use all the tools we do have where they do provide
a clear benefit.

On Thu, 27 May 2004 22:18:18 +1200, "WindAndWaves" <ac****@ngaru.com> wrote:

Dear Steve

That indeed is a pretty sharp solution. Thank you for that.

It seems to me that the Class Modules are not required, but very powerful in
simplifying things.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:nd********************************@4ax.com.. .

Correction to last class example:

On Thu, 27 May 2004 09:59:35 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

...
<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 & varArgument & Suffix
End Sub
</clsClauseText>

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

[WindAndWaves]

Yeah, that is funny actually.

A lot of people criticise me for using MS Access. However, as you say, it
does not really matter what you use, it is all about what you do with it.

I use my own way to keep things simple and I am sure that everyone has their
own tricks and theories.

Thanks again.

Nicolaas

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:h4********************************@4ax.com...

Any language can perform any task, but some code of the types of code
organization to remove duplication, keep code and its data together, etc., are not possible without class modules. Many other desirable organizational tasks are not possible without the full compliment of OOP features that VB/VBA does not have, but it's good to use all the tools we do have where they do provide a clear benefit.

On Thu, 27 May 2004 22:18:18 +1200, "WindAndWaves" <ac****@ngaru.com> wrote:

Dear Steve

That indeed is a pretty sharp solution. Thank you for that.

It seems to me that the Class Modules are not required, but very powerful insimplifying things.
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:nd********************************@4ax.com.. .

Correction to last class example:

On Thu, 27 May 2004 09:59:35 GMT, Steve Jorgensen <no****@nospam.nospam> wrote:

...
<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 & varArgument & Suffix
End Sub
</clsClauseText>

---
Please immediately let us know (by phone or return email) if (a) this emailcontains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

---
Please immediately let us know (by phone or return email) if (a) this email
contains a virus
(b) you are not the intended recipient
(c) you consider this email to be spam.
We have done our utmost to make sure that
none of the above are applicable. THANK YOU
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004