dlookup

I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" & Forms!
[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later used
as a subform,the expression produces a #NAME# error.

What gives?

A subform isn't "open". It is an object on the parent form. Change the last
part to:

Forms![Name Of Parent Form]![Name Of Subform Control].Form!Office

[Name Of Subform Control] is the name of the control that holds the subform,
not the subform itself. To get this name, open the parent form in design
mode, open the Properties sheet, and click on the subform ONE time. The
properties sheet will show the name of the subform control. If you click on
the subform twice, you'll be in the subform and the Properties sheet will
show the name of the subform, not the control holding it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in message
...
I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" & Forms!
[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later used
as a subform,the expression produces a #NAME# error.

What gives?

That worked. But now the field gives a #Name? error on the
original form. In addition, the form with the subform on
it is in turn a subform on the primary form. I have a
nested form with three levels. If I get it to work on the
primary form then neither of the subforms work. If I get
it to work on the secondary form then the primary and
tertiary dont work.

I use the subforms as primary forms also, via buttons on
the switchboard. What ever happened to the concept of
inheritance?

How can I get all three forms to work?



-----Original Message-----
A subform isn't "open". It is an object on the parent
form. Change the last
part to:

Forms![Name Of Parent Form]![Name Of Subform
Control].Form!Office

[Name Of Subform Control] is the name of the control that
holds the subform,
not the subform itself. To get this name, open the parent
form in design
mode, open the Properties sheet, and click on the subform
ONE time. The
properties sheet will show the name of the subform
control. If you click on
the subform twice, you'll be in the subform and the
Properties sheet will
show the name of the subform, not the control holding it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in message
...
I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" & Forms!
[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later
used
as a subform,the expression produces a #NAME# error.

What gives?


.

I made 3 copies of the tertiary form and two copies of the
secondary form. The second copy of the tertiary goes on
the first copy of the secondary. The second copy of the
secondary goes on the primary form and carries with it the
third copy of the tertiary form.

Now each copy has it own subforms and the callouts for the
dlookup are all different, and all the forms work.

Bill Gates would say "That's the stupidest thing I ever
heard of."


-----Original Message-----
A subform isn't "open". It is an object on the parent
form. Change the last
part to:

Forms![Name Of Parent Form]![Name Of Subform
Control].Form!Office

[Name Of Subform Control] is the name of the control that
holds the subform,
not the subform itself. To get this name, open the parent
form in design
mode, open the Properties sheet, and click on the subform
ONE time. The
properties sheet will show the name of the subform
control. If you click on
the subform twice, you'll be in the subform and the
Properties sheet will
show the name of the subform, not the control holding it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in message
...
I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" & Forms!
[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later
used
as a subform,the expression produces a #NAME# error.

What gives?


.

Miaplacidus wrote:
That worked. But now the field gives a #Name? error on the
original form. In addition, the form with the subform on
it is in turn a subform on the primary form. I have a
nested form with three levels. If I get it to work on the
primary form then neither of the subforms work. If I get
it to work on the secondary form then the primary and
tertiary dont work.

I use the subforms as primary forms also, via buttons on
the switchboard. What ever happened to the concept of
inheritance?

I don't know how this should be related to inheritance. But what you should
do is to check if there is any parent and then access the control in the
respective way. You need some VBA code like this:

If Me.Parent Is Nothing Then
' Form is used as a primary form
Else
' Form is used as a subform
End If

HTH,
Boris

[...]

It is possible to nest the command line I used previously. Also, receiving
#Name is frequently an indication that the control on the form and the field
it is bound to have the same name so Access doesn't know if you are
referring to the control or the field. If this is the case try changing the
name of the control. (i.e. Field Name: MyField, Control Name: txtMyField).

Nesting to subforms, 2nd subform referred to from first:
Forms![Name Of Parent Form]![Name Of Subform Control].Form![Name Of 2nd
Subform Control].Form!txtMyTextbox

If you are doing this from the parent form, you can remove Forms![Name Of
Parent Form]. To refer to the 2nd subform from the 1st subform, you could
start at [Name of 2nd Subform Control]. To refer to a 2nd subform that is on
the Parent form instead of nested into the 1st subform from the 1st subform:

Parent![Name Of 2nd Subform Control].Form!txtMyTextbox

--
Wayne Morgan
Microsoft Access MVP


"Miaplacidus" wrote in message
...
That worked. But now the field gives a #Name? error on the
original form. In addition, the form with the subform on
it is in turn a subform on the primary form. I have a
nested form with three levels. If I get it to work on the
primary form then neither of the subforms work. If I get
it to work on the secondary form then the primary and
tertiary dont work.

I use the subforms as primary forms also, via buttons on
the switchboard. What ever happened to the concept of
inheritance?

How can I get all three forms to work?



-----Original Message-----
A subform isn't "open". It is an object on the parent
form. Change the last
part to:

Forms![Name Of Parent Form]![Name Of Subform
Control].Form!Office

[Name Of Subform Control] is the name of the control that
holds the subform,
not the subform itself. To get this name, open the parent
form in design
mode, open the Properties sheet, and click on the subform
ONE time. The
properties sheet will show the name of the subform
control. If you click on
the subform twice, you'll be in the subform and the
Properties sheet will
show the name of the subform, not the control holding it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in message
...
I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" & Forms!
[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later
used
as a subform,the expression produces a #NAME# error.

What gives?


.

I don't understand. I don't think this is a matter of
referencing. I have a form with an unbound field that uses
a dlookup expression in the fields control source. It
works properly. But when I use that form as a subform the
field no longer works properly: it gives a #Name? error
for a field that otherwise works properly. If I change the
reference so that the expression works as a subform, then
the original form no longer works, and presents a #Name
error. It also doesn't work as a sub,subform. Again, if I
change the expression so that it works as a sub, subform,
then it no longer works as either a subform or as a main
form.

What I need is an expression that will work for all three
cases. In every case the expression lives in the control
source of the unbound field on the lowest level form.

When you use the subform tool to import a working form to
a higher level form, the expectation is that the lower
level form will still work. I don't need (or want) to do
anything at the level of the first or second parent
containers.

The approach offered by Boris might work where the
expression depends on a pair of IF statements or a CASE
switch, but I'm not sure I know how to put an If statement
in a control source. I suppose I could put it in one of
the events, but since this field is for information only
it's not clear to me that any of the events apply. There
is no reason to enter or leave the field or click on it,
etc.

In a sense that is what I did with the control buttons on
the switchboard. If you click the button for the highest
level form it opens with a copy of the lowest level form
that has the right reference. If you click the button for
the second level form it opens with another copy of the
lowest level form that has the correct reference. If you
open the lowest level of the form you get a copy with the
expression I quoted.

I get the idea of IF me.parent is Nothing, but that only
works for the second level form. How do you do IF
me.grandparent is Nothing? If me.Parent.Parent is Nothing?








-----Original Message-----
It is possible to nest the command line I used
previously. Also, receiving
#Name is frequently an indication that the control on the
form and the field
it is bound to have the same name so Access doesn't know
if you are
referring to the control or the field. If this is the
case try changing the
name of the control. (i.e. Field Name: MyField, Control
Name: txtMyField).

Nesting to subforms, 2nd subform referred to from first:
Forms![Name Of Parent Form]![Name Of Subform
Control].Form![Name Of 2nd
Subform Control].Form!txtMyTextbox

If you are doing this from the parent form, you can
remove Forms![Name Of
Parent Form]. To refer to the 2nd subform from the 1st
subform, you could
start at [Name of 2nd Subform Control]. To refer to a 2nd
subform that is on
the Parent form instead of nested into the 1st subform
from the 1st subform:

Parent![Name Of 2nd Subform Control].Form!txtMyTextbox

--
Wayne Morgan
Microsoft Access MVP


"Miaplacidus" wrote in message
...
That worked. But now the field gives a #Name? error on
the
original form. In addition, the form with the subform on
it is in turn a subform on the primary form. I have a
nested form with three levels. If I get it to work on
the
primary form then neither of the subforms work. If I get
it to work on the secondary form then the primary and
tertiary dont work.

I use the subforms as primary forms also, via buttons on
the switchboard. What ever happened to the concept of
inheritance?

How can I get all three forms to work?



-----Original Message-----
A subform isn't "open". It is an object on the parent
form. Change the last
part to:

Forms![Name Of Parent Form]![Name Of Subform
Control].Form!Office

[Name Of Subform Control] is the name of the control
that
holds the subform,
not the subform itself. To get this name, open the
parent
form in design
mode, open the Properties sheet, and click on the
subform
ONE time. The
properties sheet will show the name of the subform
control. If you click on
the subform twice, you'll be in the subform and the
Properties sheet will
show the name of the subform, not the control holding
it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in
message
.. .
I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" &
Forms!
[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later
used
as a subform,the expression produces a #NAME# error.

What gives?


.



.

Honestly, it sounds to me like you need to be posting some of your code
and giving some more detail of your tables and subforms, and how things
are related.

grep

Miaplacidus wrote:

I don't understand. I don't think this is a matter of
referencing. I have a form with an unbound field that uses
a dlookup expression in the fields control source. It
works properly. But when I use that form as a subform the
field no longer works properly: it gives a #Name? error
for a field that otherwise works properly. If I change the
reference so that the expression works as a subform, then
the original form no longer works, and presents a #Name
error. It also doesn't work as a sub,subform. Again, if I
change the expression so that it works as a sub, subform,
then it no longer works as either a subform or as a main
form.

What I need is an expression that will work for all three
cases. In every case the expression lives in the control
source of the unbound field on the lowest level form.

When you use the subform tool to import a working form to
a higher level form, the expectation is that the lower
level form will still work. I don't need (or want) to do
anything at the level of the first or second parent
containers.

The approach offered by Boris might work where the
expression depends on a pair of IF statements or a CASE
switch, but I'm not sure I know how to put an If statement
in a control source. I suppose I could put it in one of
the events, but since this field is for information only
it's not clear to me that any of the events apply. There
is no reason to enter or leave the field or click on it,
etc.

In a sense that is what I did with the control buttons on
the switchboard. If you click the button for the highest
level form it opens with a copy of the lowest level form
that has the right reference. If you click the button for
the second level form it opens with another copy of the
lowest level form that has the correct reference. If you
open the lowest level of the form you get a copy with the
expression I quoted.

I get the idea of IF me.parent is Nothing, but that only
works for the second level form. How do you do IF
me.grandparent is Nothing? If me.Parent.Parent is Nothing?









-----Original Message-----
It is possible to nest the command line I used

previously. Also, receiving

#Name is frequently an indication that the control on the

form and the field

it is bound to have the same name so Access doesn't know

if you are

referring to the control or the field. If this is the

case try changing the

name of the control. (i.e. Field Name: MyField, Control

Name: txtMyField).

Nesting to subforms, 2nd subform referred to from first:
Forms![Name Of Parent Form]![Name Of Subform

Control].Form![Name Of 2nd

Subform Control].Form!txtMyTextbox

If you are doing this from the parent form, you can

remove Forms![Name Of

Parent Form]. To refer to the 2nd subform from the 1st

subform, you could

start at [Name of 2nd Subform Control]. To refer to a 2nd

subform that is on

the Parent form instead of nested into the 1st subform

from the 1st subform:

Parent![Name Of 2nd Subform Control].Form!txtMyTextbox

--
Wayne Morgan
Microsoft Access MVP


"Miaplacidus" wrote in message
...

That worked. But now the field gives a #Name? error on

the

original form. In addition, the form with the subform on
it is in turn a subform on the primary form. I have a
nested form with three levels. If I get it to work on

the

primary form then neither of the subforms work. If I get
it to work on the secondary form then the primary and
tertiary dont work.

I use the subforms as primary forms also, via buttons on
the switchboard. What ever happened to the concept of
inheritance?

How can I get all three forms to work?




-----Original Message-----
A subform isn't "open". It is an object on the parent

form. Change the last

part to:

Forms![Name Of Parent Form]![Name Of Subform

Control].Form!Office

[Name Of Subform Control] is the name of the control

that

holds the subform,

not the subform itself. To get this name, open the

parent

form in design

mode, open the Properties sheet, and click on the

subform

ONE time. The

properties sheet will show the name of the subform

control. If you click on

the subform twice, you'll be in the subform and the

Properties sheet will

show the name of the subform, not the control holding

it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in

message

. ..

I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" &

Forms!

[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later

used

as a subform,the expression produces a #NAME# error.

What gives?


.



.

OK

1. Staff Table has IDNO, FirstName LastName, Affiliation,
ReportsToID

2. Staff Table_1 Same Table, links IDNO to ReportsToID

3. Duties Table has IDNO, Primary Task, Secondary Task,
Skills, Office. Some staff have multiple duties and
multiple offices.

4. Locator Table has IDNO, Phone Type, Phone Number,
Location

5. Organization Table has Office, Department, Level.
Linked to Duties Table by Office. Maintains Org Structure
and Hierarchy of Departments and offices by use of a
number like a WBS number.

Form 1, based on Staff Table_1, shows all supervisors.

Form 2, based on Staff Table, shows all employees.
Form 2 is a subform to form 1, linked ReportsToID to IDNO
on form 1. As you scroll through form 1, staff for each
supervisor appears in Form 2.

Form 3, Based on Duties Table, shows duties, office and
department, linked on IDNO to Form 2. Subform to form 2.

Form 4, Based on Locator table, gives phone number, and
locations, Based on IDNO, Subform to form 2.

The Department field on Form 3 is a Dlookup. =DLookUp
("[Department]","Organization","[Office] = '" & Forms!
[Duties Matrix]!Office & "'")

That's all there is. This Dlookup works on form 3("Duties
Matrix"). If you then use the subform tool and put form 3
onto form 2, then the Dlookup fails.

If you change the Dlookup, while it is on form 2 to

=DLookUp("[Department]","Organization","[Office] = '" &
Forms![Employees Duties]![Duties Matrix].Form!Office & "'")

Then it works. But if you look at form 3 alone, not as a
subform, then it fails.

On the other hand, when you then use the subform tool and
paste form 2 onto form 1, then the Dlookup fails again. If
you change the reference again so that it works, then it
works only so long as you view form 3 as a sub,subform.

Form 3 as a subform to form 2 no longer works, and form 3
viewed alone no longer works.

My solution uses 3 copies of form 3, to meet it's three
conditions and two copies of form 2, to meet it's two
conditions. This works, but it is dumb as toast.




-----Original Message-----
Honestly, it sounds to me like you need to be posting
some of your code
and giving some more detail of your tables and subforms,
and how things
are related.

grep

Miaplacidus wrote:

I don't understand. I don't think this is a matter of
referencing. I have a form with an unbound field that
uses
a dlookup expression in the fields control source. It
works properly. But when I use that form as a subform
the
field no longer works properly: it gives a #Name? error
for a field that otherwise works properly. If I change
the
reference so that the expression works as a subform,
then
the original form no longer works, and presents a #Name
error. It also doesn't work as a sub,subform. Again, if
I
change the expression so that it works as a sub,
subform,
then it no longer works as either a subform or as a
main
form.

What I need is an expression that will work for all
three
cases. In every case the expression lives in the
control
source of the unbound field on the lowest level form.

When you use the subform tool to import a working form
to
a higher level form, the expectation is that the lower
level form will still work. I don't need (or want) to
do
anything at the level of the first or second parent
containers.

The approach offered by Boris might work where the
expression depends on a pair of IF statements or a CASE
switch, but I'm not sure I know how to put an If
statement
in a control source. I suppose I could put it in one of
the events, but since this field is for information
only
it's not clear to me that any of the events apply.
There
is no reason to enter or leave the field or click on
it,
etc.

In a sense that is what I did with the control buttons
on
the switchboard. If you click the button for the
highest
level form it opens with a copy of the lowest level
form
that has the right reference. If you click the button
for
the second level form it opens with another copy of the
lowest level form that has the correct reference. If
you
open the lowest level of the form you get a copy with
the
expression I quoted.

I get the idea of IF me.parent is Nothing, but that
only
works for the second level form. How do you do IF
me.grandparent is Nothing? If me.Parent.Parent is
Nothing?









-----Original Message-----
It is possible to nest the command line I used

previously. Also, receiving

#Name is frequently an indication that the control on
the

form and the field

it is bound to have the same name so Access doesn't
know

if you are

referring to the control or the field. If this is the

case try changing the

name of the control. (i.e. Field Name: MyField, Control

Name: txtMyField).

Nesting to subforms, 2nd subform referred to from first:
Forms![Name Of Parent Form]![Name Of Subform

Control].Form![Name Of 2nd

Subform Control].Form!txtMyTextbox

If you are doing this from the parent form, you can

remove Forms![Name Of

Parent Form]. To refer to the 2nd subform from the 1st

subform, you could

start at [Name of 2nd Subform Control]. To refer to a
2nd

subform that is on

the Parent form instead of nested into the 1st subform

from the 1st subform:

Parent![Name Of 2nd Subform Control].Form!txtMyTextbox

--
Wayne Morgan
Microsoft Access MVP


"Miaplacidus" wrote in
message
.. .

That worked. But now the field gives a #Name? error on

the

original form. In addition, the form with the subform
on
it is in turn a subform on the primary form. I have a
nested form with three levels. If I get it to work on

the

primary form then neither of the subforms work. If I
get
it to work on the secondary form then the primary and
tertiary dont work.

I use the subforms as primary forms also, via buttons
on
the switchboard. What ever happened to the concept of
inheritance?

How can I get all three forms to work?




-----Original Message-----
A subform isn't "open". It is an object on the parent

form. Change the last

part to:

Forms![Name Of Parent Form]![Name Of Subform

Control].Form!Office

[Name Of Subform Control] is the name of the control

that

holds the subform,

not the subform itself. To get this name, open the

parent

form in design

mode, open the Properties sheet, and click on the

subform

ONE time. The

properties sheet will show the name of the subform

control. If you click on

the subform twice, you'll be in the subform and the

Properties sheet will

show the name of the subform, not the control holding

it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in

message

.. .

I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" &

Forms!

[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later

used

as a subform,the expression produces a #NAME# error.

What gives?


.



.

.

Never mind - I changed the form so it was based on a query
instead of a table. The query includes the field I was
trying to Dlookup.

-----Original Message-----
Honestly, it sounds to me like you need to be posting
some of your code
and giving some more detail of your tables and subforms,
and how things
are related.

grep

Miaplacidus wrote:

I don't understand. I don't think this is a matter of
referencing. I have a form with an unbound field that
uses
a dlookup expression in the fields control source. It
works properly. But when I use that form as a subform
the
field no longer works properly: it gives a #Name? error
for a field that otherwise works properly. If I change
the
reference so that the expression works as a subform,
then
the original form no longer works, and presents a #Name
error. It also doesn't work as a sub,subform. Again, if
I
change the expression so that it works as a sub,
subform,
then it no longer works as either a subform or as a
main
form.

What I need is an expression that will work for all
three
cases. In every case the expression lives in the
control
source of the unbound field on the lowest level form.

When you use the subform tool to import a working form
to
a higher level form, the expectation is that the lower
level form will still work. I don't need (or want) to
do
anything at the level of the first or second parent
containers.

The approach offered by Boris might work where the
expression depends on a pair of IF statements or a CASE
switch, but I'm not sure I know how to put an If
statement
in a control source. I suppose I could put it in one of
the events, but since this field is for information
only
it's not clear to me that any of the events apply.
There
is no reason to enter or leave the field or click on
it,
etc.

In a sense that is what I did with the control buttons
on
the switchboard. If you click the button for the
highest
level form it opens with a copy of the lowest level
form
that has the right reference. If you click the button
for
the second level form it opens with another copy of the
lowest level form that has the correct reference. If
you
open the lowest level of the form you get a copy with
the
expression I quoted.

I get the idea of IF me.parent is Nothing, but that
only
works for the second level form. How do you do IF
me.grandparent is Nothing? If me.Parent.Parent is
Nothing?









-----Original Message-----
It is possible to nest the command line I used

previously. Also, receiving

#Name is frequently an indication that the control on
the

form and the field

it is bound to have the same name so Access doesn't
know

if you are

referring to the control or the field. If this is the

case try changing the

name of the control. (i.e. Field Name: MyField, Control

Name: txtMyField).

Nesting to subforms, 2nd subform referred to from first:
Forms![Name Of Parent Form]![Name Of Subform

Control].Form![Name Of 2nd

Subform Control].Form!txtMyTextbox

If you are doing this from the parent form, you can

remove Forms![Name Of

Parent Form]. To refer to the 2nd subform from the 1st

subform, you could

start at [Name of 2nd Subform Control]. To refer to a
2nd

subform that is on

the Parent form instead of nested into the 1st subform

from the 1st subform:

Parent![Name Of 2nd Subform Control].Form!txtMyTextbox

--
Wayne Morgan
Microsoft Access MVP


"Miaplacidus" wrote in
message
.. .

That worked. But now the field gives a #Name? error on

the

original form. In addition, the form with the subform
on
it is in turn a subform on the primary form. I have a
nested form with three levels. If I get it to work on

the

primary form then neither of the subforms work. If I
get
it to work on the secondary form then the primary and
tertiary dont work.

I use the subforms as primary forms also, via buttons
on
the switchboard. What ever happened to the concept of
inheritance?

How can I get all three forms to work?




-----Original Message-----
A subform isn't "open". It is an object on the parent

form. Change the last

part to:

Forms![Name Of Parent Form]![Name Of Subform

Control].Form!Office

[Name Of Subform Control] is the name of the control

that

holds the subform,

not the subform itself. To get this name, open the

parent

form in design

mode, open the Properties sheet, and click on the

subform

ONE time. The

properties sheet will show the name of the subform

control. If you click on

the subform twice, you'll be in the subform and the

Properties sheet will

show the name of the subform, not the control holding

it.

--
Wayne Morgan
Microsoft Access MVP


"miaplacidus" wrote in

message

.. .

I have a dlookup on an unbound field on a form:

=DLookUp("[Area]","Organization","[Office] = '" &

Forms!

[Office Duties Matrix]!Office & "'")

This works OK on the form, but when the form is later

used

as a subform,the expression produces a #NAME# error.

What gives?


.



.

.