Reference parent form in DLookup Criteria (1 Viewer)

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
I have a main form (frmMain). Inside frmMain is a subform (frmSub1) in Datasheet view. Inside that form is a form (frmSub2)in Continuous Forms view.

It looks like this:

DlookupQuestion.jpg

On frmSub2 is a control called "Unit". I want it took look up the value in the "Unit" field in tblData where Category = Me.cboCategory AND Stage = Me.Parent!cboStage.

The table tblData has the following fields: Stage, Category and Unit.

I can't get this to work no matter how I word the criteria.

Previously, it simply looked up "Unit" from tblData where Category = Me.cboCategory and it worked fine.
This was the previous control source of Units:
Code:
=DLookUp("Unit","tblPhaseCategory","Category='" & [cboCategory] & "'")
Can anyone tell me how to write the criteria to add the additional test?

Here are a couple that I have tried:

Code:
=DLookUp("Unit","tblData","Category='" & [cboCategory] & " And Stage='" & Me.Parent![cboStage] & "'")
or this:

Code:
=DLookUp("Unit","tblData","[Category]=""" & [Me].[cboCategory] & """ AND [Stage]=""" & [Parent].[Form].[cboStage] & """")
It seems like this really ought to be possible, but I'm probably writing the criteria incorrectly.

It appears that you can't use "Me" on an actual form. I think you can only use that in VBA.

Secondary question: Understanding the syntax of these types of criteria statements is a real issue for me, mainly the placement of " and '. What do I need to study to get a better grasp of this? SQL?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:03
Joined
Jan 14, 2017
Messages
18,186
Code:
Assuming the combo cboStage is in frmMain then

Code:
=DLookUp("Unit","tblData","Category=[COLOR="Red"]'[/COLOR]" & Me.cboCategory &"[COLOR="red"]'[/COLOR] AND Stage=[COLOR="red"]'[/COLOR]"& Parent.Form.cboStage & "[COLOR="red"]'[/COLOR]")

If its in the top subform then its a bit more tricky as you need to reference that via the parent form

EDIT:
Just looked at your screenshot ... its in the first subform ..so it should be:

Code:
=DLookUp("Unit","tblData","Category=[COLOR="Red"]'[/COLOR]" & Me.cboCategory &"[COLOR="red"]'[/COLOR] AND Stage=[COLOR="red"]'[/COLOR]"& Parent.[B][COLOR="Red"]frmSub1[/COLOR][/B].Form.cboStage & "[COLOR="red"]'[/COLOR]")

PROVIDED frmSub1 is the name of the SUBFORM container control in frmMain.
If its something else, use that name
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:03
Joined
Oct 29, 2018
Messages
21,357
Hi. I think your second attempt is closer, just remove the Form part.
Code:
[Parent].cboStage
Edit: Oops, I didn’t look at your screenshot, so if Colin said your combo is on another subform, then you’ll need a different syntax.
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
Colin,

I just plugged that code in and all the unit fields are filled with #Name?.

I have double checked to make sure I have entered everything correctly.

Any idea why this might be?
 

isladogs

MVP / VIP
Local time
Today, 10:03
Joined
Jan 14, 2017
Messages
18,186
If my version is still not right, have a look at these 2 sites:
http://allenbrowne.com/casu-04.html
http://access.mvps.org/access/forms/frm0031.htm

The second one is very comprehensive but IIRC I've never done Parent!Parent notation

Instead I'd do the following if all else fails:
Code:
=DLookUp("Unit","tblData","Category='" & Me.cboCategory &"' AND Stage='"& Forms!frmMain.frmSub1.Form.cboStage & "'")

EDIT Posts crossed - try the above. Signing off now for the night. good luck
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
Thanks for your help.

All of these are resulting in #Name? Man, this is frustrating.

I use the 2nd reference you linked to regularly. It is excellent, however, as far as I know, the "me" & "parent" terminology only works in code, they won't work in a form property.

If anyone knows how I can accomplish this, I would be eternally grateful.
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
Update:
If I use this:
Code:
=DLookUp("Unit","tblData","Category='" & [cboCategory] & "' AND Stage='" & [Forms]![frmMain].[frmSub1].[Form].[cboStage] & "'")

The first record in frmSub2 will get a result in the "Unit" field. All the other records are blank.
 

isladogs

MVP / VIP
Local time
Today, 10:03
Joined
Jan 14, 2017
Messages
18,186
Dlookup returns one record only.
Are your forms and subforms linked using parent/child fields?
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
Maybe this will explain what it is doing better:

DlookupQuestion2.jpg
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
Sorry, I didn't see your question. Yes, the forms are linked with parent/child fields.
 

isladogs

MVP / VIP
Local time
Today, 10:03
Joined
Jan 14, 2017
Messages
18,186
I was hoping someone would have been able to assist overnight. Unfortunately not.
I think it would help to see a copy of this part of your database in order for someone to provide a solution for you.
 

Solo712

Registered User.
Local time
Today, 06:03
Joined
Oct 19, 2012
Messages
828
Update:
If I use this:
Code:
=DLookUp("Unit","tblData","Category='" & [cboCategory] & "' AND Stage='" & [Forms]![frmMain].[frmSub1].[Form].[cboStage] & "'")

The first record in frmSub2 will get a result in the "Unit" field. All the other records are blank.

Hi,
I checked my subforms referencing guide (which has yet to let me down) :
http://access.mvps.org/access/forms/frm0031.htm, and if I read your design correctly you might want to try a small change in notation:

Code:
=DLookUp("Unit","tblData","Category='" & [cboCategory] & "' AND Stage='" & [Forms]![frmMain][COLOR="Red"][B]![/B][/COLOR][frmSub1].[cboStage] & "'")


Best,
Jiri
 

isladogs

MVP / VIP
Local time
Today, 10:03
Joined
Jan 14, 2017
Messages
18,186
Hi Jiri
Oops! Well spotted.
That's what I meant to write having used and listed the same excellent reference in post #5
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:03
Joined
May 21, 2018
Messages
8,463
Code:
[Forms]![frmMain].[frmSub1].[Form].[cboStage]
Nothing wrong with that. A control is a property of the form. So you can reference the property directly or through the controls collection.
Me.ControlName or Me!ControlName
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
Sorry for the delay and thanks for the replies.

I have tried all of your suggestions and I still get #Name? in all of the Unit fields. I don't get it. It seems like DLookup isn't capable of looking outside of the form it is on.

I came up with a very "ghetto" workaround for the time being just to get it working: In the underlying query of frmSub2, I brought in the underlying query of frmSub1 and made a relationship on 'Category'. Then I added the stage field.

Then I made an invisible text box on frmSub2 called stage. Now I can use this DLookup:

Code:
=DLookUp("Unit","tblData","Category='" & [cboCategory] & "' AND Stage='" & [Stage] & "'")

Now, it works as expected because it isn't trying to look to the parent form. However, it really seems like there ought to be a way to make it work by pointing it to the parent form.

Do you guys agree?
 

isladogs

MVP / VIP
Local time
Today, 10:03
Joined
Jan 14, 2017
Messages
18,186
Now, it works as expected because it isn't trying to look to the parent form. However, it really seems like there ought to be a way to make it work by pointing it to the parent form.

Do you guys agree?

Yes I agree and I've done it myself in the past - if you are willing to upload it, I will have a look
Having said that, I've used the same work-round as you on more than one occasion

Did you ever confirm that the name of the top subform CONTAINER is frmSub1?
i.e. the name of the subform control in the main form - it may NOT the name as the subform itself
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:03
Joined
May 21, 2018
Messages
8,463
I tested this with a subdatasheet like in the picture and it works fine with the
parent.ControlName notation.
However, there is a much bigger issue in doing this, so I would not pursue doing that. If you expand the subdatasheet and you are not on that record you will pull the reference for the record that you are on. The only way to get the correct reference is to minimize the subdatasheet and then move to it and then open it.
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
I tested this with a subdatasheet like in the picture and it works fine with the
parent.ControlName notation.
However, there is a much bigger issue in doing this, so I would not pursue doing that. If you expand the subdatasheet and you are not on that record you will pull the reference for the record that you are on. The only way to get the correct reference is to minimize the subdatasheet and then move to it and then open it.

Did you actually use the word "parent" in the criteria? If so, did you put the DLookup in the control source?

I could never get it to give me a result beyond the 1st record. If it behaves as you say, that definitely is a problem.

Yes I agree and I've done it myself in the past - if you are willing to upload it, I will have a look
Having said that, I've used the same work-round as you on more than one occasion

Did you ever confirm that the name of the top subform CONTAINER is frmSub1?
i.e. the name of the subform control in the main form - it may NOT the name as the subform itself
Yes, the container and the subform have the same name. Good idea, but I don't think that's the issue.

This database is a monstrosity. I would have to scale it down to an upload-friendly format. If I can do that, I'd love to have you take a look and see if Dlookup can work as intended.

I'll admit it's re-assuring to know that you've used this same workaround. For now it seems like an OK solution.
 

isladogs

MVP / VIP
Local time
Today, 10:03
Joined
Jan 14, 2017
Messages
18,186
Up to you. The offer still stands if you want to take it up.

Looking again at your two screenshots, is there any good reason why you haven't just modified the record source for subform2 to include the Unit field from tblData using joins to include the Category and Stage fields.

If you do that, the field will populate automatically and there will be no need to use DLookup at all....
 

DataBass

Registered User.
Local time
Today, 03:03
Joined
Jun 6, 2018
Messages
68
That is a good idea. If I recall correctly, I think I tried that over the weekend. It did pull in the correct values for the Unit field, but having those joins made the recordsource not updateable and I couldn't create new records.

I'll try it again this afternoon to doublecheck.
 

Users who are viewing this thread

Top Bottom