Having problem moving out of spurious record

[prn]

Hi folks,

I've got something that's driving me crazy here. If you don't want to read a long explanation, this is not the post for you.

My problematic Access app is a DB for keeping track of software test data. Each instance of a test is associated with a "test case", that is, a test item. Each test instance (or "run") may have a number of other characteristics too, such as browser used, OS (XP, Vista, Linux, Mac OSX, etc.), etc., but for the moment, if I can solve one, then I should be able to handle all. Another table contains the data for the "cases" (insert data, delete data, etc.) where each case record has an autonumber key, a name, various descriptors, etc.

So the records for the individual test "runs" contain a foreign key for the "case".

With that out of the way, take the following situation. In the "run" form, I am on the last record in the "run" table. I click the next record button (in the form footer) to go to a new record. A new record shows up with defaults filled in. Now I click the previous record button. Access complains that the Jet database engine cannot save the record because it "cannot find a record in the table 'TEST_CASE' with key matching field(s) 'TEST_CASE_ID', i.e., NULL.

OK. That figures. There certainly is no record in the case table with a null ID and that key field is a required field in the "run" table. OTOH, I don't want Access to save a run instance record in this situation. As I understand it, Access should not try to save a new record unless I have entered some field data, but my Form_Current() event procedure does need to do quite a bit of twiddling to make things work out so it appears that I need to do something special when I want to leave without saving.

So, here's (part of) what I'm trying. Maybe someone can help me figure out where I'm going wrong. I've created a number of event procedures just so I have a place to put a message box to track things. I may be missing some relevant events, but I'm not sure which.

Private Sub Form_AfterInsert()
    MsgBox ("Form After Insert event")
End Sub
 
Private Sub Form_AfterUpdate()
    MsgBox ("Form After Update event")
End Sub
 
Private Sub Form_BeforeInsert(Cancel As Integer)
    MsgBox ("Form Before Insert event")
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    MsgBox ("Form BeforeUpdate event")
    If IsNull(Me.cbxTestCase) Then
            boolCancel = True
            Cancel = True
            Me.Undo
    End If
    If boolCancel Then
        MsgBox ("Canceled, so we should leave")
        Cancel = True
        Exit Sub
    End If
    MsgBox ("still in BeforeUpdate")
    boolCancel = False
    If (Not (boolCancel)) Then
        Dim strMBtext As String
        strMBtext = "Me.cbxTestCase = " & Me.cbxTestCase
        strMBtext = strMBtext & ""
        MsgBox (strMBtext)
    End If
    If Not (boolRecordDeleted) Then
        If Me.NewRecord Then
            Me.ENTERED_BY = fPersonID(strEnteredBy)
            Me.tbxEnteredDateTime = Now()
        Else
            TEST_RUN.UPDATED_BY = fPersonID(strUpdatedBy)
            Me.tbxUpdatedDateTime = Now()
        End If
    End If
    MsgBox ("Leaving BeforeUpdate")
End Sub

Now when I load the form, I get:

• MsgBox for Load event
• MsgBox for Form_Current event
• Now the form appears with the last record
• I click the > next record button (just to the right of the record number)
• MsgBox for Form_Current event
• MsgBox for Form_BeforeInsert event
• And the new record appears in the form, with the cursor in the CASE_ID field
• Now I click the < Previous Record button (just to the left of the record number)
• MsgBox for BeforeUpdate event
• MsgBox "Canceled, so we should leave"
• And the event procedure drops, leaving me still where I was before
• Now I click the < Previous Record button (just to the left of the record number) again and get
• MsgBox for Form_Current event (no BeforeUpdate MsgBox)
• but now I'm sent back to the previous record, where I wanted to go in the first place!

So, any ideas? Why should I have to click the previous record button twice? Why does Access want me to go through the BeforeUpdate event procedure twice?

It is clear that the first time through BeforeUpdate, the global variable boolCancel is set to True. The only other place boolCancel is referenced (currently) in in a procedure for a "Cancel Entry" button. The procedure is:

Private Sub btnCancel_Click()
    boolCancel = True
    DoCmd.Close
End Sub

and clicking this button does successfully close the form without saving a new record. But I don't want to close the form. I just want to trap the "previous record" button (shouldn't it generate some kind of event?), check for a valid new record and if it is not valid, go back to the previous record. (Once I can do that, I can ask whether the user wants to fix the record or just abandon it, but for now, I just want to figure out a way to trap the button event.) Or maybe it would be better not to trap the button event and handle it all in BeforeUpdate? If so, how should I go about that? It seemed to me that "If IsNull(Me.cbxTestCase)" ought to be a reasonable way to trap cases where I might not want to save, but I've clearly got something wrong.

Clues for the clueless, anyone?

Thanks,
Paul

[MMcCarthy]

Hey Paul

I think what you need to look at here is precendence. I also don't think you need boolCancel at all. Try this....

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMBtext As String
 
    MsgBox ("Form BeforeUpdate event")
    If IsNull(Me.cbxTestCase) Then
        Me.Undo
        MsgBox ("Canceled, so we should leave")
        Cancel = True
        Exit Sub
    Else
        MsgBox ("still in BeforeUpdate")
        strMBtext = "Me.cbxTestCase = " & Me.cbxTestCase
        strMBtext = strMBtext & ""
        MsgBox (strMBtext)
    End If
 
    If Not (boolRecordDeleted) Then ' not sure where this value comes from so left it alone
        If Me.NewRecord Then
            Me.ENTERED_BY = fPersonID(strEnteredBy)
            Me.tbxEnteredDateTime = Now()
        Else
            TEST_RUN.UPDATED_BY = fPersonID(strUpdatedBy)
            Me.tbxUpdatedDateTime = Now()
        End If
    End If
    MsgBox ("Leaving BeforeUpdate")
End Sub
 

[prn]

Ah! Me.Undo ! Thanks, Mary. I will try that. It looks promising.

(Sorry about leaving boolRecordDeleted in. I do need to pay attention to that, but for the present, it just confuses things.)

Thanks!
Paul

[prn]

I finally had a chance to test this. We've had some problems with the SQL server box that holds the database for this app. Fortunately it is one of the test servers, so getting it fixed and me able to continue testing this app was not as high priority as it might have been had it occurred on a production server.

Anyway, I revised the Form_BeforeUpdate event procedure following Mary's suggestion. The relevant portion is now:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    MsgBox ("Form BeforeUpdate event")
    If IsNull(Me.cbxTestCase) Then
            Me.Undo
            Cancel = True
            MsgBox "About to leave Form_BeforeUpdate event procedure"
            Exit Sub
    End If
...

At this time, I can "load" a new record by pressing the "new record" ">*" button at the bottom of the window to the right of the record number. I then get a Form_Current event and a Form_BeforeInsert event. I understand from the MS doc page on the BeforeInsert event that this occurs when the "user types the first character in a new record" and I have confirmed that it is triggered during my Form_Current event procedure.

Now, I immediately click the "previous record" button just to the left of the record number and I get the BeforeUpdate event, followed by the MsgBox for "About to leave Form_BeforeUpdate event procedure", but the "new" record remains current. When I click the "previous record" button again, I get the "Form_Current" event for the previous record and then am left in the previous record.

It looks like the Form_BeforeUpdate procedure is correctly undoing the data entry by the Form_current procedure, but is stopping the actual change of record from occurring. I (mis?)understood that the BeforeUpdate event is triggered when you leave the current record, and that setting "Cancel=True" should allow you to leave the record without updating (or entering a new record in the DB) but so far, it seems to allow me only to abort the update without actually leaving the record.

Anyone have any clues for me on how to get BeforeUpdate to allow me to continue leaving the record?

Thanks,
Paul