472,336 Members | 1,254 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,336 software developers and data experts.

Diary of a class module

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!?
Nov 13 '05 #1
4 1400
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>

Nov 13 '05 #2
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
Nov 13 '05 #3
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


Nov 13 '05 #4
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
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mike | last post by:
Hi all, I am writing a database using SQL as the back end and Access as the front end. My project is to create a rota for when people have to...
2
by: groups | last post by:
If you haven't read the book, nor watched the movie, "Harry Potter and the Chamber of Secrets". Harry Potter finds a diary with nothing written in...
0
by: jonathan.beckett | last post by:
Hi all, Just announcing the latest version of my blog / diary / journal script. You can get it (and read all about it) at;...
1
by: OHM \( One Handed Man \) | last post by:
Hi Guys, Im working on something which requires a method of managing entries for resources in ASP.NET and I need to find a good solution for it....
0
by: OHM \( One Handed Man \) | last post by:
Hi Guys, Im working on something which requires a method of managing entries for resources in ASP.NET and I need to find a good solution for it....
0
by: jonathan.beckett | last post by:
Version 1.9.9f of the PluggedOut Blog script is now available for download. PluggeOut Blog is a free, open source blog/diary/journal script,...
1
by: SandipAdhikary | last post by:
hello sir, I have faced a prob.how can i make a diary control using .net with c#. exactly microsoft diary control. explanation=: there is...
8
by: shrimpeh | last post by:
Hi im currently messing around with creating a diary/booking system for a leisure centre. And im stuggling with how I can display all the data. The...
1
by: TC | last post by:
Hi, I have an app that is going to require recurring diary entries, it's a relatively simple app but I have no idea how to go about the recurring...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.