By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

How can I make a form re-read (refresh) it's datasource

P: n/a
I am trying to give the user dynamic search capabilities to select
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("queries", S2Qry) = True Then 'the query
exists, modify it
'
' modify the SQL property of the existing query
'
Set MyQueryDef = db.QueryDefs(S2Qry)
MyQueryDef.sql = MyRecordSource
db.QueryDefs.Refresh
Else ' the query does not exist, create it
Set MyQueryDef = db.CreateQueryDef(S2Qry, MyRecordSource)
db.QueryDefs.Refresh
End If
'
' If the form is open, then refresh the recordsource to update the
new
' query data
'
If SysCmd(acSysCmdGetObjectState, acForm, "f2") = 1 Then
Form.RecordSource = Form_f2.RecordSource
Else
'
' set the form recordsource to the query and open it.
'
Form_f2.RecordSource = 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?

May 19 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi Robert.

Use Me.refresh or Me.requery after the line where you change the form's
datasource.

HTH

Colin

*** Sent via Developersdex http://www.developersdex.com ***
May 19 '06 #2

P: n/a
Colin,

Thanks for the advice and I have done that. First, the procedure above
is not in the form so Me.refresh won't work. You will notice that I do
have a requery on the querydef and I have put me.requery and me.refresh
in the on_current() procedure in the form. It still doesn't work. If I
close the form and the reopen, it always pulls the correct data set. I
just wanted a way to do it without closing the form. Procedures from
the forms are below.
Private Sub Form_Current()
Dim stEncWrd As Boolean
Dim sttext As String
'
' Set the input focus to the correct tab based on word type
'
stEncWrd = Me![WrdEncode]
Me.Requery
Debug.Print (Me.RecordSource)

If stEncWrd Then
Me![EncWrd].SetFocus
Else
Me![StdWrd].SetFocus
End If

End Sub
Private Sub Form_Load()
Select Case CallSource
Case "main_menu"
Me.RecordSource = defF2rst
Me.Requery
Case "sf8_msg_wordid"

Case "s2_wrd_definition_sheet"
Me.RecordSource = S2Qry
Me.Requery
Case Else
Me.RecordSource = defF2rst
Me.Requery
End Select

End Sub

I have noticed that sometimes it does work. It just isn't consistent.

Anyother suggestions?

May 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.