CODEWITHSUNDEEP
ms-access-2007
Flip The Pip Seeds
Flip The Pip Seeds

Reputation: 71

Dlookup in a form with 2 subforms (The requirement for the dlookup is in the second subform)

Access 2007 

Form Name is: MineLocationQuery1
1st Subform name is:MineExtractionSubform
2nd Subform name is: ExtractionLineSubform2

I am trying to use dlookup to get check a value in a table and then multiply it by another value. It works with: NO Subform and with 1 Subform. But when I add 2 subforms I cannot get it to work.

Here is what I have tried so far

Simple form lookup that works:

=DLookUp("[Price]","[Resource]","[AtomicRef]='" & [Forms]![ExtractionLine]![AtomicRef] & "'")*[Tonnage]

Here is the working example from a form created with 1 subform:

=DLookUp("[Price]","[Resource]","[AtomicRef]='" & [Forms]![MineExtraction1]![ExtractionLineSubform]![AtomicRef] & "'")*[Tonnage]

And here is 1 of the many attempts I have made with 2 subforms and it is not working.

=DLookUp("[Price]","[Resource]","[AtomicRef]='" & Forms![MineLocationQuery1]!MineExtractionSubform.Form!ExtractionLineSubform2.Form.AtomicRef & "'")*[Tonnage]

What am I doing wrong other than getting tired and frustrated at copying and pasting many attempts into the worths textbox control source and repeatedly getting #Name errors

Upvotes: 1

Views: 1608

Answers (1)

Fionnuala
Fionnuala

Reputation: 91356

If I wish to refer to a nested subform in a textbox on the main form, I might say:

=[SubformControl1].[Form].[SubformControl2].[Form].[AControlName]

Note that this uses the name of the subform control and Form as a reference to the object contained by the control.

So this (split for ease of reading) looks right:

=DLookUp("[Price]","[Resource]","[AtomicRef]='" & Forms![MineLocationQuery1]!
MineExtractionSubform.Form!ExtractionLineSubform2.
Form.AtomicRef & "'")*[Tonnage]

However, it is very easy to get names wrong. For example, you may be referring to the form contained, rather than the subform control name. You can use the Expression Builder to troubleshoot, or you can refer to the form bit by bit in the immediate window (Ctrl+G) to ensure you have the names right. For example:

?Forms![MineLocationQuery1].Name
?Forms![MineLocationQuery1]!MineExtractionSubform.Name

More information: http://access.mvps.org/access/forms/frm0031.htm

EDIT re comments

If you work in subform 2, as I believe you should in this case, you can simply refer to [atomicref]:

=DLookUp("[Price]","[Resource]","[AtomicRef]='" & [atomicref] & "'")

Upvotes: 1

Related Questions