almost any record in the database from criteria they select. Everything
seems to work except when I open the display form to display the data
to the user. If that form is already open, how do I make it refresh its
data source and display the new data.
Here is what I am doing.
From a text entry form, I create an SQL query dymanically and alter the
sql statement of an existing query or create a new query. Then I open
the display form (F2) with this new query as the recordsource. It works
if the display form (F2) is not open. But if that form is already open
displaying filtered results, it doesn't refresh the data based on the
updated query.
Sample code
Dim myCriteria As String, MyRecordSource As Variant
Dim MyQueryDef As DAO.QueryDef
Dim db As DAO.Database
Dim ArgCount As Integer
Dim Encode As Boolean
Dim strfield As String, S2Qry As String, sqlQ10 As String, sqlQ11
As String
Dim rst As DAO.Recordset
Set db = CurrentDb()
If S2Qry = "" Then
S2Qry = "s2_" & CurrentUser()
End If
'
' Initialize variables.
'
ArgCount = 0
myCriteria = ""
MyRecordSource = ""
'
' Use values entered in text boxes in form header to create
criteria for WHERE clause.
' The AddToWhere function constructs syntacticly corrent SQL where
clause based on input
'
AddToWhere [Id], "[tbl4].Id", myCriteria, ArgCount,
[btn_exact_match]
AddToWhere [Name], "[tbl4].Name", myCriteria, ArgCount,
[btn_exact_match]
AddToWhere [Desc], "[tbl4].Desc", myCriteria, ArgCount,
[btn_exact_match]
'
' If no criterion specifed, return all records.
'
If myCriteria = "" Then
myCriteria = "True"
End If
CallSource = Me.Name
'
' Open the Word Definition Sheet with search criteria specified
'
MyRecordSource = sqlQ10 & " where " & myCriteria & ";"
At this point MyRecordSource contains a valid SQL query statement that
selects
the records the user wants to see in the form. Set the form
recordsource to display
the data.
'
' The function ObjectExists returns true if the item exists
'
If ObjectExists("q ueries", S2Qry) = True Then 'the query
exists, modify it
'
' modify the SQL property of the existing query
'
Set MyQueryDef = db.QueryDefs(S2 Qry)
MyQueryDef.sql = MyRecordSource
db.QueryDefs.Re fresh
Else ' the query does not exist, create it
Set MyQueryDef = db.CreateQueryD ef(S2Qry, MyRecordSource)
db.QueryDefs.Re fresh
End If
'
' If the form is open, then refresh the recordsource to update the
new
' query data
'
If SysCmd(acSysCmd GetObjectState, acForm, "f2") = 1 Then
Form.RecordSour ce = Form_f2.RecordS ource
Else
'
' set the form recordsource to the query and open it.
'
Form_f2.RecordS ource = S2Qry
DoCmd.OpenForm strF2
End If
So, when I execute, if the form F2 is not open, the form opens and
displays the correct queried data. I use a debug.print me.recordsource
in the form_open() procedure and I know that the recordsource is set to
S2Qry. The form displays only those records that match the query
selection. Opening the query in the database window shows only records
matching the query.
Now, then I rerun the query with a new search, the querydef exists, so
I modify the sql statement and refresh the query (I can see that new
data is selected by opening the query in the database window) but the
form doesn't refresh with the new data. It still displays the old query
results. Unless I close the form and reopen it, the new query results
aren't displayed. How can I make the form re-read the recordsource to
get the new query data?
