Using DAO, why does .edit of a record make it disappear?

In the attached VBA code, I don't understand what is happening. I seek a record (successfully) and then go into .edit mode, move correct data into the  .fields, then .update the record being updated - and then the record disappears!!  When I query after running this code, the original record (the one found using .seek), is gone. The .add branch of the "if .nomatch" clause executes perfectly.

This code is in a form's BeforeUpdate event.

Any ideas?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you saying the record disappears from tblTransactions after editing?

If so, I'd say you have some serious issues with your environment. Issuing Edit and Update commands should do only that - edit and update the record. Unless there is other code that is run that could potentially remove the record, of course.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Seems odd you would have this code in the Form Before Update event?
I assume this is a Bound Form (otherwise the BU event would never happen). So what is the Recordsource of this Form?
The main intent of the Form BU event is to do validation of required fields, etc.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Also, clearly define "disappear".
I know you say:
the original record (the one found using .seek), is gone
But I don't see anything there that would "delete" a record
So, is the record actually deleted, ...or is it just being overwritten?
In other words, is the record count the same, or does it decrease by one?

Try running this code twice.
Once on a test table with only one record, (a "matching" record) and see what happens.
Then run it again on another one record table (a table with an "unmatched" record), and see what happens...
This way you are only ever dealing with one record in the entire table.

Always keep things simple.
Did this code ever work?
Try first simply identifying the matches and no matches, in one procedure.
Then do the "Updating" in a another procedure.
If this works, then you can combine the two.
If not, then all you have to do is to troubleshoot the non working code, ...not and entire complex sub that depends on a input from a function.

SowleManRetiredAuthor Commented:
Joe Anderson's answer was the most influential.  Joe suggested that I might be using BeforeUpdate inappropriately. That comment made  me rethink my whole design for that form and I am going to try to rewrite it using much less VBA. I programmed for a living decades ago using COBOL, and I designed this form using old design principles.  

Thanks to Mr. Coachman - you gave my problem serious thought.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.