Hey people...
I've been searching this forum for a few hours and even though this topic has been went over from many different angles; I cannot seem to figure out how to make things work on my side.
I am trying to learn how to manipulate Dynamic Queries by forms via the example database: QrySampl.MDB, offered by Microsoft (as a learning tool, I suppose.) In particular, I am working with code from the example: "Query By Form (QBF) Using Dynamic QueryDef." Found here...
What I would like to change and adapt to my own project is a way in which I can specify the SQL and resulting query from a form (as shown in the example) but instead of displaying the query data in a plain data table, output it to a subform under the search/query criteria.
I've gotten as far as being able to display the first query, but have not figured a way to make the subform data update each time as I change the query criteria on the main form. The query itself will change, but the linked subform will not reflect these changes. The only way I have been able to show the new data in the subform is to close/open the "parent" form, or to open/close the VB debugger. The main form and subform are not linked with any references to one another. The main form is just serving as a place holder for unbound text boxes which help build the SQL statement, which in turn creates the query criteria. The subform was designed separately, linked to the query and was dragged onto the main form.
The example employs one table called orders. A form called: frmDynamicQBF acts as the front-end for the query. 6 unbound text boxes are placed on this form: Customer ID, Employee ID, Ship City, Ship Country, Order Start Date, and Order End Date. A Command button executes the following code on click, which builds, and/or deletes the dynamic query - qryDynamic_QBF as follows:
Private Sub cmdRunQuery_Click()
Dim MyDatabase As Database
Dim MyQueryDef As QueryDef
Dim where As Variant
Set MyDatabase = CurrentDb()
' Delete the existing dynamic query; trap the error if the query does
' not exist.
If ObjectExists("Queries", "qryDynamic_QBF") = True Then
MyDatabase.QueryDefs.Delete "qryDynamic_QBF"
MyDatabase.QueryDefs.Refresh
End If
' Note the single quotation marks surrounding the text fields [Ship
' Country] and [CustomerID].
' Note that there are no type-casting characters surrounding the
' numeric field [EmployeeID].
where = Null
where = where & " AND [ShipCountry]= '" + Me![Ship Country] + "'"
where = where & " AND [CustomerID]= '" + Me![Customer Id] + "'"
where = where & " AND [EmployeeID]= " + Me![Employee Id]
'NOTE: In Microsoft Access 97, when you use the plus sign (+) in an
'expression in which you are concatenating a variable of the numeric
'data type, you must use parenthesis around the syntax, as in the
'following example:
'
' where = where & (" AND [EmployeeID]= " + Me![Employee Id])
'
'You must also use a conversion function to make sure that the proper
'conversion (to either NULL or String) takes place.
' The following section evaluates the ShipCity criteria you enter.
' If the first or last character of the criteria is the wildcard
' character (*), then the function uses the "LIKE" operator in the
' SQL statement instead of "=". Also note the single quotation
' marks surrounding the text field [ShipCity].
If left(Me![Ship City], 1) = "*" Or Right(Me![Ship City], 1) = "*" Then
where = where & " AND [ShipCity] like '" + Me![Ship City] + "'"
Else
where = where & " AND [ShipCity] = '" + Me![Ship City] + "'"
End If
' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![Order End Date]) Then
where = where & " AND [OrderDate] between #" + _
Me![Order Start Date] + "# AND #" & Me![Order End Date] & "#"
Else
where = where & " AND [OrderDate] >= #" + Me![Order Start Date] _
+ " #"
End If
' Remove the following MsgBox line if you do not want to display the
' SQL statement.
' NOTE: The Mid function is used in the following MsgBox function to
' remove the word AND that follows the first Where clause. If you do
' not use the Mid function, the SQL statement contains the word AND
' at the beginning of the WHERE clause, for example:
'
' Select * from Orders where AND [CustomerID] = 'CACTU'
Set MyQueryDef = MyDatabase.CreateQueryDef("qryDynamic_QBF", _
"Select * from orders " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "qryDynamic_QBF"
End Sub
A second command button allows visual verification of the SQL statement built by the first command button. No need to post this code, as it is working fine.
Also to get this working properly, two modules must be present: basSPTExamples, and basCommonFunctions.
I quickly designed a separate form called: frmDynamicQBF_subform, which contains textboxes based on the fields in the table, linked the recordsource to the query, incorporated it as a subform under frmDynamicQBF, and set it to continuous. All text boxes were properly bound.
What I would like to do is get the query to output to the subform: frmDynamicQBF_subform placed under the form: frmDynamicQBF and have it update itself automatically each time the first command button is clicked, just as the query itself updates. I could probably cheat and have the data in the subform open in a new window as a separate form, which would force a requery each time (I suppose)
Any help would be greatly appreciated. I know many versions of this have been hashed over and over, but I am pretty much a novice with Access.
Sorry for such a long-winded post, I know eyes must be rolling... ;-)
Regards,
- BSM ->