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!? 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>
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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;...
|
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....
|
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....
|
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,...
|
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...
|
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...
|
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...
|
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...
|
by: Kemmylinns12 |
last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
|
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...
|
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...
|
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...
|
by: antdb |
last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine
In the overall architecture, a new "hyper-convergence" concept was...
|
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.
...
|
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...
|
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...
| |