By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,127 Members | 1,366 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

User-defined class for SQL Select expressions

FishVal
Expert 2.5K+
P: 2,653
Hereby I'm proposing a way of convinient work with properties containing SQL Select statements, particulary RowSource property of ComboBox and ListBox.

The usual way is the following.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo1_AfterUpdate()
  2.  
  3.     Me.Combo2.RowSource = "SELECT ... FROM ... WHERE Table2.keyID=" & Me.Combo1 & ";"
  4.     Combo2.Requery
  5.  
  6. End Sub
  7.  
Till now it looks good, however it has the following disadvantages.
1) It is not suitable to place in the code long string containing SQL Select statement with numerous fields and joins if the only purpose is to change WHERE clause.
2) Each time the RowSource property is changed in Design view the code has to be modified too.

A human being is not obliged to work so hard but a computer has to.

The solution I'm proposing is to write User-defined class.

Class module "SQLSelect".

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3.  
  4. Dim varClauses(1 To 4) As Variant
  5. Dim strClausesNames(1 To 5) As String
  6. Dim strSQL As String
  7.  
  8. Public Property Get SelectString() As String
  9.     SelectString = Me.SelectExpression & ";"
  10. End Property
  11.  
  12. Public Property Let SelectString(ByVal strNewValue As String)
  13.  
  14.     Dim intCutPosition As Integer
  15.     Dim intCutLength As Integer
  16.     Dim strSubStr As String
  17.  
  18.     strSQL = strNewValue
  19.  
  20.     For i = 1 To 4
  21.         intCutPosition = InStr(1, strSQL, strClausesNames(i), vbTextCompare)
  22.         If intCutPosition <> 0 Then
  23.             intCutPosition = intCutPosition + Len(strClausesNames(i))
  24.             For j = i + 1 To 5
  25.                 intCutLength = InStr(1, strSQL, _
  26.                     strClausesNames(j), vbTextCompare)
  27.                 If intCutLength <> 0 Then
  28.                     intCutLength = intCutLength - intCutPosition
  29.                     strSubStr = Mid(strSQL, intCutPosition, intCutLength)
  30.                     varClauses(i) = Trim(strSubStr)
  31.                     Exit For
  32.                 End If
  33.             Next j
  34.             'Debug.Print strClausesNames(i) & "   <" & varClauses(i) & ">"
  35.         Else
  36.             varClauses(i) = Null
  37.         End If
  38.     Next i
  39.  
  40. End Property
  41.  
  42. Public Property Get What() As Variant
  43.     What = varClauses(1)
  44. End Property
  45.  
  46. Public Property Let What(ByVal vNewValue As Variant)
  47.     varClauses(1) = vNewValue
  48. End Property
  49.  
  50. Public Property Get From() As Variant
  51.     From = varClauses(2)
  52. End Property
  53.  
  54. Public Property Let From(ByVal vNewValue As Variant)
  55.     varClauses(2) = vNewValue
  56. End Property
  57.  
  58. Public Property Get Where() As Variant
  59.     Where = varClauses(3)
  60. End Property
  61.  
  62. Public Property Let Where(ByVal vNewValue As Variant)
  63.     varClauses(3) = vNewValue
  64. End Property
  65.  
  66. Public Property Get OrderBy() As Variant
  67.     OrderBy = varClauses(4)
  68. End Property
  69.  
  70. Public Property Let OrderBy(ByVal vNewValue As Variant)
  71.     varClauses(4) = vNewValue
  72. End Property
  73.  
  74. Private Sub Class_Initialize()
  75.     strClausesNames(1) = "SELECT"
  76.     strClausesNames(2) = "FROM"
  77.     strClausesNames(3) = "WHERE"
  78.     strClausesNames(4) = "ORDER BY"
  79.     strClausesNames(5) = ";"
  80. End Sub
  81.  
  82. Public Property Get SelectExpression() As String
  83.  
  84.     Dim varClause As Variant
  85.  
  86.     SelectExpression = ""
  87.  
  88.     For i = 1 To 4
  89.         If Not (IsNull(varClauses(i)) Or IsEmpty(varClauses(i))) Then
  90.             varClause = strClausesNames(i) & " " & varClauses(i)
  91.             SelectExpression = SelectExpression & varClause & " "
  92.         End If
  93.     Next i
  94.  
  95.     SelectExpression = RTrim(SelectExpression)
  96.  
  97. End Property
  98.  
  99.  

The class has the following properties:

.What - r/w, SELECT clause of SQL Select statement
.From - r/w, FROM clause of SQL Select statement
.Where - r/w, WHERE clause of SQL Select statement
.OrderBy - r/w, ORDER clause of SQL Select statement
.SelectString - r/w, SQL Select statement with trailing ";"
.SelectExpression - r, the same as .SelectString w/o trailing ";"

Now the above example looks like.

Form module.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Combo1_AfterUpdate()
  3.  
  4.     Dim sqlRowSource As New SQLSelect
  5.  
  6.     With Me.Combo2
  7.         sqlRowSource.SelectString = .RowSource
  8.         sqlRowSource.Where = "Table2.keyID=" & Me.Combo1
  9.         .RowSource = sqlRowSource.SelectString
  10.         .Requery
  11.     End With
  12.  
  13.     Set sqlRowSource = Nothing
  14.  
  15. End Sub
  16.  
  17.  
To my mind this simple solution makes programming more flexible and provides a more readable and intuitive code.

I hope this will be helpful.

Best regards to all.

Fish
Jun 9 '07 #1
Share this Article
Share on Google+