473,842 Members | 1,535 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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("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?

May 19 '06 #1
2 8182
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.RecordSourc e)

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_definit ion_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
9986
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 performing the validation check on the values that were entered into the form, I simply repost the form to perform the PHP validation. If any of the values that have been entered into the form are incorrect, I display a warning message on the screen...
4
2376
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 client-side Javascript validation which flags up when a field is empty but the form is still submitted. Here is the ASP code I am using to submit the form to an e- mail with CDONTS:
2
2190
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 The first time the form is displayed, the value of ShowFilenames should be
1
1693
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 different, how can I roll-up the total of the form fields (request.form("subtotal_"& i)) to get one final figure? Sean - Thanks in advance for your answer
2
2913
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. Although the child form has a close button (with java script) if the user navigates the parent form to another form the child form is still open. Just wondering if there is a way to close the child form when the user navigates away with the parent...
4
3773
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 the end of the loop and the value of x3 as not all options may be selected from the form yet the loop goes through the entire request.form list I have to add addtional code to strip off the last "and" and was wondering if there is a way to...
2
2138
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. Using Safari,my first iteration the script works fine ( indicating that there are 33 form variables ). When trying another dropdown select value, the
6
6202
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 will say that user will first select one from Brand as IBM then he will select Type as Laptop. then what will happen again brand will set to All. I need to avoid happening this.. again i want to repeat the same for all the other menus. please be...
7
2093
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 form has multiple input fields with the same id in case the employee has volunteered for multiple organizations. First of all what's the beset practice out there for handling this scenario? Currently I'm running a FOR loop which is creating a new...
4
1952
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 than one field or check muliple boxes to query the database. What I want to do is create a multiple search criteria data string. Below is the code that working with a the moment. Any input would be appreciate. Thanks Greg If...
0
9715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10674
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10317
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9454
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7859
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7040
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5886
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4500
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4090
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.