473,574 Members | 2,350 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Having problem moving out of spurious record

254 Recognized Expert Contributor
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
  5. Private Sub Form_AfterUpdate()
  6.     MsgBox ("Form After Update event")
  7. End Sub
  9. Private Sub Form_BeforeInsert(Cancel As Integer)
  10.     MsgBox ("Form Before Insert event")
  11. End Sub
  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_BeforeInse rt 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.cbxTe stCase)" 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?

Jul 6 '07 #1
3 2428
14,534 Recognized Expert Moderator MVP
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
  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
  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
Jul 7 '07 #2
254 Recognized Expert Contributor
Ah! Me.Undo ! Thanks, Mary. I will try that. It looks promising.

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

Jul 8 '07 #3
254 Recognized Expert Contributor
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_BeforeUpda te 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_BeforeInse rt 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_BeforeUpda te event procedure", but the "new" record remains current. When I click the "previous record" button again, I get the "Form_Curre nt" event for the previous record and then am left in the previous record.

It looks like the Form_BeforeUpda te procedure is correctly undoing the data entry by the Form_current procedure, but is stopping the actual change of record from occurring. I (mis?)understoo d that the BeforeUpdate event is triggered when you leave the current record, and that setting "Cancel=Tru e" 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?

Jul 10 '07 #4

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

Similar topics

by: Giovanni | last post by:
Hi, I need to move a database from an instance running SQL Server 2000 SP3 to another running SQL Server 2000 SP2. Can I just use backup/restore or detach/reattach and let SQL server take care of any downgrading (if any). Many thanks
by: Doslil | last post by:
hi, I have a drop down list on the main form which has a query attached to it.Looks like the record is not moving to the next record,cos when I select the next record from the list it gives an error message "the record could not saved ,it could create a duplicate values in the index,primary key or relationship............".I am also getting...
by: Matt | last post by:
I'm not entirely sure how to describe this issue. I have a number of ComboBoxes in my application which have their text properties bound to a field in a data set. The items loaded in the ComboBox are not data bound (they just use the built in collection property of the ComboBox), and they are all set to use the DropDownList style. When moving...
by: Jeff Silverman | last post by:
I have a PHP program that almost works. I'm running it from the command line and simulating a form using a GET method. That part is working, but I get spurious records with all of the fields concatenated into a single field. <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <meta content="text/html;...
by: sandi | last post by:
Hi all, I have datagridview with one column how can i disable enter key action so the when i press enter key active record not moving to next record (but still stand in current record in grid). thank's you all
by: =?Utf-8?B?UmljaA==?= | last post by:
In a database search application (vb2005), the user wants to be able to scroll through records using the mousewheel. The data display form contains textboxes for the main data and a datagridview for the detail data for each main record. The form also contains btnBegining, btnPrevious, btnNext, btnEnd. In the btnNext.MouseWheel event I can...
by: linkswanted | last post by:
We are your trusted source. World Moving & Storage is bonded and licensed by the U.S. Department of Transportation and is one of the largest residential moving and corporate relocation company in the country. allows you to easily obtain no obligation moving quotes from local movers, long distance movers, international movers, auto transport,...
by: linkswanted | last post by:
We are your trusted source. World Moving & Storage is bonded and licensed by the U.S. Department of Transportation and is one of the largest residential moving and corporate relocation company in the country. allows you to easily obtain no obligation moving quotes from local movers, long distance movers, international movers, auto transport,...
by: SuzK | last post by:
I am trying to calculate in VBA in Access 2002 a moving average and update a table with the calculations. Fields in my WeeklyData table are Week Ending (date) ItemNbr (double) Sales Dollars (double) Sales Units (double) Promo (Text) -- is null or "X" AvgWklyDollars (double) AvgWklyUnits (double) I have a vba module which I thought would...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.