DLookup default Value

  • Follow


I am having trouble getting the correct syntax using DLookup as a default 
value in a text box on a form.  

Form Name:  FrmMainInput
Unbound control Name: FunctionNumber

I am trying to get the default value to look up the FunctionNumber, using 
the FunctionName from the table TblFunction.  I am using the expression below 
in the default value of the FunctionNumber control, but am not getting any 
value.

=DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" & 
[Forms]![FrmMainInput]![FunctionNumber] & "")


0
Reply Utf 2/22/2008 4:18:13 PM

"rbb101" <rbb101@discussions.microsoft.com> wrote in message 
news:E3C9BFDF-EEEA-4C59-9B80-16366B8D173B@microsoft.com...
>I am having trouble getting the correct syntax using DLookup as a default
> value in a text box on a form.
>
> Form Name:  FrmMainInput
> Unbound control Name: FunctionNumber
>
> I am trying to get the default value to look up the FunctionNumber, using
> the FunctionName from the table TblFunction.  I am using the expression below
> in the default value of the FunctionNumber control, but am not getting any
> value.
>
> =DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" &
> [Forms]![FrmMainInput]![FunctionNumber] & "")

If FunctionNumber is a numeric field, drop the & "'" from the end.

=DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" & 
[Forms]![FrmMainInput]![FunctionNumber])

If it's a text field, you need a single quote between the second equal
sign and the double quote.

=DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] ='" & 
[Forms]![FrmMainInput]![FunctionNumber] & "")

Tom Lake 

0
Reply Tom 2/22/2008 4:37:29 PM


This can't work. The timing is wrong.

Access applies the DefaultValue as soon as you move to a new record, before 
you start the entry. At that time, the FunctionNumber has not been filled 
it.

Use the AfterUpdate event of the FunctionNumber text box to assign the 
value. Example in the 2nd part of this article:
    Calculated Fields
at:
    http://allenbrowne.com/casu-14.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rbb101" <rbb101@discussions.microsoft.com> wrote in message
news:E3C9BFDF-EEEA-4C59-9B80-16366B8D173B@microsoft.com...
>I am having trouble getting the correct syntax using DLookup as a default
> value in a text box on a form.
>
> Form Name:  FrmMainInput
> Unbound control Name: FunctionNumber
>
> I am trying to get the default value to look up the FunctionNumber, using
> the FunctionName from the table TblFunction.  I am using the expression 
> below
> in the default value of the FunctionNumber control, but am not getting any
> value.
>
> =DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" &
> [Forms]![FrmMainInput]![FunctionNumber] & "") 

0
Reply Allen 2/23/2008 7:05:37 AM

Thanks Allen, that makes sense, but I can't get the syntax correct.  Can you 
help out with that.  

Thanks.

"Allen Browne" wrote:

> This can't work. The timing is wrong.
> 
> Access applies the DefaultValue as soon as you move to a new record, before 
> you start the entry. At that time, the FunctionNumber has not been filled 
> it.
> 
> Use the AfterUpdate event of the FunctionNumber text box to assign the 
> value. Example in the 2nd part of this article:
>     Calculated Fields
> at:
>     http://allenbrowne.com/casu-14.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "rbb101" <rbb101@discussions.microsoft.com> wrote in message
> news:E3C9BFDF-EEEA-4C59-9B80-16366B8D173B@microsoft.com...
> >I am having trouble getting the correct syntax using DLookup as a default
> > value in a text box on a form.
> >
> > Form Name:  FrmMainInput
> > Unbound control Name: FunctionNumber
> >
> > I am trying to get the default value to look up the FunctionNumber, using
> > the FunctionName from the table TblFunction.  I am using the expression 
> > below
> > in the default value of the FunctionNumber control, but am not getting any
> > value.
> >
> > =DLookUp("[FunctionNumber] ","TblFunction"," [FunctionName] =" &
> > [Forms]![FrmMainInput]![FunctionNumber] & "") 
> 
> 
0
Reply Utf 2/23/2008 4:27:01 PM

The syntax notwithstanding, your code doesn't make a lot of sense. You're
trying to set the value of your control

[FrmMainInput]![FunctionNumber]

and you're asking Access to do this by looking up the field

[FunctionNumber] from the table "TblFunction"

by comparing the field [FunctionNumber] from the table "TblFunction" to the
control 

[FrmMainInput]![FunctionNumber]

which is empty!

You're trying to fill [FrmMainInput]![FunctionNumber] by using [FrmMainInput]!
[FunctionNumber] as the criteria!

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1

0
Reply Linq 2/23/2008 6:55:57 PM

Are you indicating this cannot be done, or that I do not have the correct 
critieria.  I am out of my element when using code.

What I am trying to do is the default value on the control [FunctionNumber] 
on the [FrmMainInput] look up it's value by using the critieria 
[FunctionName] from the table [TblFunction].  [FunctionName] is a field on 
the same form.

I appreciate your feedback.  Thanks.

"Linq Adams via AccessMonster.com" wrote:

> The syntax notwithstanding, your code doesn't make a lot of sense. You're
> trying to set the value of your control
> 
> [FrmMainInput]![FunctionNumber]
> 
> and you're asking Access to do this by looking up the field
> 
> [FunctionNumber] from the table "TblFunction"
> 
> by comparing the field [FunctionNumber] from the table "TblFunction" to the
> control 
> 
> [FrmMainInput]![FunctionNumber]
> 
> which is empty!
> 
> You're trying to fill [FrmMainInput]![FunctionNumber] by using [FrmMainInput]!
> [FunctionNumber] as the criteria!
> 
> -- 
> There's ALWAYS more than one way to skin a cat!
> 
> Answers/posts based on Access 2000/2003
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1
> 
> 
0
Reply Utf 2/25/2008 12:34:02 PM

5 Replies
666 Views

(page loaded in 0.138 seconds)


Reply: