Hi All

Im a bit confused as to why Access2007 throws errors back when I use the
following statement with set value or set temp var, but is fine with msgbox:

=DLookUp("[AutoASC]","[RetailerASCList]","ID= [Forms]![ClaimEntry]![Store]")

When it complains, it throws a screen with "Action Failed" and error number
2950.

Any help would be great
Thanks
Andrew

I would suggest that you create the literal string in your criteria of the
DLookup() ...

If "ID" is Numeric ...
=DLookUp("[AutoASC]","[RetailerASCList]","ID=" &
Nz([Forms]![ClaimEntry]![Store],0))

If "ID" is Text ...
=DLookUp("[AutoASC]","[RetailerASCList]","ID='" &
Nz([Forms]![ClaimEntry]![Store],"") & "'")

Also, make sure the form is open before you run the macro ..

--
Brent Spaulding | datAdrenaline | Access MVP

"Andrew P." <(E-Mail Removed)> wrote in message
news:87771994-2C4E-46C0-9DAC-(E-Mail Removed)...
> Hi All
>
> Im a bit confused as to why Access2007 throws errors back when I use the
> following statement with set value or set temp var, but is fine with
> msgbox:
>
> =DLookUp("[AutoASC]","[RetailerASCList]","ID=
> [Forms]![ClaimEntry]![Store]")
>
> When it complains, it throws a screen with "Action Failed" and error
> number
> 2950.
>
> Any help would be great
> Thanks
> Andrew

On Wed, 3 Sep 2008 20:10:04 -0700, Andrew P.
<(E-Mail Removed)> wrote:

Try this:
=DLookUp("[AutoASC]","[RetailerASCList]","ID=" &
[Forms]![ClaimEntry]![Store])

-Tom.
Microsoft Access MVP


>Hi All
>
>Im a bit confused as to why Access2007 throws errors back when I use the
>following statement with set value or set temp var, but is fine with msgbox:
>
>=DLookUp("[AutoASC]","[RetailerASCList]","ID= [Forms]![ClaimEntry]![Store]")
>
>When it complains, it throws a screen with "Action Failed" and error number
>2950.
>
>Any help would be great
>Thanks
>Andrew

Hi Brent

Different error now:
"The object doesnt contain the Automation object 'ILLA2530.' "

Interestingly, ILLA2530 is the correct lookup that I want inserted into the
combo. Not sure if it makes any difference, but Im using the builder, not VBA.

Regards
Andrew

"Brent Spaulding (datAdrenaline)" wrote:

> I would suggest that you create the literal string in your criteria of the
> DLookup() ...
>
> If "ID" is Numeric ...
> =DLookUp("[AutoASC]","[RetailerASCList]","ID=" &
> Nz([Forms]![ClaimEntry]![Store],0))
>
> If "ID" is Text ...
> =DLookUp("[AutoASC]","[RetailerASCList]","ID='" &
> Nz([Forms]![ClaimEntry]![Store],"") & "'")
>
> Also, make sure the form is open before you run the macro ..
>
> --
> Brent Spaulding | datAdrenaline | Access MVP
>
> "Andrew P." <(E-Mail Removed)> wrote in message
> news:87771994-2C4E-46C0-9DAC-(E-Mail Removed)...
> > Hi All
> >
> > Im a bit confused as to why Access2007 throws errors back when I use the
> > following statement with set value or set temp var, but is fine with
> > msgbox:
> >
> > =DLookUp("[AutoASC]","[RetailerASCList]","ID=
> > [Forms]![ClaimEntry]![Store]")
> >
> > When it complains, it throws a screen with "Action Failed" and error
> > number
> > 2950.
> >
> > Any help would be great
> > Thanks
> > Andrew
>
>
>

Hi Tom

I still get the same "Action Failed" screen with the same error number.
Regards
Andrew

"Tom van Stiphout" wrote:

> On Wed, 3 Sep 2008 20:10:04 -0700, Andrew P.
> <(E-Mail Removed)> wrote:
>
> Try this:
> =DLookUp("[AutoASC]","[RetailerASCList]","ID=" &
> [Forms]![ClaimEntry]![Store])
>
> -Tom.
> Microsoft Access MVP
>
>
> >Hi All
> >
> >Im a bit confused as to why Access2007 throws errors back when I use the
> >following statement with set value or set temp var, but is fine with msgbox:
> >
> >=DLookUp("[AutoASC]","[RetailerASCList]","ID= [Forms]![ClaimEntry]![Store]")
> >
> >When it complains, it throws a screen with "Action Failed" and error number
> >2950.
> >
> >Any help would be great
> >Thanks
> >Andrew
>

Hi All

Not sure how I thought of this, but got it going this morning!! - just
needed to include quotes before and after the dlookup.
="'" &
DLookUp("[AutoASC]","[RetailerASCList]","ID=[Forms]![ClaimEntry]![Store]") &
"'"
-----------------------------------------------
"Andrew P." wrote:

> Hi Tom
>
> I still get the same "Action Failed" screen with the same error number.
> Regards
> Andrew
>
> "Tom van Stiphout" wrote:
>
> > On Wed, 3 Sep 2008 20:10:04 -0700, Andrew P.
> > <(E-Mail Removed)> wrote:
> >
> > Try this:
> > =DLookUp("[AutoASC]","[RetailerASCList]","ID=" &
> > [Forms]![ClaimEntry]![Store])
> >
> > -Tom.
> > Microsoft Access MVP
> >
> >
> > >Hi All
> > >
> > >Im a bit confused as to why Access2007 throws errors back when I use the
> > >following statement with set value or set temp var, but is fine with msgbox:
> > >
> > >=DLookUp("[AutoASC]","[RetailerASCList]","ID= [Forms]![ClaimEntry]![Store]")
> > >
> > >When it complains, it throws a screen with "Action Failed" and error number
> > >2950.
> > >
> > >Any help would be great
> > >Thanks
> > >Andrew
> >