Run-time error 438 when using dlookup with boolean

[riggsdp]

I need to find what a boolean value is set to for a specific item but am getting a run-time error 438, Object doesn't support this property or method.



I've tried declaring responseReq as a Boolean, Integer, String, and Variant - all with no success.

responseReq = DLookup("Response_Required", "tblDeliverableDetails", "CDRL_ID=" & Me!frmDeliveriesReceived_Subform.CDRL_Number)

When the response is returned, I have a Select Case that calls different functions depending on whether the response is true or false.

Would appreciate your assistance in declaring the variable correctly and correcting the DLookup code as needed.

Here is the complete section of code, with the problem line highlighted, after I tried adding Nz and IIf to see if that would help.

Private Sub Date_Received_AfterUpdate() 'when date received is entered

    'declare variables
    Dim revDays As Integer 'number of review days
    Dim busDays As Integer 'are review days business days
    Dim responseReq As Integer 'is a response required
    Dim disDays As Integer 'is disposition letter time needed
    Dim appCode As String 'contents of APP Code field
    Dim commentsDue As Date 'date comments due
    Dim dispDue As Date 'date disposition letter is due

    'check whether it was received on time or not
    If Me.Date_Received <= Me.Date_Due Then 'if received before or on due date
        Me.Received_On_Time = True 'set to True
    Else: Me.Received_On_Time = False 'if received after due date, set to False
    End If
    'refresh to see updated check box
    Me.Received_On_Time.Requery
    
    'check to see if a response and approval is required
    
    'get response required
    responseReq = IIf(Nz(DLookup("Response_Required", "tblDeliverableDetails", "CDRL_ID=" & Me!frmDeliveriesReceived_Subform.CDRL_Number)) = vbNullString, 1, 0)
    'get approval code
    appCode = DLookup("APP_Code", "tblDeliverableDetails", "CDRL_ID=" & Me!frmDeliveriesReceived_Subform.CDRL_Number)
    'get number of review days
    revDays = DLookup("Govt_Review", "tblDeliverableDetails", "CDRL_ID=" & Me!frmDeliveriesReceived_Subform.CDRL_Number)
    'get business days
    busDays = DLookup("Govt_Business_Days", "tblDeliverableDetails", "CDRL_ID=" & Me!frmDeliveriesReceived_Subform.CDRL_Number)
    
    'get USG and comments due dates based on approval code
    
    Select Case appCode 'check for approval code
        Case "N/A" 'if approval code is "NA"
            Select Case responseReq
                Case 0 'if response is not required
                    disDays = 0 'set disposition days to option 0, just in case
                    Exit Sub 'no response or approval required, so no further calculations required
                Case 1 'if response is required
                    disDays = 1 'set disposition days needed to option 1 as no approval is required even though the option is set
                    'call function to calculate USG comments and final due date
                    commentsDue = FindReviewDueDate(Me.Date_Received, Me.USG_Due_Date, disDays, revDays, busDays)
                    'set disposition due date to calculated date on return
                    Me.USG_Due_Date = commentsDue
                    'set comments due date to calculated date on return
                    Me.Comments_Due_Date = commentsDue
                    'refresh to see updated dates
                    Me.USG_Due_Date.Requery
                    Me.Comments_Due_Date.Requery
            End Select
        Case "A" ' if approval code is "A"
            Select Case responseReq 'if response is not required
                Case 0
                    ' ==== TBD as this should never happen, should show error message
                    Exit Sub
                Case Else 'if response is required, will need
                    'to subtract 72 hours from USG due date for disposition letter and approval
                    disDays = 2 'set disposition days to option 2
                    'call function to calculate USG comments due date
                    commentsDue = FindReviewDueDate(Me.Date_Received, Me.USG_Due_Date, disDays, revDays, busDays)
                    disDays = 3 'set disposition days to option 3
                    'call function to calculate USG due date for disposition letter
                    dispDue = FindReviewDueDate(commentsDue, Me.USG_Due_Date, disDays, revDays, busDays)
                    'set disposition due date to calculated date on return
                    Me.USG_Due_Date = dispDue
                    'set comments due date to calculated date on return
                    Me.Comments_Due_Date = commentsDue
                    'refresh to see updated dates
                    Me.USG_Due_Date.Requery
                    Me.Comments_Due_Date.Requery
            End Select
    End Select
End Sub

[ranman256]

how about simplify it:

responseReq =NZ(DLookup("Response_Required", "tblDeliverableDetails", "CDRL_ID=" & Me!frmDeliveriesReceived_Subform.CDRL_Number)))

[riggsdp]

I tried that already, before adding the IIf. Still get the run-time error 438.

[RuralGuy]

It is *strongly* recommended that you NOT mix single line and multi-line code:

If Me.Date_Received <= Me.Date_Due Then 'if received before or on due date
        Me.Received_On_Time = True 'set to True
    Else: Me.Received_On_Time = False 'if received after due date, set to False
    End If

It can make Access get REAL confused.

[riggsdp]

Thanks RG, I'll keep that in mind as I continue.

On a whim, even though I am in the form code, I added the full forms path for the subform and ... it worked.

responseReq = DLookup("Response_Required", "tblDeliverableDetails", "CDRL_ID=" & Forms!frmDeliveriesReceived!frmDeliveriesReceived_Subform.Form!CDRL_Number)

It's odd that sometimes the Me!subform_name will work and other times it won't. I understand why it won't work in a module, but not in the form.

[RuralGuy]

Excellent! Thanks for sharing your success and solution.

[RonL]

......

It's odd that sometimes the Me!subform_name will work and other times it won't. I understand why it won't work in a module, but not in the form.

In the yellowed line above, the syntax is missing a ".form." The Me syntax for referencing a subform control from main form code is Me!NameOfSubformControlITSELF.FORM.Nameof a control On the Subform. (Which is what you did when writing it full out. Wish I had a nickel for every time I've been burned by this.)

[riggsdp]

I have other references to the subform that work without adding Form after the subform name so, since I was in the form itself, didn't think anything of it. I'm just glad it works now!

[RonL]

Glad it works for you also. But of course, if you're in the subform's code itself, there's no need to reference the main form. Not to belabor, but I do still think it's important to obviate confusion about the difference between a subform CONTROL (on a main form) and the (sub)form object CONTAINED by that control. During form design, these frequently end up with the same name. Future searchers may find the following instructive:

http://www.btabdevelopment.com/easy-...r-to-subforms/