The usual way is the following.
Expand|Select|Wrap|Line Numbers
- Private Sub Combo1_AfterUpdate()
- Me.Combo2.RowSource = "SELECT ... FROM ... WHERE Table2.keyID=" & Me.Combo1 & ";"
- Combo2.Requery
- End Sub
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
- 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
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
- 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
I hope this will be helpful.
Best regards to all.
Fish