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:
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:
Can anyone tell me how to write the criteria to add the additional test?
Here are a couple that I have tried:
or this:
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?
It looks like this:
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] & "'")
Here are a couple that I have tried:
Code:
=DLookUp("Unit","tblData","Category='" & [cboCategory] & " And Stage='" & Me.Parent![cboStage] & "'")
Code:
=DLookUp("Unit","tblData","[Category]=""" & [Me].[cboCategory] & """ AND [Stage]=""" & [Parent].[Form].[cboStage] & """")
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: