problem finding the table when using OpenRecordset

[metalicsillver]

Function UpdateUnmatched(WordDoc)
Dim RecordNo As Long
Dim myDb As DAO.Database
Dim MySet As DAO.Recordset

Set myDb = CurrentDb()
Set MySet = myDb.OpenRecordset("UnmatchedDocuments", dbOpenTable)

MySet.Index = "WordDocFile"
MySet.Seek "=", WordDoc.Value

If MySet.NoMatch Then

MySet.AddNew
MySet!WordDocFile.Value = WordDoc
MySet!TimeStamp.Value = Format(Now(), "hh:mm dd/mm/yy")
MySet.Update
Debug.Print WordDoc & " has been added to the Unmatched Code Table.
" & _
"Please check the table for complete information."
MySet.Close

Else:

MySet.Edit
MySet!WordDocFile.Value = WordDoc
MySet!TimeStamp.Value = Format(Now(), "hh:mm dd/mm/yy")
MySet.Update
Debug.Print "Time for Unmatched Record " & Format(RecordNo, "#") & "
has changed. " & _
"Please check the Unmatched Documents for information."
MySet.Close

End If
End Function

I keep on getting a null string for myset even the table exist. Not sure
what I'm doing wrong. Thanks.

 

[Klatuu]

It isn't that it can't find the table. You successfully opened the table
UnmatchedDocuments, or you would have and an error on the OpenRecordset line.
It is you are not finding anything with the Seek method. The Seek method
only works for table recordsets. That is, local tables, not linked tables.
For linked tables you need to use the FindFirst method:

It would be

MySet.FindFirst "[FieldToSearch] = """ & ValueToFind & """"

[FieldToSearch] is a field in the recordset that you want to find a value in.
ValueToFind is either a variable or a reference to a control on your form or
a field from another recordset. Basically, the exact value you want. The
above syntax is used when [FieldToSearch] is a text field. For numberic
fields it would be:
MySet.FindFirst "[FieldToSearch] = " & ValueToFind

And for Date fields:
MySet.FindFirst "[FieldToSearch] = #" & ValueToFind & "#"

It is not necessary to set the index. Jet will use the index automatically.