sql to query instead of filter-by-form

[Seth Delaney]

I have a form with multiple unbound text boxes which serves as a
"search form". I can enter my search parameters in the various boxes
as needed and click okay. My records are then filtered to produce the
results I want in a separate form (filter by form). No problem, except
I want to save the sql to a query, not a form.

When I debug.print the various sql, I get something like this example:
(([strLastCoName] LIKE "cam*"))
which obviously is not enough data to run a query. I've tried querydef
which only produces "SELECT;" in the View SQL since it's not receiving
enough information from the code.

How can I take the search form which is working very well and use the
results in a query instead of a form? I've attached much of the code
below:

Private Function BuildSQLString(strFieldName As String, varFieldValue
As Variant, 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

strTemp = "[" & strFieldName & "]"
' 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
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 (DB_BINARY?)
strTemp = ""
End Select
End If
BuildSQLString = strTemp
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 intI As Integer
Dim strLocalSQL As String
Dim strTemp As String
Dim varDataType As Integer

Dim varControlSource As Variant
Dim ctl As Control

For intI = 0 To frm.Count - 1
Set ctl = frm(intI)
' 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(CStr(varControlSource), ctl, CInt(varDataType)) & ")"
strLocalSQL = strLocalSQL & IIf(Len(strLocalSQL) =
0, strTemp, " AND " & strTemp)
End If
End If
On Error GoTo 0
End If
Next intI
If Len(strLocalSQL) > 0 Then strLocalSQL = "(" & strLocalSQL & ")"
Debug.Print strLocalSQL
BuildWHEREClause = strLocalSQL
End Function

Function glrDoQBF(strFormName As String, fCloseIt As Integer)

' 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 a null 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
' fCloseIt: Close the form, if the user didn't?
' Out:
' Return Value: The calculated SQL string.

Dim strSQL As String

DoCmd.OpenForm strFormName, WindowMode:=acDialog

' You won't get here until user hides or closes the form.
' If the user closed the form, there's nothing
' to be done. Otherwise, build up the SQL WHERE
' clause. Once you're done, if the caller requested
' the QBF form to be closed, close it now.
If isFormLoaded(strFormName) Then
strSQL = BuildWHEREClause(Forms(strFormName))
If fCloseIt Then
DoCmd.Close acForm, strFormName
End If
End If
glrDoQBF = strSQL
Debug.Print strSQL
End Function

Function glrQBF_DoHide(frm As Form)
Dim varSQL As Variant
Dim strParentForm As String

'Get the name of the Parent form
strParentForm = Left(CStr(frm.Name), Len(CStr(frm.Name)) - 4)
'Create the approprite Where clause based on the fields with data
in them
varSQL = glrDoQBF(CStr(frm.Name), False)
'Open the Parent form filtered with the Where clause genereated
above
DoCmd.OpenForm strParentForm, acNormal, , varSQL
'Make this *_QBF form invisible
frm.Visible = False

End Function
Thanks,

[Larry Linson]

"Seth Delaney" wrote

I have a form with multiple unbound
text boxes which serves as a "search
form". I can enter my search parameters
in the various boxes as needed and
click okay. My records are then filtered
to produce the results I want in a separate
form (filter by form). No problem, except
I want to save the sql to a query, not a form.

When I debug.print the various sql, I get
something like this example:
(([strLastCoName] LIKE "cam*"))
which obviously is not enough data to run a query.
Not surprising, as that is a condition (aka "WHERE clause without the
WHERE"), a perfectly valid filter. You have to concatenate that to the basic
SQL (aka query) that is used as RecordSource of the bound Form that is used
as your search Form.

When you do, you'll have a string -- if you want to save it for later use,
you can save it in a Public variable in a standard Module, or in a Text
field in a Table, or in the SQL property of a QueryDef. If you are using a
QueryDef rather than an SQL Statement as the RecordSource of the bound Form,
saving it in that QueryDef's SQL statement would be an efficient approach
(you may need to Requery the Form, but requerying may not be necessary).
I've tried querydef which only produces
"SELECT;" in the View SQL since it's not
receiving enough information from the code.

The basic SELECT must specify Fields to be retrieved and the Table (or other
QueryDef) from which they are to be retrieved -- "SELECT" alone is not
enough. To that, you'll concatenate your conditions.

[Glenn Davy]

Hi Seth
First you need to build it in to a string - so with out actually reading
all your code too carefully, I'm guessing you are sucessfully building a
meaningful where clause - so concatinate it up like

mystring = "SELECT * " _
& " FROM myTableName " _
& " WHERE " _
& myWhereClause

or am I missing the point?

THEN, if your using an mdb file,
create a querydef - here's quick and
dirty sample:

Sub makeQuery(name_q as string,sql_q as string)

Dim q As New DAO.QueryDef

q.SQL = sql_q
q.Name = name_q
CurrentDb.QueryDefs.Append q

q.Close
set q=nothing
End Sub

If your not using an mdb lemme know and we'll go from there

Glenn