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
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
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
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?
.
.
.
