因此,我提出一种方便的方法来处理包含SQL Select语句的属性,特别是ComboBox和ListBox的RowSource属性。
通常的方法如下。
Private Sub Combo1_AfterUpdate()
Me.Combo2.RowSource = "SELECT ... FROM ... WHERE Table2.keyID=" & Me.Combo1 & ";"
Combo2.Requery
End Sub
到现在为止看起来还不错,但是它具有以下缺点。
1)如果唯一的目的是更改WHERE子句,则不适合将包含SQL Select语句的长字符串包含在具有许多字段的代码中,并使其联接。
2)每次在“设计”视图中更改RowSource属性时,也必须修改代码。
人没有义务这么辛苦地工作,但是计算机必须如此。
我建议的解决方案是编写用户定义的类。
类模块“ SQLSelect”。
Option Compare Database
Dim varClauses(1 To 4) As Variant
Dim strClausesNames(1 To 5) As String
Dim strSQL As String
Public Property Get SelectString() As String
SelectString = Me.SelectExpression & ";"
End Property
Public Property Let SelectString(ByVal strNewValue As String)
Dim intCutPosition As Integer
Dim intCutLength As Integer
Dim strSubStr As String
strSQL = strNewValue
For i = 1 To 4
intCutPosition = InStr(1, strSQL, strClausesNames(i), vbTextCompare)
If intCutPosition <> 0 Then
intCutPosition = intCutPosition + Len(strClausesNames(i))
For j = i + 1 To 5
intCutLength = InStr(1, strSQL, _
strClausesNames(j), vbTextCompare)
If intCutLength <> 0 Then
intCutLength = intCutLength - intCutPosition
strSubStr = Mid(strSQL, intCutPosition, intCutLength)
varClauses(i) = Trim(strSubStr)
Exit For
End If
Next j
'Debug.Print strClausesNames(i) & " <" & varClauses(i) & ">"
Else
varClauses(i) = Null
End If
Next i
End Property
Public Property Get What() As Variant
What = varClauses(1)
End Property
Public Property Let What(ByVal vNewValue As Variant)
varClauses(1) = vNewValue
End Property
Public Property Get From() As Variant
From = varClauses(2)
End Property
Public Property Let From(ByVal vNewValue As Variant)
varClauses(2) = vNewValue
End Property
Public Property Get Where() As Variant
Where = varClauses(3)
End Property
Public Property Let Where(ByVal vNewValue As Variant)
varClauses(3) = vNewValue
End Property
Public Property Get OrderBy() As Variant
OrderBy = varClauses(4)
End Property
Public Property Let OrderBy(ByVal vNewValue As Variant)
varClauses(4) = vNewValue
End Property
Private Sub Class_Initialize()
strClausesNames(1) = "SELECT"
strClausesNames(2) = "FROM"
strClausesNames(3) = "WHERE"
strClausesNames(4) = "ORDER BY"
strClausesNames(5) = ";"
End Sub
Public Property Get SelectExpression() As String
Dim varClause As Variant
SelectExpression = ""
For i = 1 To 4
If Not (IsNull(varClauses(i)) Or IsEmpty(varClauses(i))) Then
varClause = strClausesNames(i) & " " & varClauses(i)
SelectExpression = SelectExpression & varClause & " "
End If
Next i
SelectExpression = RTrim(SelectExpression)
End Property
该类具有以下属性:
.What-r / w,SQL Select语句的SELECT子句
.From-r / w,SQL Select语句的FROM子句
.where-r / w,SQL Select语句的WHERE子句
.OrderBy-r / w,SQL Select语句的ORDER子句
.SelectString-r / w,后跟“;”的SQL Select语句
.SelectExpression-r,与不带尾部“;”的.SelectString相同
现在上面的例子看起来像。
表单模块。
Private Sub Combo1_AfterUpdate()
Dim sqlRowSource As New SQLSelect
With Me.Combo2
sqlRowSource.SelectString = .RowSource
sqlRowSource.Where = "Table2.keyID=" & Me.Combo1
.RowSource = sqlRowSource.SelectString
.Requery
End With
Set sqlRowSource = Nothing
End Sub
在我看来,这个简单的解决方案使编程更加灵活,并提供了更具可读性和直观性的代码。
我希望这会有所帮助。
致以最诚挚的问候。
鱼
From: https://bytes.com/topic/access/insights/660000-user-defined-class-sql-select-expressions