dlookup help referring to a subform

[Kozbot]

Hello

I have the following code

Option Compare Database

Private Sub TestResult_BeforeUpdate(Cancel As Integer)
If Me.TestResult < DLookup("MinValue", "QCSpecs", "criteria1='" & Forms!QC2EntryForm!ProductCode _
    & "and criteria2 = '" & Forms!QC2EntryForm!TestName & "'") And Me.TestResult > _
    DLookup("MaxValue", "QCSpecs", "criteria1='" & Forms!QC2EntryForm!ProductCode _
    & "and criteria2 = '" & Forms!QC2EntryForm!TestName & "'") Then
    MsgBox ("Out of Spec")
    Cancel = True
End If
End Sub

I am getting runtime error 2465 "Access cannot find the field TestName in your expression". I believe this is because TestName is in a subform of QC2EntryForm because the Product Code criteria is returning the proper value. How do I refer to a subform properly?

 

[Kozbot]

I wrote this code

Private Sub TestResult_BeforeUpdate(Cancel As Integer)
If Me.TestResult < DLookup("MinValue", "QCSpecs", "criteria1='" & Forms!QC2EntryForm!ProductCode _
    & " and criteria2 = '" & Forms!QC2EntryForm![QC2TestResults subform].Form.TestName & "'") And Me.TestResult > _
    DLookup("MaxValue", "QCSpecs", "criteria1='" & Forms!QC2EntryForm!ProductCode _
    & " and criteria2 = '" & Forms!QC2EntryForm![QC2TestResults subform].Form.TestName & "'") Then
    MsgBox ("Out of Spec")
    Cancel = True
End If
End Sub

to attempt to refer to the subform however I get
Run time error 3075
Syntax error(missing operator in query expression
‘criteria1=’ab-120 and criteria2=’carblack”

what is causing this?

 

[pbaldy]

If you look at the error message you'll see that you correctly have the ' at the beginning of each text value, but missed the closing '.

 

[billmeye]

In your original code you are missing the ending ' single quotes in a couple of places:

Private Sub TestResult_BeforeUpdate(Cancel As Integer)
If Me.TestResult < DLookup("MinValue", "QCSpecs", "criteria1='" & Forms!QC2EntryForm!ProductCode _
    & "[COLOR="Red"]' [/COLOR]and criteria2 = '" & Forms!QC2EntryForm!TestName & "'") And Me.TestResult > _
    DLookup("MaxValue", "QCSpecs", "criteria1='" & Forms!QC2EntryForm!ProductCode _
    & "[COLOR="red"]' [/COLOR]and criteria2 = '" & Forms!QC2EntryForm!TestName & "'") Then
    MsgBox ("Out of Spec")
    Cancel = True
End If
End Sub

 

[Kozbot]

Thank you!

Private Sub TestResult_BeforeUpdate(Cancel As Integer)
If Me.TestResult < DLookup("MinValue", "QCSpecs", "ProductCode='" & Forms!QC2EntryForm!ProductCode _
    & "' and TestName = '" & Me![QC2TestResults subform].Form.TestName & "'") And Me.TestResult > _
    DLookup("MaxValue", "QCSpecs", "ProductCode='" & Forms!QC2EntryForm!ProductCode _
    & "' and TestName = '" & Forms!QC2EntryForm![QC2TestResults subform].Form.TestName & "'") Then
    MsgBox ("Out of Spec")
    Cancel = True
End If
End Sub

this is my code now. I am getting the error message. Run time error 2465 Access cannot find the field QC2Testresults subform. How am I referring to the subform wrong?

 

[billmeye]

When referring to a subform, you have to include the names of every form that precedes it, probably in your case it is just the main form:

Forms!MainForm!Subform!TestName

 

[Kozbot]

When referring to a subform, you have to include the names of every form that precedes it, probably in your case it is just the main form:

Forms!MainForm!Subform!TestName

I am referring to the subform like

Forms!QC2EntryForm![QC2TestResults subform]!TestName
and
Forms!QC2EntryForm![QC2TestResults subform]!form!TestName

With either or them there are no error messages but the code does not function. No msgbox pops up

 

[Kozbot]

ah I got it to work! Thank you everyone

 

[billmeye]

Great, good luck.