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

rowek
rowek used Ask the Experts™
on
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.  

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
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.

Author

Commented:
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!
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
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.

Author

Commented:
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!
Michael CarrilloInformation Systems Manager
Top Expert 2012

Commented:
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.

Author

Commented:
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.
Information Systems Manager
Top Expert 2012
Commented:
Does the error seem to occur on the same record every time or random records?
Also, if you update the value to the same value already in the field, can this create the error.
My line of thinking is to eliminate the possibility that it is a value related error. Perhaps related to the underlining query, tables and the way it is joined.

How many users use this database and how many typically are in the database at the same time?

Also, are you able to reproduce this error on a system that you recently ran Windows Update on?  Given the information you have provided so far, this type of error really seems like it might be an Office or Windows dll issue.

Finally, in your initial statement of the problem you stated:

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.  

Is your code updating the fields then updating the record as described in the KB article?
Have you tried debugging the code by stepping through, to see where in the code the error is being thrown? Can you post your code?

Author

Commented:
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.

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial