refresh/requery, field/form - confused!

I've been reading (and testing) around refreshing vs. requerying forms and fields, and I'm still confused.
I have a form that takes a while to requery, so I'm looking for alternatives.
Questions (all these presupposed that the record is in the original recordset, i.e. was not added or deleted):
1) If I refresh, and assuming that the record is already in the recordset, the underlying fields are supposed to display the updated data, right?
2) If I am only interesting in 1 field, if I refresh just that control, does that achieve the same thing?
3) How is refreshing a field different from recalc?
4) How about a field that is a function (UDF) called within the SQL query - if this needs to be updated, do I need to do a requery, or will a refresh of that field work?
5) If a field on the form is based on an IIF statement, for example, will a refresh update this field, or do I need a recalc or requery of the underlying recordsource?
THANK YOU!
terpsichoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent DyerIT Security Analyst SeniorCommented:
For your requery, do only a:
SELECT FIELDNAME WHERE ME!FORM_ID = TABLE_ID

Open in new window

and that way you only grab one value for one field in your lookup.  If you have to have more than one field, you can do a
SELECT FIELDNAME,FIELDNAME2 WHERE ME!FORM_IE=TABLE_ID

Open in new window

..
0
terpsichoreAuthor Commented:
sorry, not following that.
0
Jeffrey CoachmanMIS LiasonCommented:
If a Requery works then I would stay with that.

<1. If I refresh, and assuming that the record is already in the recordset, the underlying fields are supposed to display the updated data, right?>
A refresh may not bring in all the new or updated records.
See here:
http://office.microsoft.com/en-us/access-help/refresh-or-requery-data-HA010256400.aspx
...and here:
http://support.microsoft.com/kb/209634
...for more exact information.


<2) If I am only interesting in 1 field, if I refresh just that control, does that achieve the same thing?>
The same thing as what?
You cannot refresh a control, you can only refresh the form.

<3) How is refreshing a field different from recalc?>
You cannot recalc a field/control, you can only recalc the form

<4) How about a field that is a function (UDF) called within the SQL query - if this needs to be updated, do I need to do a requery, or will a refresh of that field work?>
See the above, A requery of the field/control would be the only way.

<5) If a field on the form is based on an IIF statement, for example, will a refresh update this field, or do I need a recalc or requery of the underlying recordsource?>
A recalc of the form would be preferred, but here again, if the control needs updated data from the forms recordsource, ...then requery of the form would be best.
See here for more info  on recalc:
http://msdn.microsoft.com/en-us/library/office/aa221552%28v=office.11%29.aspx


I would rather have something that works "all the time" (but is a bit slow), than something that "Might" work (depending on the circumstances).
For example what if refresh/recalc is rocket fast, but in certain situations does not display the correct info.

If requery is slow then you need to consider a few things:
Are you re-querying the entire form, or just the control(s) that need it.
Why is your form and its controls so complex that a requery slows it down considerably. (consider simplifying the form, or using a separate form for other functions, ..etc)
Your definition of "slow".  Can you state this in real chronological terms?
Speed, (real or perceived) is relative.
There are a lot of other things that affect the requery time:
Indexing, the number of complex controls, number of records involved, Graphics, Network, ...etc
For example, you are requerying the Customer form (45,000 records) and it is "slow"
Why would the customer form need every single customer loaded?
Why not filter the form for Active customers only, ...or only customers with outstanding orders, ...etc


But again, I mostly use Requery, then I never have to worry.
You can try the others, but again, you must make sure that the data is correct for all situations and user actions (especially in Multi-user systems)

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
terpsichoreAuthor Commented:
fantastic response. Just the breadth of ideas I was looking for.
In terms of 'slow' - we can't complain, I guess - it is maybe 3-4 seconds to requery the form.
If we did NOT use conditional formatting and UDFs in the underlying recordsource, it would probably take half the time - but those are powerful advantages to the user...

Thanks so much - very valuable info.
0
Jeffrey CoachmanMIS LiasonCommented:
ok
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.