DLookup default Value

[rbb101]

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] & "")

 

[Tom Lake]

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

 

[Allen Browne]

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

 

[rbb101]

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

Thanks.

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

Reply to group, rather than allenbrowne at mvps dot org.

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] & "")

 

[Linq Adams via AccessMonster.com]

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

 

[rbb101]

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.