By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,435 Members | 2,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,435 IT Pros & Developers. It's quick & easy.

Having problem moving out of spurious record

prn
Expert 100+
P: 254
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterInsert()
  2.     MsgBox ("Form After Insert event")
  3. End Sub
  4.  
  5. Private Sub Form_AfterUpdate()
  6.     MsgBox ("Form After Update event")
  7. End Sub
  8.  
  9. Private Sub Form_BeforeInsert(Cancel As Integer)
  10.     MsgBox ("Form Before Insert event")
  11. End Sub
  12.  
  13. Private Sub Form_BeforeUpdate(Cancel As Integer)
  14.     MsgBox ("Form BeforeUpdate event")
  15.     If IsNull(Me.cbxTestCase) Then
  16.             boolCancel = True
  17.             Cancel = True
  18.             Me.Undo
  19.     End If
  20.     If boolCancel Then
  21.         MsgBox ("Canceled, so we should leave")
  22.         Cancel = True
  23.         Exit Sub
  24.     End If
  25.     MsgBox ("still in BeforeUpdate")
  26.     boolCancel = False
  27.     If (Not (boolCancel)) Then
  28.         Dim strMBtext As String
  29.         strMBtext = "Me.cbxTestCase = " & Me.cbxTestCase
  30.         strMBtext = strMBtext & ""
  31.         MsgBox (strMBtext)
  32.     End If
  33.     If Not (boolRecordDeleted) Then
  34.         If Me.NewRecord Then
  35.             Me.ENTERED_BY = fPersonID(strEnteredBy)
  36.             Me.tbxEnteredDateTime = Now()
  37.         Else
  38.             TEST_RUN.UPDATED_BY = fPersonID(strUpdatedBy)
  39.             Me.tbxUpdatedDateTime = Now()
  40.         End If
  41.     End If
  42.     MsgBox ("Leaving BeforeUpdate")
  43. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCancel_Click()
  2.     boolCancel = True
  3.     DoCmd.Close
  4. 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
Jul 6 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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....

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strMBtext As String
  3.  
  4.     MsgBox ("Form BeforeUpdate event")
  5.     If IsNull(Me.cbxTestCase) Then
  6.         Me.Undo
  7.         MsgBox ("Canceled, so we should leave")
  8.         Cancel = True
  9.         Exit Sub
  10.     Else
  11.         MsgBox ("still in BeforeUpdate")
  12.         strMBtext = "Me.cbxTestCase = " & Me.cbxTestCase
  13.         strMBtext = strMBtext & ""
  14.         MsgBox (strMBtext)
  15.     End If
  16.  
  17.     If Not (boolRecordDeleted) Then ' not sure where this value comes from so left it alone
  18.         If Me.NewRecord Then
  19.             Me.ENTERED_BY = fPersonID(strEnteredBy)
  20.             Me.tbxEnteredDateTime = Now()
  21.         Else
  22.             TEST_RUN.UPDATED_BY = fPersonID(strUpdatedBy)
  23.             Me.tbxUpdatedDateTime = Now()
  24.         End If
  25.     End If
  26.     MsgBox ("Leaving BeforeUpdate")
  27. End Sub
  28.  
Jul 7 '07 #2

prn
Expert 100+
P: 254
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
Jul 8 '07 #3

prn
Expert 100+
P: 254
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     MsgBox ("Form BeforeUpdate event")
  3.     If IsNull(Me.cbxTestCase) Then
  4.             Me.Undo
  5.             Cancel = True
  6.             MsgBox "About to leave Form_BeforeUpdate event procedure"
  7.             Exit Sub
  8.     End If
  9. ...
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
Jul 10 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.