We help IT Professionals succeed at work.
Troubleshooting Question

Access VBA Commit changes on bound form

Murray Brown
Murray Brown asked
on
56 Views
Last Modified: 2020-08-21
Hi
I have a bound form where I want to use VBA code to commit any changes that the user has made to the form.
I tried the following but neither seem to work

DoCmd.RunCommand acCmdSaveRecord
If Me.Dirty Then Me.Dirty = False 
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Either one should work....what are you seeing and where are you trying this?

Jim.
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Better to work in unbound concept and commit the changes if needed
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I just have to disagree with John on this one. If you are going to work with unbound forms, you might as well find some other application other than Access.


Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
I just have to disagree with John on this one. If you are going to work with unbound forms, you might as well find some other application other than Access.

+ 1 on that.

 If you are going to go through all the trouble of doing that, you might as work in something else (like Dev Express/.Net) and ditch the Access baggage.

Jim.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I tried the following but neither seem to work
Then, I guess, you have some code elsewhere - say, in a BeforeUpdate event of a control or the form - that politely prevents the update.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
You're welcome, Murray.  Glad I could help.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Curious when the .NET parameter was introduced in this question and why Access is considered "baggage to ditch."...
To add a bit more to the question.
All the above solutions would/should work but there is a key difference..the other methods allow the data to get inserted and simply pull them back (which might work , might won't..crash/code clashing...etc) while the unbound unless the "commit" button is pressed it is just thin air...no matter what no data will get inserted unless they are commited.
To picture it think it like this...you have Wolverine and Flash..and you fire a bullet at each.
Wolverine has no problem...will get hit and his system will ditch it...but the Flash will simply avoid it...pick your hero....:) ....big fan of both ....:)
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
John, as you well know, the advantage of Access over so many other software development platforms is its bound forms and reports.  These are what make it a Rapid Application Development (RAD) platform.  When you use unbound unbound forms, you have to handle all of the interaction with data via code, which adds a lot of work (and time) to the process.

Just my 2 cents.

Dale
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Exactly.

Beyond that, the main Access "baggage" that you can avoid is that it is so sensitive to its environment.

Being that it does not compile into a true .exe but is more like a document that is read, a lot of things can impact it.   That's not to say other products don't have install/environment problems, but certainly no where near as many.

 Beyond that, how many times have you had to use /decompile?  Deal with database corruption?   Again other products have their own problems and bugs, but in general are a lot more stable when executing because they are compiled and linked rather than being read and executed at runtime.

The other issue with Access is a reflection of its very design; because many development features are built-in, your resulting application is tightly bound to Access itself (and that goes to the heart of this question with bound vs unbound forms).    That's why you don't see any true 3 tier designs with Access.    Lack of full OOP capabilities is another.

 The third thing is the lack of controls, interface elements, tools, etc.    We don't even have a native grid control.   Yeah there is the MS Flexgrid, but it has issues at times.   Take a look at something like Dev Express used with .Net and see what it offers.    To use something with Access, it has to be specifically written for Access as it does not have a complete implementation of the iDispatch interface.

 So if you are going to start going down the road of not using what Access has to offer, then why would you bother using Access?    

 Access is a fantastic tool, but like anything else it has its place and is not a solution for everything.

FWIW,
Jim.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Strange opinions from 2 Ms Access MVPs...... especially when it comes to ditching..and I think for pretty much every "drawbacks" mentioned there have being pretty good solutions.
Anyway unbound does have 1 extra step but besides that nothing too strange.
You design it as a bound form ...on the "last minute" you ditch the recordsource ...so it's unbound...use it pretty much as it was bound..and when everything is correct you just insert the data either with a simple SQL or using recordset object.

Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Strange opinions from 2 Ms Access MVPs...... especially when it comes to ditching..and I think for pretty much every "drawbacks" mentioned there have being pretty good solutions. >>

 Nothing that I haven't said for many, many years.   Before being an MVP, while I was an MVP, and after being a MVP.   You'll also hear it from many others as well, both MVP's and non-MVP's alike.

 Like any product Access has it strengths and weaknesses.   If used in the right situations, it's fantastic.   But in some situations, it can be a poor choice.

Jim.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Sry but I just can't justify the easiness of ditching Access just because it is bound oriented...it's just so  easy to be in either "side" that .....
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.