Prev: opening access2003 from access97
Next: A2010. Can be accessed by phone?
From: Owen on 27 Jul 2010 23:14
I have a calculated field on a form with the following as its
controlsource.

=DLookUp("BupaID","Lookups_UserT","Category='Provider 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.
From: Salad on 28 Jul 2010 01:34
Owen wrote:

> I have a calculated field on a form with the following as its
> controlsource.
>
> =DLookUp("BupaID","Lookups_UserT","Category='Provider 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.

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?


From: paii, Ron on 28 Jul 2010 11:09
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(a)bigpond.net.au

"Owen" <google(a)healthbase.com.au> wrote in message
news:0da92a5f-323d-415d-9abb-4457b4eadadb(a)v35g2000prn.googlegroups.com...
> I have a calculated field on a form with the following as its
> controlsource.
>
> =DLookUp("BupaID","Lookups_UserT","Category='Provider 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.


From: David W. Fenton on 28 Jul 2010 13:20
Owen <google(a)healthbase.com.au> wrote in
news:0da92a5f-323d-415d-9abb-4457b4eadadb(a)v35g2000prn.googlegroups.co
m:

> I have a calculated field on a form with the following as its
> controlsource.
>
>=DLookUp("BupaID","Lookups_UserT","Category='Provider 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.

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/
From: Phil on 28 Jul 2010 17:07
On 28/07/2010 16:09:33, "paii, Ron" wrote:
> 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(a)bigpond.net.au
>
> "Owen" <google(a)healthbase.com.au> wrote in message
> news:0da92a5f-323d-415d-9abb-4457b4eadadb(a)v35g2000prn.googlegroups.com...
>> I have a calculated field on a form with the following as its
>> controlsource.
>>
>> =DLookUp("BupaID","Lookups_UserT","Category='Provider 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.
>
>

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
 |  Next  |  Last
Pages: 1 2 3
Prev: opening access2003 from access97
Next: A2010. Can be accessed by phone?