I am trying to use the .NoMatch method in my code, however it will not compile correctly giving the error message:

"Compile error: Method or Data Member not found"

any suggestions?

What are you trying to do with .NoMatch? I have not heard of that before, and generally you will only get this error message if the method (.NoMatch) is not found.

Alternatively, you could post some of the code and we could try and work it out from there.

Mike

Mike
EchoVue.com

'************************************************* ***********************
'*This Function searches a defined Table and field checking if an entry *
'*Matching the one provided exists. Used for entering new Contracts and *
'*Suppliers *
'************************************************* ***********************
Function vbaNoMatch(stFieldName As String, stFieldValue As Variant, _
                    stTblName As String) As Boolean

    Dim dbs As Database
    Dim rstUniqueField As Recordset

    Set dbs = CurrentDb

    Set rstUniqueField = dbs.OpenRecordset( _
        "SELECT " & stFieldName & " FROM " & stTblName)

    With rstUniqueField

    'Findmatch for entered value
        'Check if there is a match for the value as an integer

        Select Case .Fields(stFieldName).Type
        Case dbInteger
            '.FindFirst stFieldName & " = " & CInt(stFieldValue)
            If .NoMatch Then Let vbaNoMatch = True

        Case dbDate
            .FindFirst stFieldName & " = " & CDate(stFieldValue)
            If .NoMatch Then Let vbaNoMatch = True

        Case dbCurrency
            .FindFirst stFieldName & " = " & CCur(stFieldValue)
            If .NoMatch Then Let vbaNoMatch = True

        'Check for match as a String
        Case Else
            .FindFirst stFieldName & " = """ & stFieldValue & """"
            If .NoMatch Then Let vbaNoMatch = True
            If Not .NoMatch Then Let vbaNoMatch = False

        End Select

        ' If Find method fails, notify user and return to the
        ' last current record.


' strMessage = _
' "Not found! Returning to current record." & _
' vbCr & vbCr & "NoMatch = " & .NoMatch
' MsgBox strMessage
' .Bookmark = varBookmark
' End If
' FindMatch rstUniqueField, stFieldName & " = """ & stFieldValue & """"

        .Close
    End With

End Function

Thanks - I got it working on my machine.

A couple of questions that might help narrow possible problems down...

If there a certain NoMatch line that this errors out on, or is it all of them?

What version of Access are you using, and has this worked before?

Mike

Mike
EchoVue.com

1: It causes error on all of the .NoMatch lines.

2: I am using 2003, however I think this code was created with 2000

I have made my own database and in order to give it integrated version control, I am having to include this code. Hence my confusion on the matter.

I assume it's worked before...

I betcha' its cause you have references to both the DAO and ADO libraries set in you app, that ADO is listed higher in the references dialog, thus giving it priorty, thereby instatiating an ADO recordset object at:

Dim rstUniqueField As Recordset

which doesn't suppot the NoMatch property (a DAO property).

ALWAYS fully qualify MDAC object declarations :):

DAO.Recordset

or

ADO.Recordset

so your app knows which to create.

HTH,

Bob

Oop...meant ADODB.Recordset

Thank you Bob for your post.

I tried doing the ADODB.Recordset, however it still gave compile errors. what I needed to do was to move the ADO library up in the priority of Libraries.

Cheers,

Hi ggiibboo,

If your app isn't using ADO just remove the reference to the ADO library. If it is just use:

Dim rstUniqueField As DAO.Recordset

instead of

Dim rstUniqueField As Recordset

is what I meant. That way you can just forget about the references dialog hierarchy. Its just good practice to fully qualify any MDAC object variable with "library.object" syntax.

Sound like you got it working though.

Bob