Save record on another form ACCESS 2010 VBA

I have a data entry form that if the client is in another country, a separate form opens for the user to enter a few details.  I am trying to save data on both forms when the user closes the main form.  I tried the below but does not work.  

Forms!frmPersonAAInfoSub.RunCommand acCmdSaveRecord
Sandra SmithRetiredAsked:
Who is Participating?
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.

PatHartmanCommented:
Forms don't store data.  Tables store data.  If you want to add the same data to multiple tables, you might want to rethink your process.  Perhaps adding a flag to the original table will solve the problem of how to separate the data when necessary.

If the second form is based on the original table and you are just popping it up to capture additional data, then simply save the current record before opening the form.

If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
End If
    docmd.Openform "formname", acNormal,,"PK =" & Me.PK

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
So what do you do if the customer is in the "Same" country ("USA")
...in other words, why two separate processes...?

What are these "Few Details" needed for other countries...?

Another approach would be to store this data in a "Child" table...

Either way, you would first complete the data on the main form.
when you close the main form, ...or switch to another form, ...the data on the main form should be saved automatically.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
is the form frmPersonAAInfoSub bound to the same table as the main form?

If yes, it is critical to save the data on the main form before opening the form frmPersonAAInfoSub. If you don't you will get write conflicts

Me.Dirty = False ' save the record 
Docmd.OpenForm "frmPersonAAInfoSub",,,,,acDialog

Open in new window



To save the data on another form names frmPersonAAInfoSub

chage:

Forms!frmPersonAAInfoSub.RunCommand acCmdSaveRecord 

Open in new window


to


forms!frmPersonAAInfoSub.form.Dirty = False

Open in new window


You might also consider opening the form frmPersonAAInfoSub as a dialog (modal/popup)  form. This will force the user to close it before returning to the main form. This will automatically save the data.

Something like:

Docmd.OpenForm "frmPersonAAInfoSub",,,,,acDialog

Open in new window


If I were doing this I would place the form frmPersonAAInfoSub on the main form. Assuming that the field are in a different table. If the same table move the controls to the main form. Set hem as hidden ( property .visible = False ) if not needed.

example:
Dim bolOtherCountryFlag as boolean

' Set the country flag 

bolOtherCountryFlag = False ' set to false is the same country - true is other country

Me.TxtBoxOther1.visible = bolOtherCountryFlag
Me.TxtBoxOther2.visible = bolOtherCountryFlag
' add any additional controls.

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@PatHartman


TIP if the Day:


If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
End If

Open in new window

can be changes to only a  the single line:
Me.Dirty = False

Open in new window

If the form is dirty Access will save the data. If not dirty then no error is raised. So there is no need to test if the form is dirty first like with your suggestion.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If not dirty then no error is raised. So there is no need to test if the form is dirty first like with your suggestion.
I would have to disagree with you on that one. I have experienced errors many times when I use only "Me.Dirty = False". Granted this may have only happened in older versions of Access (I can't remember now) - but old habits die hard.
0
PatHartmanCommented:
Thanks Boyd but that expression is a hack and obfuscates what is truly happening.  It is like saving a record by using Me.Requery or Me.Refresh (which as we all know, cause unexpected side-effects for the unwary).  If you ask people who have never before encountered the expression, they think that the save is being cancelled rather than being forced.  I recommend that if people use it (I don't), that they add a comment to the effect that it is forcing a save so people encountering the expression in the future know what it is doing.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Scott

I would have to disagree with you on that one. I have experienced errors many times when I use only "Me.Dirty = False". Granted this may have only happened in older versions of Access (I can't remember now) - but old habits die hard.

Since the OP stated 2010 I know it works  in that version.

Just verified with Access 2000, 2002, 2003, 2007, 2010, and 2013 and it works just fine.

I don't have Access 2.0, 85 or 97 available at this time so I cant test those.
0
PatHartmanCommented:
I have experienced failures in A2010 and A2013.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@PatHartman,

I agree that it is  power code and not obvious like move VBA code.  

When working on other peoples project s I use this: :

' Save record if needed
Me.Dirty = False

Open in new window


It is like saving a record by using Me.Requery or Me.Refresh
I have to disagree. It  not like that at all.

Me.Dirty = False is the same as   DoCmd.RunCommand acCmdSaveRecord
0
PatHartmanCommented:
"Power code" is in the eyes of the beholder.  Me.Dirty = False is the same as the explicit method in that it doesn't appear to have any unwanted side effects but it is obtuse none the less and therefore should not be used without specific reason.  I understand that the original recommendation arose out of an error caused by using DoCmd.RunCommand acCmdSaveRecord but I have experienced exactly the same issue it was intended to overcome in databases I have acquired from others.  An app that was working fine all of a sudden experiences a save error.  In fact, I've never run into the error using the explicit method but have seen it using the Me.Dirty = False method in both A2010 and A2013.  Saving a record is important and it should be explicit.  It should not happen as a side effect of a different command.
0
Sandra SmithRetiredAuthor Commented:
Appreciate the dialog.  This is a separate form with data being saved to another table.  The main form is large and there is no room for this.  Right now, I have it as a popup when it meets certain criteria as it is not needed for every client.  I will wade through the suggestions and get back.
0
PatHartmanCommented:
My original suggestion to save the record first before opening the form will solve your problem.  Choose which ever save method appeals to you based on the dialog.
0
Sandra SmithRetiredAuthor Commented:
Sorry the Me.Dirty = False did not work and I simply gave up on the bound form.  I finally made the form unbound and used code to save the data.  I hate bound forms as they cause me more problems than they solve.
0
Sandra SmithRetiredAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for ssmith94015's comment #a40916130

for the following reason:

Resolved another way than using a bound form.
0
PatHartmanCommented:
Sorry the Me.Dirty = False did not work and I simply gave up on the bound form.
And you didn't try the DoCmd.RunCommand. acCmdSaveRecord method?????  Both Scott and I told you that you might have a problem with the Me.Dirty = False method.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just verified with Access 2000, 2002, 2003, 2007, 2010, and 2013 and it works just fine.
I don't have Access 2.0, 85 or 97 available at this time so I cant test those.
Works fine for me most of the time too - but it has most certainly NOT worked fine for me on a number of occasions until I added the If Me.Dirty Then portion. Of course if you Cancel the BeforeUpdate event you'll get errors, but you'll get that regardless of the method used to save.

My point is that just because it works in your environment, doesn't mean it'll work in all environments, nor does it mean that you can use the single line save as you suggested. Using Me.Dirty = False is a non-standard method (although I use it all the time), and anytime you use non-standard methods you run the risk of troubles.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
FWIW: I agree with Pat's objection to this. Your method does not address the issue of saving the record on the second form, so at the very least there should be a Delete.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Scott,

My point is that just because it works in your environment, doesn't mean it'll work in all environments, nor does it mean that you can use the single line save as you suggested. Using Me.Dirty = False is a non-standard method (although I use it all the time), and anytime you use non-standard methods you run the risk of troubles. 

Open in new window


I must just be really lucky. It has work for 15+ years on 1000's of PCs and 50+ terminal servers in over 1000+ of  database with every version of Windows and Access (full and runtime)  combination  since 2000 without ever an issue.

A thought .. maybe I don't have issues due more to my CRUD coding style is more compatible with this method since it works in so many different environments.
0
PatHartmanCommented:
Boyd,
I've had no problem with the standard method for 20 years which is why I never felt the need to adopt the hack.  It was only when I acquired a couple of databases last year that used it did I run into trouble.  Regardless, checking for dirty first seems to be cleaner than arbitrarily forcing a save when it isn't needed.
Pat
0
Sandra SmithRetiredAuthor Commented:
As state in my question, I did try to RunCommand and it would not work.  I tried the other suggestions and they did not work so I did what I knew would work.  I inherited this database which is 15 years old and still was in ACCESS 2000 and had a minimum of five other developers, with various skill levels, having worked on it.  It is a mess to begin with, even had option explicit turned off and when I turned it off, error starting throwing everywhere.  I appreciate all the suggestions and help, but simply would not work in this environment.
0
PatHartmanCommented:
As state in my question, I did try to RunCommand and it would not work.
Your original attempt was not the same as what I posted.  Access AUTOMATICALLY saves data when you close a bound form.  You would never force data to save in formB from code in formA - which is what you were trying to do.  Since the data in formB was related to formA, you needed to save the data in formA before opening formB and that is what the code I posted was doing.
did not work
Is not very helpful.  We don't know if you got a compile error, a runtime error, or what happened, if anything.
Option Explicit requires variable declarations.  If you get errors when you turn it off, that means that the programmer was sloppy with his variable declarations or didn't bother at all.  A sure sign of an amateur.

Please close the thread again.
0
Sandra SmithRetiredAuthor Commented:
As to option explicit, yes, I turned it back on, typo in my comment.  Problem is prior developers WERE sloppy and did not follow conventions.  Also, when I get a suggestion, I copy/paste that just in case what I did was not correct.  Again, the options suggested simply did not work and I finally fell back onto something that I knew would get the job done.  When the form closed, it simply acted as though nothing had changed, that is, it did not recognize data or changes to the data and I am running out of time to get this thing done.  I did not get an error, I just did not get the data into the table.
0
PatHartmanCommented:
If formA is showing data that formB modifies.  FormA will not immediately reflect the changes made in formB.  Form data is refreshed in the background at specified intervals.  I think the default is 1 minute.  If you know that formB made a change that you want to see immediately, always open formB in dialog mode.  The when formB closes, your formA code regains control and you can do a me.Refresh to update what formA is showing.

I agree, we are done.
0

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
Sandra SmithRetiredAuthor Commented:
Pat, thank you for the information.  I really do appreciate your input and I have another project with the same issue, but will use your suggestion for that as I am the only developer so I can get it right from the get-go.

Sandra
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
A thought .. maybe I don't have issues due more to my CRUD coding style is more compatible with this method since it works in so many different environments.
Very well could be the case. The times I had issues were when I was working on apps created by others, and I did not write the original code. I never really took the time to figure out why/how it happened, I just added the If Dirty section and all was well.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Accept these two:
http:#a40913952
Http:#a40914615
0
Sandra SmithRetiredAuthor Commented:
Delete please as resolvedmy own way.
0
Sandra SmithRetiredAuthor Commented:
The solution was to make it an unbound form and when the user closed the first form, it triggered the procedure to save the record to the sale with the DAO.Recordset process.

Private Sub SaveData()
On Error GoTo ErrorHandler
    Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblEmployees", dbOpenTable)
With rst
    .AddNew
    !EmpID = Me.txtEmpID
    !EmpFirstName = Me.txtFirstName
    !EmpLastName = Me.txtLastName
    !EmpPosition = Me.txtPosition
    !EmpAddress1 = Me.txtAddress1
    !EmpAddress2 = Me.txtAddress2
    !EmpCity = Me.txtCity
    !EmpZip = Me.txtZip
    !EmpState = Me.cboState
    !EmpCountry = Me.txtCountry
    !EmpEmail = Me.txtEmail
    !EmpDOB = Me.txtDOB
    !EmpSSN = Me.txtSSN
    !EmpDriversLicense = Me.txtDriversLicense
    !EmpPhone = Me.txtPhone
    !EmpStartDate = Me.txtStartDate
    .Update
End With
    rst.Close
    Set rst = Nothing
0
PatHartmanCommented:
I'm glad you found a solution but a bound form would have worked just fine.  It is a matter of understanding how Access works.  Access ALWAYS saves the record in a bound form whenever that form closes (in addition to saving under other situations).  You didn't need to force it to happen.
0
Sandra SmithRetiredAuthor Commented:
yes, I know it always saves, but sometimes the error checking and validation is so extensive that trying to do it in a bound form causes more problems.  I have found through the years that control works better with unbound forms.  I only use bound forms for very simple cases, but a lot of what I do is very complex and requires intricate validation rules and checking.  Things that in a bound form simply are more difficult to achieve.  Both have their places in development, neither is better or worse that the other, more of a developer preference than anything else.  I was just trying to get what I inherited to work and finally went back to what I know would work and in the timeframe to get it done, was the fastest.
0
PatHartmanCommented:
We're going to have to agree to disagree on pretty much everything you said.  

Over the years, I've come to understand how events work and so in my complex applications, I can control them by using the appropriate events.  I don't ever have to resort to unbound forms.  I don't see the bound vs unbound as a matter of preference.  I see it as working with Access or fighting it.

Developers frequently try to use events other than the control's BeforeUpdate and the form's BeforeUpdate for validation and it just doesn't work.  You have to put your validation in multiple events once you get off on the wrong track and even then you leave holes.  I acquired one application where I removed 5,000 lines of ineffective code from a single form and put back less than 200.  The developer had used three control level events and duplicated the code, cumulatively control by control.  So the first control validation was done in three events.  The second control copied what was done in the first control's three events and added the code for itself.  The third control did 1, 2, and then itself in three events, etc.,  And in no case was any code ever put into a control's BeforeUpdate event or the form's BeforeUpdate event so the 5,000 lines of code was doing exactly - nothing.  It was raising a lot of error messages but many were after the record was already saved and you could blow by all of them.
0
Sandra SmithRetiredAuthor Commented:
LOL, yes agree to disagree.  I have come across inherited applications with thousands of lines of code which I managed to condense into a few.  In fact, the one I am working on now just removed about seven pages of code to one procedure. There have been five other developers on this application before I got it and it is sloppy to pure laziness, not bothering to understand what was created, only adding another layer of duplicate code procedures on top of another.   I think it is more a function of developer experience vs those that have just read a book or two not realizing there is more to developing than that.  The city I work in, if there is an existing application created by a certain developer, I refuse to work on it.  I am not the only one who will not work on this "developers" applications as they are a mess.  We all agree it is faster to simply re-create the application using normal standards than try to figure out the mess this guy left, much to the detriment of the client.  I look forward to future discussions!  I learn from everyone I interact with and appreciate the exchange.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Moving to an unbound form isn't an answer to the question. IMO, if the author doesn't want to accept any Expert comments, they should delete the question.

But that's just my opinion ...
0
PatHartmanCommented:
I concur.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
i agree with Scott's opinion.

I backed away since the Author did not seam to be willing to try any of the expert's suggestions. at the time. I see there has been some effort now.  My suggestion would have fixed the issue. I have used it to solve the same issue for other clients.
0
Sandra SmithRetiredAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for ssmith94015's comment #a40932205

for the following reason:

worked
0
Sandra SmithRetiredAuthor Commented:
Will use on another application as this one was completed with a different approach of my own.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.