Getting Data Type Mismatch in Expression Error

[Kaloyanides]

If anyone could help me out, I'd greatly appreciate it.

I'm getting a data type mismatch in the following expression. I love what this code does but I've always had trouble getting it to work properly. Both LastName and FirstName table fields are formatted = Short Text.

Thanks!

' If on a new row,
If (Me.NewRecord = True) Then
    ' Check for similar name
    If Not IsNothing(Me.LastName) Then
        ' Open a recordset to look for similar names
        Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName FROM " & _
            "tblContacts WHERE Soundex([LastName]) = '" & _
            Soundex(Me.LastName) & "'")
        ' If got some similar names, issue warning message
        Do Until rst.EOF
            strNames = strNames & rst!LastName & ", " & rst!FirstName & vbCrLf
            rst.MoveNext
        Loop
        ' Done with the recordset
        rst.Close
        Set rst = Nothing
        ' See if we got some similar names
        If Len(strNames) > 0 Then
            ' Yup, issue warning
            If vbNo = MsgBox("The System found contacts with similar " & _
                "last names already saved in the database: " & vbCrLf & vbCrLf & _
                strNames & vbCrLf & "Are you sure this contact is not a duplicate?", _
                vbQuestion + vbYesNo + vbDefaultButton2, "Question?") Then
                ' Cancel the save
                Cancel = True
            End If
        End If
    End If
End If

[andy49]

Are you able to say which line creates the error?

[Micron]

My guess is it fails on the line If Not IsNothing.
Nothing is a value for an uninitialized object variable. You should be checking if the control is Null OR an empty string. Null is sufficient most times, but I have run across two or more occasions when a control seemed to hold no value but was not Null. I recommend a public function in a standard module that accepts the control as a parameter and check for Null and "". You can call this from anywhere in your project.

EDIT - notice the setting of the recordset object to Nothing. A recordset is an object, not a value or variable. "Nothing" cannot be used on those.

[Kaloyanides]

This is the "isnothing" function (see below)

I don't know what's causing the error. I just get a data type mismatch error. I'm not a programmer. I wish I was. There are things about vba that don't make sense to me. As an example (another issue I'm having) I use Private "SaveIt" function and within this function I use Cancel = True. This works perfectly in two forms but fails in one form. The error I get is Variable Not Defined. Again, I don't get this VBA compile error on the two other forms and all three have almost identical coding. I've spent hours trying to track this down...

Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
' Null = nothing
' Empty = nothing
' Number = 0 is nothing
' String = "" is nothing
' Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
' Created By: JLV 01/31/95
' Last Revised: JLV 01/31/95
'-----------------------------------------------------------
Dim intSuccess As Integer


On Error GoTo IsNothing_Err
IsNothing = True


Select Case varType(varValueToTest)
Case 0 ' Empty
GoTo IsNothing_Exit
Case 1 ' Null
GoTo IsNothing_Exit
Case 2, 3, 4, 5, 6 ' Integer, Long, Single, Double, Currency
If varValueToTest <> 0 Then IsNothing = False
Case 7 ' Date / Time
IsNothing = False
Case 8 ' String
If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
End Select




IsNothing_Exit:
On Error GoTo 0
Exit Function


IsNothing_Err:
IsNothing = True
Resume IsNothing_Exit


End Function

[Bulzie]

Put a breakpoint and step though the code. In that code window, click on the grey line to the left of the code line at "IsNothing = True", the line should highlight in red. How run the form or click the button and the code will stop at that line. Now hit F8 to step though each line and notice which line causes the error to trigger.

Also did you run a Compile (in debug tab) on the code, that might tell you what is the cause.

[Micron]

The error I get is Variable Not Defined.

ALWAYS ALWAYS ALWAYS have these two statements at the top of every module
Option Compare (usual word following Compare is Database although Binary and Text are the other 2 options)
Option Explicit - this is automatically added if the vb editor option "Require Variable Declaration" is set. Without this one, variables are not considered until run time, otherwise, they are examined whenever code is compiled. If you misspell strFName as strFNmae (for example) it will catch it). Other than that, cannot comment on code we can't see, and that would be a different subject/thread.

Next, I mistook IsNothing for Is Nothing - didn't realize it was a custom function. What I wrote applies to Is Nothing. Regardless of who wrote the code, I don't like it (I'm a bit of a nit-picker) and some of the statements don't ring true to me. Read here to see why http://allenbrowne.com/vba-NothingEmpty.html

Not sure at this point, but I still think it's because you're passing the wrong type to the function (although I now realize it's a different one). (ByVal varValueToTest) and what follows makes me think that the function is not expecting an object (you are passing the control as an object to what it looks like is expecting a value). So in other words, try Me.LastName.Value. This might be one of the rare times that you cannot omit the .Value property.
If you write
Msgbox Me.LastName OR Msgbox Me.LastName.Value, the result will be the same since .Value is the default property of the control. However, in your case, I think it is be accepted (passed) as an object - the control itself and not its current value.

The advice to step through the code often helps - maybe try that if what I'm suggesting doesn't help.
One other thing: please use code tags when posting more than a few lines of code. It makes the code easier to read.

[Bulzie]

Edit: I would do want Micron says first, add the 2 Option lines and then run a Debug/Compile and see if it tells you the error line.

For a breakpoint, put it on the first line of your OP code at the line below so you start at the beginning of that process. Again F8 though each step to find the issue.
If (Me.NewRecord = True) Then