Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

DLookup function displays "#Error" on new record?

383 views
Skip to first unread message

Bob Lothar

unread,
Jul 26, 2001, 6:25:37 PM7/26/01
to
Version: Access 97

I'm using the DLookup( ) function in a 'text box' control on my form to
look-up a value in a table depending on the value on another control (Combo
box) on the form.

The function works fine until I 'Add' a new record upon which there no
longer a value for the DLookup function and hence the word Error# is
displayed in the text box control using the DLookup function.

The access knowledge base states that when you move to a new record, the
DLookup() text boxes that update automatically will have "#Error" in them
until you enter something in the text box that the DLookup function is
referencing.

When a New record is entered, is there a way to suppress the word #Error
until the user has selected an option from my combo box?
Maybe I missed something in the knowledge base?

TIA

Rob

unread,
Jul 26, 2001, 6:54:36 PM7/26/01
to
Set the recordsource to null on the textbox.

Set the Textbox's source in the afterUpdate event of the combobox

"Bob Lothar" <count...@hotmail.com> wrote in message
news:9jq5k1$5vj$1...@newsg1.svr.pol.co.uk...

Allen Browne

unread,
Jul 26, 2001, 9:50:57 PM7/26/01
to
The problem is most likely that the 3rd argument doesn't make sense
at a new record.

Example: your text box might have a ControlSource like this:
=DLookup("SomeField", "SomeTable", "AField = " & [MyKeyField])

At a new record, MyKeyField has no value yet, so the 3rd argument
becomes just:
"AField = "
which is an error.

Use Nz() to solve the error, e.g.:
=DLookup("SomeField", "SomeTable", "AField = " & Nz([MyKeyField],0))

--
Perth, Western Australia
Tips for MS Access users at:
http://odyssey.apana.org.au/~abrowne

alt.talk.bollocks

unread,
Jul 27, 2001, 7:25:15 PM7/27/01
to
Is there a way of suppressing the message ?

The arguments are corrrect as stated in the access knowledge base.


Which event do I use?
OnCurrent?


Allen Browne <abr...@odyssey.apana.org.au> wrote in message
news:3B60C901...@odyssey.apana.org.au...

Allen Browne

unread,
Jul 28, 2001, 1:38:59 AM7/28/01
to
Use a real name if you want to be taken seriously here.


"alt.talk.bollocks" wrote:
[snip: nothing of value]

Dogs ßollocks

unread,
Jul 28, 2001, 7:21:26 PM7/28/01
to
Hows this then smart arse...


> "abr...@odyssey.apana.org.au" wrote:

Allen Browne <abr...@odyssey.apana.org.au> wrote in message

news:3B624FF3...@odyssey.apana.org.au...


> Use a real name if you want to be taken seriously here.
>
>
> "alt.talk.bollocks" wrote:
> [snip: nothing of value]


[snip: If its not worth it, don't bother]


Wayne Gillespie

unread,
Jul 28, 2001, 9:17:17 PM7/28/01
to
On Sun, 29 Jul 2001 00:21:26 +0100, "Dogs ßollocks"
<count...@hotmail.com> wrote:

No that sucks too smart arse...

How about "Bob Lothar"

0 new messages