Re: Repost: OpenForm Does Not Update Embedded Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You should not call Me.Refresh neither Me.Requery in the procedure answering to the Open event since the data is already 'fresh' and up to date at that point. That is just plain useless and a lost of time, and immobilisation of ressources.

When you say:


The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time.


I assume you refer to a procedure of yours. If your form, Solution, is aware about WHEN that "Optimizer" is run, say because the form Solution call it (from its VBA code), then add the Me.Refresh or Me.Requery after the call:



Sub SomeSubroutinename()
...
Call Optimizer( probablySomeArgument )
Me.Requery
End Sub




If your form is not automatically aware about when that "Optimizer" is run, then maybe you have access to the VBA code of that Optimizer (or which launch that Optimizer), and then, in that code, add code that will requery the form Solution, if the form Solution is open.


If the "Optimizer" is not VBA code, or if you don't have access to it, and if it doesn't have any EVENT which is usable to warn YOUR code that the Optimizer has just run some update, then the only option left (that I see at the moment) is to use a form timer for Solution, and, in the code handling the timer, check if the form is dirty (if it is, the user is entering some data) "do nothing", ELSE, refresh the form:

if Me.Dirty then
else
Me.Refresh
end if


That code can be in the procedure handling the onTimer event of the form Solution, and you can use a timer interval of 1000 (1000 milliseconds, or one second), or as it fits for your solution. That is called polling, and is not considered to be very user friendly, since each 1000 msec, it will refresh your form (probably consuming network ressources) Solution.


About Me.Refresh, it is not a requery, and what it does, really, is not documented, probably it pokes its buffers to test if they are still valid, or something like that. It does not rebuilt the 'bookmarks' and does not test the records to see if they belong, or not, to those to be usable by the form.

You can look to query as if they were just method. An update query necessary updates the tables, or it fails (because of error, like any procedure would stop on an error). Generally, query update are embedded into a transaction so you can accept the updates to many records AS IF it was just one single update made in ONE single step, or you can reject the updates (as the User Interface does, when DoCmd.SetWarnings is set to true).





Vanderghast, Access MVP



"SteveM" <sbmack7@xxxxxxxxx> wrote in message news:79f66ebb-ddbc-4ddd-aa6c-01e6830d76f4@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On May 25, 1:57 pm, "vanderghast" <vanderghast@com> wrote:
Me.Requery will update, delete AND append records added by other
users/processes.

Me.Refresh will update existing records, as they have been affected by other
users/processes. But it won't append new records added by others, or
deleted.

Me.Recalc will update computations (if any) made on the form.

So you probably need either a Me.Refresh, either a Me.Requery. Note that the
re-query is slower and you loose the recordset bookmarks: after a requery,
you are brought back to the first record.

Vanderghast, Access MVP

"SteveM" <sbma...@xxxxxxxxx> wrote in message

news:859788d5-ba5f-45b4-a9ad-95f38474072a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> This is a repost to percolate the unsolved problem to the top:

> ***********************************************************
> I have a Form with a Query source. The associated Table content is
> updated with outputs from an Optimization routine.

> When I FIRST open the form using code or even from a command button on
> a switchbox, the underlying query is not updated to reflect the
> optimization results. However if I close the form and open it again
> from the switchboard, the query updates properly. This behavior is
> not absolute. Sometimes the query will update properly when opened
> the first time.
> **********************************************************

> Update: I've isolated the problem. The embedded query in the Form
> does execute. However, an underlying Table that is updated by the
> Optimizer is not refreshed until the Form is opened a second time. I
> found some code and inserted it after the Optimization call but before
> the OpenForm method to see if it would refresh the table:

> CurrentDb.TableDefs.Refresh
> DoEvents

> That does not work. Asking again for ideas.

> Thanks Again Too,

> SteveM

Thanks for the help men. But Requery what? where?

I tried Me.Requery inside the On Open event for the Pivot Table Form
and that merely zotzed out the design content of the Form. So now
it's empty.

Pardon my ignorance, but I'm trying to get a handle on Requery/
Refresh. I've tried different coding possibilities but they all
fail. The Optimizer repopulates a column in a table call Solution.
But the changes are not posted to Solution until I open the Form a
second time. So how to post changes to the Solution Table if the
Optimizer does not? Does Requery apply to Tables or only to Queries?
How about Refresh? Do these launch recursive updates? I.e., if an
embedded query is updated, are the tables associated with the query
also updated?

I'm sure I'm missing something trivial. Appreciate your iterative
patience.

SteveM

.

Relevant Pages

  • Re: Index chosen dependant on result columns
    ... Index hints can be put into a query but they are ... using them unless the optimizer is coming up with incorrect estimates, ... rows for each inequality overlap. ... Your nonclustered index on B,C is like a table that contains ...
    (microsoft.public.sqlserver.programming)
  • Re: [Info-Ingres] To order by, or NOT to order by
    ... [Info-Ingres] To order by, ... but then dropped the -zs flag altogether when there were no changes in the QEP. ... the best QEP but I guess the optimizer in 2.6/604 is a bit different. ... QEPs with a sample query ...
    (comp.databases.ingres)
  • Re: [Info-Ingres] To order by, or NOT to order by
    ... I'm looking for a way to make the optimizer come up with the ... [Info-Ingres] To order by, ... QEPs with a sample query ... Query and QEP, ...
    (comp.databases.ingres)
  • Re: Repost: OpenForm Does Not Update Embedded Query
    ...     Sub SomeSubroutinename ... If your form is not automatically aware about when that "Optimizer" is run, ... You can look to query as if they were just method. ... Optimizer and then Opens a PivotTable Form when the Optimization is ...
    (microsoft.public.access.modulesdaovba)
  • Re: SEQUENTIAL SCANS
    ... Above query correctly takes INDEX PATH. ... It's perplexing why would optimizer behave like this. ... you did run update statistics low for table B, ...
    (comp.databases.informix)