problem finding the table when using OpenRecordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

metalicsillver - 22 May 2008 21:53 GMT

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.

Reply to this Message

Klatuu - 22 May 2008 23:14 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> Function UpdateUnmatched(WordDoc)
> Dim RecordNo As Long
[quoted text clipped - 34 lines]
> I keep on getting a null string for myset even the table exist. Not sure
> what I'm doing wrong. Thanks.

Reply to this Message