I have a calculated field on a form with the following as its
controlsource.

=DLookUp("BupaID","Lookups_UserT","Category='Provi der number' AND
Details='" & [ProviderNumber] & "'")

This looks up a value in a lookup table based on another value in the
form. The [ProviderNumber] field is bound to the form's recordsource.

This works fine except that when the form is opened the value is not
visible in the field until you click on the field.

I can't work out how to get the value to appear automatically. I've
tried .requery and .repaint on the field and form to no avail.

Owen wrote:

What happens if you change it to
NZ([ProviderNumber],"")
since it's a text value?

What happens if the record exists and you enter
Me.CalcFieldName.Requery
in the onCurrent event. Or in the AfterUpdate event you enter a
Provider Number?

Move the DLookUp to the form's on current event; fill the control with the
returned value.

DLookUp is very slow, you may want to replace it with eLookup. I use the one
from Allen Browne. abrowne1 (AT) bigpond (DOT) net.au

"Owen" <google (AT) healthbase (DOT) com.au> wrote

Owen <google (AT) healthbase (DOT) com.au> wrote in
news:0da92a5f-323d-415d-9abb-4457b4eadadb (AT) v35g2000prn (DOT) googlegroups.co
m:

Is there some reason you can't just put the lookup table in the
form's recordsource and retrieve the value along with the main
record?

I'm always suspicious of DLookup() as it is very seldom the case
that the value cannot be retrieved in the form's/report's
recordsource (though sometimes that can cause updatability problems,
so it's not always a mistake).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 28/07/2010 16:09:33, "paii, Ron" wrote:
I always use Elookup instead of Dlookup as well as EMax, EMin etc. I tried to
test the difference in speed in AK2 some time ago and found very little
difference. Almost certainly wrong, but could this be a myth?

Phil

Thanks for all the responses.

Salad - Nz() makes no difference.
Requery makes no difference.

Ron - moving DLookup to the Current event worked. Thanks.
Thanks for the eLookup tip.

David - using the lookup value in the recordsource renders the form
data uneditable.

Phil - thanks, I'll look at using eLookup in future.

I'm still mystified as to why the calc field would not display the
value, since it is there when you click the field. But I'll go with
Ron's method to get around it.

Thanks all.
Owen

On 29/07/2010 02:32:21, Owen wrote:
I,m having problems with the structure of your Lookups_UserT table. Do you
have 2 fields Category and details both text? You alst refer to both
'Provider number' and 'ProviderNumber' , presumably on the form. What sort of
data are they - text or numeric?

Phil

RESPONDING TO "...could this be a myth?"

The domain functions (DLookup, DMax, DMin, etc.) are relatively slow, but you
only notice the "slowness" when you are calling them multiple times in a loop
of some kind. Or if you are calling them in a query and returning a lot of
records or are filtering on the result of the function call.

For one record I doubt that you could detect much difference (if any) between
Allen Browne's functions and the built-in functions. The enhanced abilities
of Allen's functions are of real benefit and the use of the enhanced abilities
might (I've not tested this) actually make them a bit slower.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

"John Spencer" <JSPENCER (AT) Hilltop (DOT) umbc> wrote

In this case the OP appears to be calling the dlookup function for each
record on the form. elookup will show a noticeable improvement in displaying
the next record.

"Owen" <google (AT) healthbase (DOT) com.au> wrote

I think Access attempts to save time by only refreshing controls it thinks
changed. In your case the row source being a function appears to Access as
static.