DLookUp

[Nanette]

I copied the syntax exactly from MS Help (substituting my data of course),
but keep getting the #Name in the text box. It is in the Control Source of a
text box.

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms!sfrmLineItem!RFQNo)

 

[Douglas J. Steele]

From your naming, I'm guessing you're trying to refer to a control on a
subform.

Subforms are different than regular forms. They're not actually put in the
Forms collection, so you can't refer to them that way: you need to go via
the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

This assumes that the name of the subform control on the parent form is
named sfrmLineItem. Depending on how you added the subform to the parent
form, the subform control may actually be named differently than the name of
the form that's being used as a subform. If they're different, make sure you
use the name of the subform control.

 

[Dirk Goldgar]

I copied the syntax exactly from MS Help (substituting my data of
course), but keep getting the #Name in the text box. It is in the
Control Source of a text box.

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms!sfrmLineItem!RFQNo)

It could be that this name has the clue: "sfrmLineItem". If
sfrmLineItem is a subform, then it isn't open in its own right, and is
not a member of the Forms collection. Is this a subform on the form
that contains the control with the DLookup controlsource expression? If
so, and if the name of the subform *control* is really "sfrmLineItem",
then maybe this would be a correct expression:

=DLookUp("RFQType","RFQ","RFQNo =" & [sfrmLineItem]![RFQNo])

 

[Nanette]

Don't understand, now I'm getting #Error

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms![RFQ
Selection]!sfrmLineItem.Form!RFQNo)

From your naming, I'm guessing you're trying to refer to a control on a
subform.

Subforms are different than regular forms. They're not actually put in the
Forms collection, so you can't refer to them that way: you need to go via
the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

This assumes that the name of the subform control on the parent form is
named sfrmLineItem. Depending on how you added the subform to the parent
form, the subform control may actually be named differently than the name of
the form that's being used as a subform. If they're different, make sure you
use the name of the subform control.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I copied the syntax exactly from MS Help (substituting my data of course),
but keep getting the #Name in the text box. It is in the Control Source of
a
text box.

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms!sfrmLineItem!RFQNo)

 

[Douglas J. Steele]

What's the data type of RFQNo? If it's text, you need quotes around the
value:

=DLookUp("[RFQType]","RFQ","[RFQNo] ='" & Forms![RFQ
Selection]!sfrmLineItem.Form!RFQNo & "'")

Exagerated for clarity, that's

=DLookUp("[RFQType]","RFQ","[RFQNo] = ' " & Forms![RFQ
Selection]!sfrmLineItem.Form!RFQNo & " ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Don't understand, now I'm getting #Error

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms![RFQ
Selection]!sfrmLineItem.Form!RFQNo)

From your naming, I'm guessing you're trying to refer to a control on a
subform.

Subforms are different than regular forms. They're not actually put in
the
Forms collection, so you can't refer to them that way: you need to go via
the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

This assumes that the name of the subform control on the parent form is
named sfrmLineItem. Depending on how you added the subform to the parent
form, the subform control may actually be named differently than the name
of
the form that's being used as a subform. If they're different, make sure
you
use the name of the subform control.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I copied the syntax exactly from MS Help (substituting my data of
course),
but keep getting the #Name in the text box. It is in the Control Source
of
a
text box.

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms!sfrmLineItem!RFQNo)

 

[Nanette]

Maybe I need to explain better.

I have a text box on a sub form that has the DLookUp code in it, in the
control source.

There is a combo box on the main form that looks up the RFQ # (titled
cboRFQNo).

The data (RFQ Type) is coming from the RFQ table.

Does this help??

From your naming, I'm guessing you're trying to refer to a control on a
subform.

Subforms are different than regular forms. They're not actually put in the
Forms collection, so you can't refer to them that way: you need to go via
the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

This assumes that the name of the subform control on the parent form is
named sfrmLineItem. Depending on how you added the subform to the parent
form, the subform control may actually be named differently than the name of
the form that's being used as a subform. If they're different, make sure you
use the name of the subform control.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I copied the syntax exactly from MS Help (substituting my data of course),
but keep getting the #Name in the text box. It is in the Control Source of
a
text box.

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms!sfrmLineItem!RFQNo)

 

[Dirk Goldgar]

Maybe I need to explain better.

I have a text box on a sub form that has the DLookUp code in it, in
the control source.

There is a combo box on the main form that looks up the RFQ # (titled
cboRFQNo).

The data (RFQ Type) is coming from the RFQ table.

Does this help??

From your naming, I'm guessing you're trying to refer to a control
on a subform.

Subforms are different than regular forms. They're not actually put
in the Forms collection, so you can't refer to them that way: you
need to go via the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

So the controlsource expression is on the *subform*, and the control
that provides the RFQNo is (a) on the parent form, and (b) *not* named
"RFQNo". Try this:

=DLookUp("[RFQType]","RFQ",
"[RFQNo] =" & [Parent]![cboRFQNo])

Note that I broke that onto two lines for clarity in posting, but it
would really be all one line in the text box's ControlSource property.

But if RFQType is entirely dependent on the parent form's RFQ number,
why is this being displayed on the subform?

 

[Douglas J. Steele]

I think it would make more sense to base the subform on a query that joins
the existing data to the RFQ table so that it has the appropriate data for
each row.

If you're trying to base the DLookup on the value in cboRFQNo, why did you
have Forms!sfrmLineItem!RFQNo originally? That should presumably be
Forms!NameOfParentForm!cboRFQNo

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Maybe I need to explain better.

I have a text box on a sub form that has the DLookUp code in it, in the
control source.

There is a combo box on the main form that looks up the RFQ # (titled
cboRFQNo).

The data (RFQ Type) is coming from the RFQ table.

Does this help??

From your naming, I'm guessing you're trying to refer to a control on a
subform.

Subforms are different than regular forms. They're not actually put in
the
Forms collection, so you can't refer to them that way: you need to go via
the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

This assumes that the name of the subform control on the parent form is
named sfrmLineItem. Depending on how you added the subform to the parent
form, the subform control may actually be named differently than the name
of
the form that's being used as a subform. If they're different, make sure
you
use the name of the subform control.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I copied the syntax exactly from MS Help (substituting my data of
course),
but keep getting the #Name in the text box. It is in the Control Source
of
a
text box.

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms!sfrmLineItem!RFQNo)

 

[Nanette]

Hi Doug,

In response to your question: If you're trying to base the DLookup on the
value in cboRFQNo, why did you have Forms!sfrmLineItem!RFQNo originally?

I'm a newbie and used the MS Help to do this. Their instructions were really
good until it reached the "criteria section". At that point the directions
were very vague, so you could say I was guessing and experimenting.

I think it would make more sense to base the subform on a query that joins
the existing data to the RFQ table so that it has the appropriate data for
each row.

If you're trying to base the DLookup on the value in cboRFQNo, why did you
have Forms!sfrmLineItem!RFQNo originally? That should presumably be
Forms!NameOfParentForm!cboRFQNo

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Maybe I need to explain better.

I have a text box on a sub form that has the DLookUp code in it, in the
control source.

There is a combo box on the main form that looks up the RFQ # (titled
cboRFQNo).

The data (RFQ Type) is coming from the RFQ table.

Does this help??

From your naming, I'm guessing you're trying to refer to a control on a
subform.

Subforms are different than regular forms. They're not actually put in
the
Forms collection, so you can't refer to them that way: you need to go via
the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

This assumes that the name of the subform control on the parent form is
named sfrmLineItem. Depending on how you added the subform to the parent
form, the subform control may actually be named differently than the name
of
the form that's being used as a subform. If they're different, make sure
you
use the name of the subform control.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I copied the syntax exactly from MS Help (substituting my data of
course),
but keep getting the #Name in the text box. It is in the Control Source
of
a
text box.

=DLookUp("[RFQType]","RFQ","[RFQNo] =" & Forms!sfrmLineItem!RFQNo)

 

[Nanette]

Hi Dirk,

I was thinking that since I have most of the data in the subform, I should
place the text box there. I'm a newbie and still learning the ins and outs.
Is it more appropriate to put the text box in the main form? If yes, any
specific reason why?

Maybe I need to explain better.

I have a text box on a sub form that has the DLookUp code in it, in
the control source.

There is a combo box on the main form that looks up the RFQ # (titled
cboRFQNo).

The data (RFQ Type) is coming from the RFQ table.

Does this help??

From your naming, I'm guessing you're trying to refer to a control
on a subform.

Subforms are different than regular forms. They're not actually put
in the Forms collection, so you can't refer to them that way: you
need to go via the parent form:

=DLookUp("[RFQType]","RFQ","[RFQNo] =" &
Forms!NameOfParentForm!sfrmLineItem.Form!RFQNo)

So the controlsource expression is on the *subform*, and the control
that provides the RFQNo is (a) on the parent form, and (b) *not* named
"RFQNo". Try this:

=DLookUp("[RFQType]","RFQ",
"[RFQNo] =" & [Parent]![cboRFQNo])

Note that I broke that onto two lines for clarity in posting, but it
would really be all one line in the text box's ControlSource property.

But if RFQType is entirely dependent on the parent form's RFQ number,
why is this being displayed on the subform?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,

I was thinking that since I have most of the data in the subform, I
should place the text box there. I'm a newbie and still learning the
ins and outs. Is it more appropriate to put the text box in the main
form? If yes, any specific reason why?

I don't know your application, of course, but to me it seems natural
that the display of data specific to the main form's record should go on
the main form. The main form/subform arrangement normally models a
parent/child relationship, with the attributes of the parent entity
displayed on the main form, and the attributes of each related child
entity displayed on the subform. If RFQType is wholly determined by the
parent record, and doesn't vary depending on which child record you are
looking at, then it seems to me that it belongs on the main form. Why
show the same invariant, unmodifiable information over and over, for
each subform record?

Maybe if you describe the nature and relationships of the tables
involved, it will clarify the situation. It could be that you have an
unusual situation that invalidates my uninformed reaction.