CODEWITHSUNDEEP
ms-accessvba
Matt
Matt

Reputation: 23

MS Access 2013 Dlookup String Criteria issue

It seems this problem should have a rather simplistic solution. When trying to run the dlookup for ID, I return a null value when using the RequestNum string. RequestNum is simply an AutoNumber. RequestNum does write to the popup form that is opening up on its own. Also, if i replace RequestNum with the actual number in the field I get the desired returned result. The only help I've been able to find online is that most people did not use '" & stringhere & "' though I obviously am. Any ideas? I'll be happy to supply any additional details if needed. Thanks in advance!

Private Sub lst_AdminDate1_DblClick(Cancel As Integer)
    Dim IDx As String
    Dim RequestNum As String
    DoCmd.OpenForm "Administrative_LeaveCalendar_Detail"
    RequestNum = Me.lst_AdminDate1.Column(2)
    IDx = DLookup("[ID]", "TimeOffCalendar", "[RequestNumber] = '" & RequestNum & "'")
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_RN] = RequestNum
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_ID] = IDx
    End Sub

Upvotes: 0

Views: 226

Answers (1)

Gustav
Gustav

Reputation: 56026

What may confuse you is, that a listbox always returns strings even if the value was a number and is supposed to be used as such. Also, DLookup may return Null.

Thus, if [RequestNumber] is a Long (which is should be):

Private Sub lst_AdminDate1_DblClick(Cancel As Integer)

    Dim IDx As Variant
    Dim RequestNum As String

    DoCmd.OpenForm "Administrative_LeaveCalendar_Detail"
    RequestNum = Me.lst_AdminDate1.Column(2)
    IDx = DLookup("[ID]", "TimeOffCalendar", "[RequestNumber] = " & RequestNum & "")
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_RN] = RequestNum
    [Forms]![Administrative_LeaveCalendar_Detail]![txtAdminDateDetail_ID] = IDx

End Sub

Upvotes: 1

Related Questions