Add unmatched record / edit matched record

[metallicsilver]

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")
Debug.Print DocNameChk & " 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 Code Table for information."
MySet.Close

End If

In this code, I'm trying to add any unmatching "WordDoc" to the list along with a time stamp or edit the time stamp for the matching "WordDoc" in the table "UnmatchedDocuments". For some reason, I can't even get the code to find "UnmatchedDocuments". Please help, been looking at this for the last 6 hrs and can't figure it out. Many Thanks.

 

[Golom]

Maybe

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")
        [red]MySet.Update[/red]
        Debug.Print DocNameChk & " 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 Code Table for information."
        MySet.Close

    End If
End Sub

 

[metallicsilver]

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


The problem is that this line didn't seem to grab the UnmatchedDocument Table from my database so MySet return a null value. As a result, all the rest of the code is wrong.

Thanks.

 

[Golom]

If opening the recordset doesn't raise an error then it's opening something. If it wasn't then you would be seeing an error to the effect that

The Microsoft Jet database engine could not find the object 'UnmatchedDocuments'.

Further, if there is nothing in an opened recordset then both EOF and BOF will be TRUE ... it doesn't return NULL.

The rest of the code isn't "wrong" (other than the missing Update method). If the recordset is in fact empty then all the Seeks will fail but nothing will be added because you weren't updating the record that you were appending.

Test the state of the recordset with something like

Set MySet = myDb.OpenRecordset("UnmatchedDocuments", dbOpenTable)
If MySet.EOF And MySet.BOF Then
    MsgBox "No Records Returned"
Else
    MySet.MoveLast
    MySet.MoveFirst
    MsgBox MySet.Recordcount & " records in Recordset."
End If