Avatar of rowek
 asked on

How to: Update fields in code on a bound form in Access 2010

We have a multi-user MS Access app that we have migrated up from Access 97 to Access 2000 then to Access 2003 without major issues. We are now migrating that same code to Access 2010 and we are recieving "Update or CancelUpdate without AddNew or Edit" with some legacy code.

The setup is simple.  We have a bound form called frmPeople that is bound to a query that joins a few tables. When the user does a search we find that record and display it in the form.  The old (and desired) behavior is the user could then type over existing data and it would be saved as soon as the textbox was exited.  Standard MS Access behavior since 1.0.  We are now running into two problems:
1) Every third or fourth update works, the rest throws the error above.
2) We used to be able to update the record's TimeStamp in code by simply setting the DateTime field to Now().  Because the field was a column in the underlying table the update would work.  Now that line of code fails every time.

Why is is necessary to Edit a record in a bound form in Access 2010?  What is the preferred method to ensure all updates are forced to the db and to update various fields in code?  The KB articles say to Edit the record first, update the desired fields, then Update the record, but I get lots of conflicts as to which event to call the Edit, modify, and Update process.  

Microsoft ApplicationsMicrosoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
Michael Carrillo

There are several things you can try:

1. Ensure that Windows and Office are fully up to date. Run Windows update.
2. Run a Compact and Repair on the database.
3. Confirm that the new application has the same references as in the older working applications (This can be found in the VBA code interface).
4. Recreate the database as follows:
  A. Run compact and repair on the old working database.
  B. Create a blank Access 2010 database.
  C. Import all objects from old working database into new database.
  D. Run compact and repair on the new database.
  E. Test

Finally, if this does not work, please post your problem code/database.

What has been found by practice is that the safest way to 'convert' to an accdb is to create a new blank accdb and then import all objects from the mdb.  

You will also need to check that all required library references have been set by referring to the list in your existing application and ensuring that corresponding references have been checked in A2010.

Once you have imported all objects you must compile the application code to check that no new errors have arisen.  It appears that A2010 is less tolerant of less than perfect code than previous versions of Access.

Guys, is there any way to simply stay with an .mdb file?  Customer does not want to migrate the app to 2010, just run it in 2010.  Thanks!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Michael Carrillo

Create a blank database in Access 2003 and migrate all your content into the new database.
Do a compact and repair and test your database.  You should be able to modify your Access 2003 database with Access 2010. However, Access 2010 will not allow you to create a .mdb database.

You should be aware that .mdb is a legacy format.  Much like .doc is legacy to .docx in the current Word application.

You still need to check references and check that the app compiles without errors.

Sorry, was out of office for four days.  I have done everything suggested above, no change.  I still get the "Update or CancelUpdate without AddNew or Edit".  I totally understand that the MDB format is legacy, but I do not have any issues with my .doc documents running in Word 2010.  Do you think this is a real problem with Access 2010 and legacy mdb files or is my approach incorrect?

Am I wrong to ask Access 2010 to update bound columns on a form in code?  Its worked since Access 1.0, not sure why it would stop working in 2010.  Thanks!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Michael Carrillo

Does your form lock the record being updated?  You stated that multiple users use this form and that some record entries do work without an error.  This might be a problem with multiple users trying to update or create records at the same time.

We are using the default locking that JET used to provide for us.  I can assure you we get the error regardless of number of users, but good idea.  We get the error when only one person is in the building using the app, but it seems to happen every third or fourth record.
Michael Carrillo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you, macarrillo1. I will visit the client today and see if I can respond to you this afternoon.  

There is no rhyme or reason to the error message.  Thanks for hanging in there on this problem.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

Sorry so slow.  Just now saw this one was open.