Microsoft Jet engine cannot find a record in the table

[nschroeder]

I'm developing a work order system that creates assignments for IT users to complete for various employees. Each assignment will have notes attached. The issue I'm having relates to three of the tables. WOrders (pk=WONum) has a one-to-many relationship with Assignments, and Assignments (pk=WONum, SysNum) has a one-to-many relationship with Notes. Notes (pk=NoteNum) is linked to Assignments by WONum and SysNum.

There is an Employees form with a Show Notes button that pops up a Notes form listing all notes tied to that employee (the WOrders table includes an EmpNum field).

The Notes form only allows record additions, not edits or deletions. Therefore the BeforeUpdate event will only fire when adding new records. In order to add a note, new WOrder and Assignment records are required, so the BeforeUpdate event adds them using recordset AddNew and Update commands. This works fine and the records get added, but when the code exits from the BeforeUpdate event, it gets the error, "Microsoft Jet database cannot find a record in the table 'Assignments' with key matching fields 'WONum,SysNum'. The new record is there with the matching keys, but I'm thinking some kind of requery is needed so the form can find it. Doing a Me.Requery from within a BeforeUpdate event causes other problems, so how do I make the new record known to the form?

Sorry for the convoluted issue. Hope someone can make sense of it. Here's the code, if it's any help, but it all works, and the error occurs after the Exit Sub.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo Error1
    Set rs = db.OpenRecordset("WOrder", dbOpenTable)
    With rs
        .AddNew
        !EmpNum = Forms!Employees.EmpNum.Value
        !ReqDate = Date
        !InitiatorNum = MyUserNum()
        Me.WONum.Value = !WONum
        .Update
    End With

    On Error GoTo Error2
    Set rs = db.OpenRecordset("Assignments", dbOpenTable)
    With rs
        .AddNew
        !WONum = Me.WONum.Value
        !SysNum = Me.SysNum.Value
        .Update
    End With
    Exit Sub

Error1:
    MsgBox Err.Description & "  WO could not be generated."
    GoTo ErrorExit
Error2:
    MsgBox Err.Description & "  Assignment could not be generated."
    GoTo ErrorExit
ErrorExit:
    Cancel = True
End Sub

Thanks for any help!

 

[peregrin]

Hi,

Maybe it would work to put the notes in an unbound field that gets appended to the notes table after the main fields update -- e.g., in an *AfterUpdate* rather than a *BeforeUpdate*.

P.