DLookUp function comes up with only the first entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all...

I am having problems with a database I am building for work. The form I am
creating contains questoins along with the requirements they fall under. To
select the requirments, I used a combo box which looks up the values in the
Requirements table. Next to the combo box is a text box which I want to
contain the name of the Requirement selected. To do this I used a "DLookUp"
function.

=DLookUp("[Requirement Name]","[ISO
Requirements]","[Requirement]=Forms![Questions Form].[Requirement]")

Here's the problem, this function only brings up the first name in the ISO
Requirements form in the Requirement name column, rather than relating the
content of the text box to the number selected in the combo box. Is my
function wrong? Am I doing something wrong?

Also, under each requirement there are sub requiremnts. Much like the
section on the form for Requirements, I have a section for sub-requirements.
I would like the combo box in the sub-requirements section to contain only
those subrequirements which fall under the requirement selected, rather than
the whole list of sub-requirements. Is this possible? If so how?

Thanks for the help
 
Try putting the reference to the control outside of the quotes:

Assuming Requirement is a numeric field:

=DLookUp("[Requirement Name]","[ISO Requirements]","[Requirement]=" &
Forms![Questions Form].[Requirement])

If it's a text field, you'll need quotes. If there's a chance that the
requirement can include apostrophes, you're best off using:

=DLookUp("[Requirement Name]","[ISO Requirements]","[Requirement]=" &
Chr$(34) & Forms![Questions Form].[Requirement] & Chr$(34))
 
That's a little confusing...

You have seperate fields in your table called "Requirement Name" and
"Requirement"? Are you using DLookup properly? The way it reads now,
you're saying, "Give me what's in the 'Requirement Name' field in the
'ISO Requirements' table where the field 'Requirement' in that same
table equals 'Forms![Questions Form].[Requirement]'"

Is that correct?
 
Hi all...

I am having problems with a database I am building for work. The form I am
creating contains questoins along with the requirements they fall under. To
select the requirments, I used a combo box which looks up the values in the
Requirements table. Next to the combo box is a text box which I want to
contain the name of the Requirement selected. To do this I used a "DLookUp"
function.

=DLookUp("[Requirement Name]","[ISO
Requirements]","[Requirement]=Forms![Questions Form].[Requirement]")

Here's the problem, this function only brings up the first name in the ISO
Requirements form in the Requirement name column, rather than relating the
content of the text box to the number selected in the combo box. Is my
function wrong? Am I doing something wrong?

Also, under each requirement there are sub requiremnts. Much like the
section on the form for Requirements, I have a section for sub-requirements.
I would like the combo box in the sub-requirements section to contain only
those subrequirements which fall under the requirement selected, rather than
the whole list of sub-requirements. Is this possible? If so how?

Thanks for the help
Click to expand...

Your syntax is incorrect.
As written, your code is looking for a literal Forms![Questions
Form].[Requirement] value, not the value IN the form control.

The value of the form control must be concatenated into the Where
clause. The actual syntax will depend upon the datatype of the
[Requirement] field.
If [Requirement] is Text datatype, then:

=DLookUp("[Requirement Name]","[ISO Requirements]","[Requirement]= '"
& Forms![Questions Form]![Requirement] & "'")

However, if [Requirement] is a Number datatype, then:

=DLookUp("[Requirement Name]","[ISO Requirements]", "[Requirement]= "
& Forms![Questions Form]![Requirement])
 
fredg said:
Hi all...

I am having problems with a database I am building for work. The
form I am creating contains questoins along with the requirements
they fall under. To select the requirments, I used a combo box
which looks up the values in the Requirements table. Next to the
combo box is a text box which I want to contain the name of the
Requirement selected. To do this I used a "DLookUp" function.

=DLookUp("[Requirement Name]","[ISO
Requirements]","[Requirement]=Forms![Questions Form].[Requirement]")

Here's the problem, this function only brings up the first name in
the ISO Requirements form in the Requirement name column, rather
than relating the content of the text box to the number selected in
the combo box. Is my function wrong? Am I doing something wrong?

Also, under each requirement there are sub requiremnts. Much like
the section on the form for Requirements, I have a section for
sub-requirements. I would like the combo box in the sub-requirements
section to contain only those subrequirements which fall under the
requirement selected, rather than the whole list of
sub-requirements. Is this possible? If so how?

Thanks for the help
Click to expand...

Your syntax is incorrect.
As written, your code is looking for a literal Forms![Questions
Form].[Requirement] value, not the value IN the form control.

The value of the form control must be concatenated into the Where
clause. The actual syntax will depend upon the datatype of the
[Requirement] field.
If [Requirement] is Text datatype, then:

=DLookUp("[Requirement Name]","[ISO Requirements]","[Requirement]= '"
& Forms![Questions Form]![Requirement] & "'")

However, if [Requirement] is a Number datatype, then:

=DLookUp("[Requirement Name]","[ISO Requirements]", "[Requirement]= "
& Forms![Questions Form]![Requirement])
Click to expand...

Fred, I have to disagree. Dave's original syntax was valid, whether it
is giving the desired result or no. The DLookup function is able to
resolve references to form controls in the criteria string literal, just
as DoCmd.RunSQL is able to. It can even do it in multiple records shown
on a continuous form. There must be more to this probem than the
validity of the syntax.
 
Dave said:
Hi all...

I am having problems with a database I am building for work. The
form I am creating contains questoins along with the requirements
they fall under. To select the requirments, I used a combo box which
looks up the values in the Requirements table. Next to the combo box
is a text box which I want to contain the name of the Requirement
selected. To do this I used a "DLookUp" function.

=DLookUp("[Requirement Name]","[ISO
Requirements]","[Requirement]=Forms![Questions Form].[Requirement]")

Here's the problem, this function only brings up the first name in
the ISO Requirements form in the Requirement name column, rather than
relating the content of the text box to the number selected in the
combo box. Is my function wrong? Am I doing something wrong?
Click to expand...

I'm not sure what's wrong -- see my reply to fredg's posting for why I
think the syntax itself is not the problem -- but I suggest you not use
a DLookup expression for this text box's ControlSource. Instead, make
sure the [Requirement] combo box's RowSource includes both the
requirement's ID and name, and then either set the combo box's
ColumnWidths property to display the requirement name rather than the ID
(while storing the ID), or else use this separate text box to pick up
the requirement name from the column of the combo box that contains it,
using a ControlSource similar to this:

=[Requirement].[Column](1)

In the above, Column(1) is actually the second column of the combo
box -- the first column is Column(0).
 
Back
Top