J
jeninOk
Can anyone see what's wrong with the code for handling the list controls. I
would so much appreciate help with this. It is so close.
Codee below (look for ' ********************BEGIN LBO CODE)
The text controls and combo controls work like AWESOME, but --
there is an error thrown whenever I select from the list control.
ERROR:
Run-time error '3075'
Missing ),], or Item in query expression '(([County] In ('&lbOptions &'))
Why would it show &lbOptions& rather than my selections? Why is there an
extra ( ?
CODE COURTESY KEN GETZ:
Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)
' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.
Dim strTemp As String
On Error GoTo HandleErrors
If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If
' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.
Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the text we got.
' This is probably a LOT slower. If you want direct matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue & "*" & QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other data types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If
BuildSQLString = strTemp
ExitHere:
Exit Function
HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")", vbExclamation,
"BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function
Private Function BuildWHEREClause(frm As Form) As String
' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.
Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference
' ********************BEGIN LBO CODE
Dim lbOptions As Variant
Dim varItem As Variant
Dim where As Variant
lbOptions = Null
For Each varItem In frm.lstCounty.ItemsSelected
lbOptions = (lbOptions + ",") & "'" & frm.lstCounty.ItemData(varItem) & "'"
Next varItem
If Not IsNull(lbOptions) Then
where = where & ") AND([County] In (' & lbOptions & ')"
'***changed " ' around & lbOptions &
'***added ( before [County]
'***added ) before the AND
Debug.Print "LBOwhere:"; where
End If
'' END LBO CODE
Const conAND As String = " AND "
For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource, ctl, varDataType) & ")"
Debug.Print "strTemp:"; strTemp '****trying to see the sql
strLocalSQL = strLocalSQL & conAND & strTemp
Debug.Print "strLocalSQL:"; strLocalSQL '****added to see the sql
End If
End If
End If
Next ctl
'****try adding var 'where' from LBO code here:
strLocalSQL = strLocalSQL & where '***********THIS IS NEW CODE
' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) & ")"
Debug.Print "BuildWHEREClause:"; BuildWHEREClause '**** to see the sql
End If
End Function
Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String
' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.
' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.
Dim strSQL As String
DoCmd.OpenForm strFormName, WindowMode:=acDialog
'*******************************************
Private Function IsOperator(varValue As Variant) As Boolean
' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.
Dim strTemp As String
strTemp = Trim$(UCase(varValue))
IsOperator = False
' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")")) Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, " AND ") >
0)) Then
IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = "LIKE ") Then
IsOperator = True
End If
End Function
would so much appreciate help with this. It is so close.
Codee below (look for ' ********************BEGIN LBO CODE)
The text controls and combo controls work like AWESOME, but --
there is an error thrown whenever I select from the list control.
ERROR:
Run-time error '3075'
Missing ),], or Item in query expression '(([County] In ('&lbOptions &'))
Why would it show &lbOptions& rather than my selections? Why is there an
extra ( ?
CODE COURTESY KEN GETZ:
Private Function BuildSQLString( _
ByVal strFieldName As String, _
ByVal varFieldValue As Variant, _
ByVal intFieldType As Integer)
' Build string that can be used as part of an
' SQL WHERE clause. This function looks at
' the field type for the specified table field,
' and constructs the expression accordingly.
Dim strTemp As String
On Error GoTo HandleErrors
If Left$(strFieldName, 1) <> "[" Then
strTemp = "[" & strFieldName & "]"
End If
' If the first part of the value indicates that it's
' to be left as is, leave it alone. Otherwise,
' munge the value as necessary.
If IsOperator(varFieldValue) Then
strTemp = strTemp & " " & varFieldValue
Else
' One could use the BuildCriteria method here,
' but it's not as flexible as I'd like to
' be. So, this code does all the work manually.
Select Case intFieldType
Case dbBoolean
' Convert to TRUE/FALSE
strTemp = strTemp & " = " & CInt(varFieldValue)
Case dbText, dbMemo
' Assume we're looking for anything that STARTS with the text we got.
' This is probably a LOT slower. If you want direct matches
' instead, use the commented-out line.
' strTemp = strTemp & " = " & QUOTE & varFieldValue & QUOTE
strTemp = strTemp & " LIKE " & QUOTE & varFieldValue & "*" & QUOTE
Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble
' Convert to straight numeric representation.
strTemp = strTemp & " = " & varFieldValue
Case dbDate
' Convert to #date# format.
strTemp = strTemp & " = " & "#" & varFieldValue & "#"
Case Else
' This function really can't handle any of the other data types. You can
' add more types, if you care to handle them.
strTemp = vbNullString
End Select
End If
BuildSQLString = strTemp
ExitHere:
Exit Function
HandleErrors:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")", vbExclamation,
"BuildSQLString"
strTemp = vbNullString
Resume ExitHere
End Function
Private Function BuildWHEREClause(frm As Form) As String
' Build the full WHERE clause based on fields
' on the passed-in form. This function attempts
' to look at all controls that have the correct
' settings in the Tag properties.
Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer
Dim varControlSource As Variant
Dim ctl As Control
'var for list control reference
' ********************BEGIN LBO CODE
Dim lbOptions As Variant
Dim varItem As Variant
Dim where As Variant
lbOptions = Null
For Each varItem In frm.lstCounty.ItemsSelected
lbOptions = (lbOptions + ",") & "'" & frm.lstCounty.ItemData(varItem) & "'"
Next varItem
If Not IsNull(lbOptions) Then
where = where & ") AND([County] In (' & lbOptions & ')"
'***changed " ' around & lbOptions &
'***added ( before [County]
'***added ) before the AND
Debug.Print "LBOwhere:"; where
End If
'' END LBO CODE
Const conAND As String = " AND "
For Each ctl In frm.Controls
' Get the original control source.
varControlSource = adhCtlTagGetItem(ctl, "qbfField")
If Not IsNull(varControlSource) Then
' If the value of the control isn't null...
If Not IsNull(ctl) Then
' then get the value.
varDataType = adhCtlTagGetItem(ctl, "qbfType")
If Not IsNull(varDataType) Then
strTemp = "(" & BuildSQLString(varControlSource, ctl, varDataType) & ")"
Debug.Print "strTemp:"; strTemp '****trying to see the sql
strLocalSQL = strLocalSQL & conAND & strTemp
Debug.Print "strLocalSQL:"; strLocalSQL '****added to see the sql
End If
End If
End If
Next ctl
'****try adding var 'where' from LBO code here:
strLocalSQL = strLocalSQL & where '***********THIS IS NEW CODE
' Trim off the leading " AND "
If Len(strLocalSQL) > 0 Then
BuildWHEREClause = "(" & Mid$(strLocalSQL, Len(conAND) + 1) & ")"
Debug.Print "BuildWHEREClause:"; BuildWHEREClause '**** to see the sql
End If
End Function
Public Function DoQBF(ByVal strFormName As String, _
Optional blnCloseIt As Boolean = True) As String
' Load the specified form as a QBF form. If
' the form is still loaded when control returns
' to this function, then it will attempt to
' build an SQL WHERE clause describing the
' values in the fields. DoQBF() will return
' either that SQL string or an empty string,
' depending on what the user chose to do and
' whether or not any fields were filled in.
' In:
' strFormName: Name of the form to load
' blnCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.
Dim strSQL As String
DoCmd.OpenForm strFormName, WindowMode:=acDialog
'*******************************************
Private Function IsOperator(varValue As Variant) As Boolean
' Return a logical value indicating whether a
' value passed in is an operator or not.
' This is NOT infallible, and may need correcting.
Dim strTemp As String
strTemp = Trim$(UCase(varValue))
IsOperator = False
' Check first character for <,>, or =
If InStr(1, "<>=", Left$(strTemp, 1)) > 0 Then
IsOperator = True
' Check for IN (x,y,z)
ElseIf ((Left$(strTemp, 4) = "IN (") And (Right$(strTemp, 1) = ")")) Then
IsOperator = True
' Check for BETWEEN ... AND ...
ElseIf ((Left$(strTemp, 8) = "BETWEEN ") And (InStr(1, strTemp, " AND ") >
0)) Then
IsOperator = True
' Check for NOT xxx
ElseIf (Left$(strTemp, 4) = "NOT ") Then
IsOperator = True
' Check for LIKE xxx
ElseIf (Left$(strTemp, 5) = "LIKE ") Then
IsOperator = True
End If
End Function