Dlookup works on all forms, except one.

[jillywilly143]

I have the following Dlookup:

=DLookUp("LastName","ClientT","AvatarNumber=" & [Me].[AvatarNumber])

It returns #Name?, but it is the exact same formula on the other forms in my database that work.

Please help. :)

[PhilOfWalton]

Does your form have a Field called AvatarNumber as well as the ControlSource being AvatarNumber?

Can AvatarNumber ever be Null (On a new record for example)?

Phil

[Seth Schrock]

Here are a few ways to troubleshoot your problem. First, try removing the criteria of the DLookup() function and see if a value is returned (it doesn't matter if it is the correct value). Right before that line of code, insert the following:

Debug.Print "AvatarNumber=" & Me.AvatarNumber

This will post some output to the Immediate window (press Ctrl + G to view if it isn't already open). Please post this back to the thread. Is AvatarNumber a number or text value?

Oops, I cross posted with Phil. Checking for a null value is also a good idea, as Phil suggested.

[mbizup]

With the = sign in front of your DLookup, it looks like you are trying to apply this to a control source property vs VBA code.

If this is indeed used in a control source property, on a property sheet, the "Me" prefix will fall over (to my knowledge that is only valid in VBA).

You can use the full form reference:

=DLookUp("LastName","ClientT","AvatarNumber=" & Forms!YourFormName!AvatarNumber)

OR, if the control is on the same form, you can get by with just the control name:

=DLookUp("LastName","ClientT","AvatarNumber=" & AvatarNumber)

[jillywilly143]

Yes, they both have it. It can be Null on a new record.

[jillywilly143]

Thank you guys so much. I feel like I was not very specific. I have a table with client information. AvatarNumber is their unique customer number. I have 2 forms. One that is for entering their contract and one for entering their payable information. On both the table, and on both forms, the field is called AvatarNumber. On the payables form, the Dlookup works. It is the exact same Dlookup as the contract form. The contract form used to work, and now it doesn't.

[jillywilly143]

Seth,

A value is returned when I remove the criteria. AvatarNumber is a number value.

This is what my Ctrl+G pulled up

Private Sub Text479_BeforeU pdate(Cancel As Integer)

End Sub

[PhilOfWalton]

I Frequently use this ELookup Function. Allegedly it is faster then Dlookup and works identically. If you paste this into a general module and change =DLookUp("LastN ame","ClientT", "AvatarNumb er=" & AvatarNumber)
to =ELookUp("LastN ame","ClientT", "AvatarNumb er=" & AvatarNumber),
you can then debug the ELookup code and see where it is bombing out.

Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _
    Optional ORDERClause As Variant) As Variant

    On Error GoTo Err_ELookup
    'Purpose:   Faster and more flexible replacement for DLookup()
    'Arguments: Same as DLookup, with additional Order By option.
    'Return:    Value of the Expr if found, else Null.
    '           Delimited list for multi-value field.
    'Author:    Allen Browne. allen@allenbrowne.com
    'Updated:   December 2006, to handle multi-value fields (Access 2007.)
    'Examples:
    '           1. To find the last value, include DESC in the OrderClause, e.g.:
    '               ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    '               ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note:      Requires a reference to the DAO library.
    Dim MyDb As DAO.Database          'This database.
    Dim Rs As DAO.Recordset         'To retrieve the value to find.
    Dim rsMVF As DAO.Recordset      'Child recordset to use for multi-value fields.
    Dim varResult As Variant        'Return value for function.
    Dim strSql As String            'SQL statement.
    Dim strOut As String            'Output string to build up (multi-value field.)
    Dim lngLen As Long              'Length of string.
    Const strcSep = ","             'Separator between items in multi-value list.

    'Initialize to null.
    varResult = Null

    'Build the SQL string.
    strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strSql = strSql & " WHERE " & Criteria
    End If
    If Not IsMissing(ORDERClause) Then
        strSql = strSql & " ORDER BY " & ORDERClause
    End If
    strSql = strSql & ";"

    'Lookup the value.

    Set MyDb = CurrentDb
    
    Set Rs = MyDb.OpenRecordset(strSql, dbOpenForwardOnly)
    If Rs.RecordCount > 0 Then
        'Will be an object if multi-value field.
        If VarType(Rs(0)) = vbObject Then
            Set rsMVF = Rs(0).Value
            Do While Not rsMVF.EOF
                If Rs(0).Type = 101 Then        'dbAttachment
                    strOut = strOut & rsMVF!FileName & strcSep
                Else
                    strOut = strOut & rsMVF![Value].Value & strcSep
                End If
                rsMVF.MoveNext
            Loop
            'Remove trailing separator.
            lngLen = Len(strOut) - Len(strcSep)
            If lngLen > 0& Then
                varResult = Left(strOut, lngLen)
            End If
            Set rsMVF = Nothing
        Else
            'Not a multi-value field: just return the value.
            varResult = Rs(0)
        End If
    End If
    Rs.Close

    'Assign the return value.
    ELookup = varResult

Exit_ELookup:
    Set Rs = Nothing
    Set MyDb = Nothing
    Exit Function

Err_ELookup:
    MsgBox "Error " & Err & " " & Err.Description, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
    
End Function

Phil

[NeoPa]

Hi JillyWilly.

JillyWilly:
This is what my Ctrl+G pulled up

Private Sub Text479_BeforeU pdate(Cancel As Integer)

End Sub

That looks like something's gone wrong. After displaying a numeric value to the Immediate pane one would expect to see a number displayed.

What you have there looks like some code that hasn't been finished. That would be found in the Code pane. Not the Immediate.

I can't help feeling, from what i've read so far, that something in the details somewhere is being overlooked. May I suggest you go over everything again very carefully. I expect you'll see something that's not the same between the working versions and that which doesn't.

If you see nothing then go along the debugging route (Debugging in VBA).

NB. ELookUp() was developed by a fellow MS Access MVP and Phil does well to recommend that to you, though it won't fix your current issue particularly. As he says though, it may help with the debugging if it gets that far. I suspect the problem's not there though if I'm honest.

[jillywilly143]

I must be doing the Debug wrong. There is nothing in the Immediate pane.

[NeoPa]

Ok. That happens.

Try going back to Seth's post (#3) and following the instructions very carefully. Identify the line in your code before which you should insert the new line then copy and paste Seth's code in exactly as it is.

You won't see the results in the Immediate pane unless and until you show that pane. The easiest way to accomplish that is to press Ctrl-G. The results will be there whether the pane is visible or not so it doesn't matter if you show it after the line of code has run.

I also included a link to instructions on how to use the debugging tools in my earlier post. It may be a good idea to add a new line there too, just as :

Stop

This will allow you to step through the code line by line and examine the variables as they are at each line.

Start with posting the value you see in the Immediate pane though, of course.

[jillywilly143]

I am not using VBA code for my Dlookup. I have it as the control source of an unbound field. So, when you say "Identify the line in your code before which you should insert the new line," I don't know where to look. I really appreciate your help and patience.

[NeoPa]

Ah. That makes debugging a little more clumsy, but the fundamentals are the same.

If a number of elements are involved that go into the overall item you're debugging then start by confirming each of the elements.

The first question you need to answer is - What happened when you tried Miriam's (MBizup) suggestions? If you're talking about a formula in an unbound control them Me. will certainly not work. If you think you have it working in that same way (as your OP) in another form then I'd need to see proof of that as it just isn't possible. That was probably the main reason most of us assumed you were working in VBA. Me. simply doesn't work anywhere else in Access.

If you find that Miriam's suggestions don't work then try, simply to gain info and not as a suggestion, putting in the following and seeing what it is you see on the form :

="AvatarNumber=" & [AvatarNumber]

I'm interested to hear your responses.

[jillywilly143]

NeoPa,

I did try MBizup's suggestion and that did not help. I decided to make a copy of my DB to get rid of the confidential names, so that you can see this is really happening. The funny thing is that the copy works, but none of the buttons do anymore. So, I'm wondering if one my macros is the problem. Is there a quick way to look at that?

[PhilOfWalton]

Often when copying, the [Event Procedure] vanishes not only on the OnClick of your buttons, but on all Events on your forms

Phil