473,326 Members | 2,128 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
2 8132
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: TG | last post by:
I have a validation form that must behave differently based on the results of a PHP validation check. I have a post command at the top of my form that calls itself. I don't leave the form when...
4
by: Andrew Williams | last post by:
Does anyone know of a way to quickly validate an HTML web form? I need to make sure that certain fields on my form actually contain data and are not submitted blank. I have tried using...
2
by: bart plessers | last post by:
Hello, I have a form with some checkboxes, i.e. The default value of this checkbox is determined in a global file (config.asp), that is included in first line of the form, i.e. ShowFilenames...
1
by: sean | last post by:
HI There, I am trying to total the value of some form fields I am referecing the form field with an inner loop, I am having a little trouble because the amount of fields returned is always...
2
by: Paul | last post by:
Hi this is related to a previous post, hopefully just a bit clearer description o the problem. I have a parent form that opens a new form (child form) while still leaving the parent form open....
4
by: Michael Kujawa | last post by:
I am using the following to create an SQL statement using the names and values from request.form. The loop goes through each item in request.form The issue comes in having an additional "and" at...
2
by: justplain.kzn | last post by:
Hi, I have a table with dynamic html that contains drop down select lists and readonly text boxes. Dynamic calculations are done on change of a value in one of the drop down select lists. ...
6
ak1dnar
by: ak1dnar | last post by:
I have created HTML form and a Java script to validate the form. Here is my requirement. When user select one from any list menu others should set to ALL. Its working here but the problem is we...
7
by: sbryguy | last post by:
Greetings, I'm pretty new at ASP/SQL so if this seems like a no brainer, please enlighten me. I have a form that is being used to track volunteer activities for employees in my company. The...
4
by: gblack301 | last post by:
Hi, I have a search form where the user can check a box or enter some data such as a name to quey the database. I was wondering what is the best way to enable the ability for a user data in more...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.