Code not OK with Set Temp Var or Set Value but is OK with MsgBox

  • Thread starter Thread starter Andrew P.
  • Start date Start date
A

Andrew P.

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 ..
 
On Wed, 3 Sep 2008 20:10:04 -0700, Andrew P.

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

-Tom.
Microsoft Access MVP
 
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) said:
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. said:
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
Click to expand...
Click to expand...
 
Hi Tom

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

Tom van Stiphout said:
On Wed, 3 Sep 2008 20:10:04 -0700, Andrew P.

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
Click to expand...
Click to expand...
 
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. said:
Hi Tom

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

Tom van Stiphout said:
On Wed, 3 Sep 2008 20:10:04 -0700, Andrew P.

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top