Link to home
Start Free TrialLog in
Avatar of rowek
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.  

Thanks!
Avatar of Michael Carrillo
Michael Carrillo
Flag of United States of America image

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.
Avatar of peter57r
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.
Avatar of rowek
rowek

ASKER

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!
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.
Avatar of rowek

ASKER

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!
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.
Avatar of rowek

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Michael Carrillo
Michael Carrillo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rowek

ASKER

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.
Avatar of rowek

ASKER

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