[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

the data has been changed another user edited this record

i have a main form and a subform to show the  details.
 
in the main form there are two field i am  looking.
One is a read only field named named MinorityContent.
other is text box to enter a percentage (%) named MinorityContentPercent.
I have an event in for that  to multiply the above 2 fields and show in the read only field.

Private Sub MinorityContentPercent_AfterUpdate()
   
   Dim MinorityContent As Currency

     MinorityContent =  some value from the subform using Dsum function.    
    Me!MinorityContent = MinorityContent * Me!MinorityContentPercent
End Sub

Then in the subform if the user changed any number i need to repopulate the above read only filed named Me!MinorityContent.
To do that i have an event in the Form_afterupdate EVENT like this

Forms![Job Revision - form].Form![Job Revision - subform]!MinorityContent = MinorityContent * Forms![Job Revision - form].Form![Job Revision - subform]!MinorityContentPercent
where MinorityContent  is a variable calculated (same as the one above).

Now my problem is that if i edit the minority content in the main form and then changed any thing in the subform and then edit the value in the subform i get a warning that the data has been changed  another  user edited this recordvalue and saved it error.
How can i avoid this error.(It is in my local machine -no one is editing it aother than me)

0
aio419
Asked:
aio419
  • 7
  • 6
1 Solution
 
harfangCommented:
Hello aio419

For all your forms (main, sub, sub-sub) enable the record selector. Watch the pencil. If the pencil is vivible, the record is being edited. You will see that it's not  saved at times you think it should be, so use any amount of Me.Dirty=False or Me.subform.form.Dirty=False to save the records at appropritate times.

(°v°)
0
 
aio419Author Commented:
Harfang thanks for the reply.

record selector was enabled.

so i added the dirty flag to the following events
in the main form and sub form's form current events( first line)
Private Sub Form_Current()
Me.Dirty = False

and the subform's exit event
Private Sub Revision_Detail_Exit(Cancel As Integer)
Me.Dirty = False

Still i get the same error.
Is that the right place i added the code?
0
 
harfangCommented:
These are two places where a save has no effect. On current, the form just reached a record and did not have time to edit anything yet, and on exit of a subform, the subform's record is saved automatically. However, you write "Me.Dirty=False", meaning to save the main form when you exit from the subform. That normally occurs automatically before you enter the subform.

I was thinking about your code really. Just before and just after you try to write something into a form. The error message is ultimately caused by the fact that you mix different ways to edit the same piece of information, and that Access doesn't follow. The idea was to catch the pencil appearing on a record selector when you edit something elsewhere...

Maybe you should just call the routine of the main form each time, instead of having a distinct version? If you create a public function on the main form called MinorityCompute(), you could call it through "Me.Parent.MinorityCompute". This would help Access to follow what you are really doing.

(°v°)
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
aio419Author Commented:
Thanks for the detaild explanation.
i need to find the sum at two places.
one is, if the percentage is changed in the main form then  i do this  to find the new amount.

Private Sub MinorityContentPercent_AfterUpdate()
   Dim MinorityContent As Currency

     MinorityContent =  some value from the subform using Dsum function.    
    Me!MinorityContent = MinorityContent * Me!MinorityContentPercent
End Sub

that alone is okay.

Now the 2nd case.
If the user changed data in the subform, i need to calculate the MinorityContent again.

So i put the same code in the main form  under the  subform's exit event
Private Sub Revision_Detail_Exit(Cancel As Integer)   'where Revision_Detail is the name of the sub form.
     Dim MinorityContent As Currency
     MinorityContent =  some value from the subform using Dsum function.    
     Me!MinorityContent = MinorityContent * Me!MinorityContentPercent
end sub

as soon as i put this code, problem starts.

In addition to this all the controls are bound controls.
When i edit the subform, the main form's  pencil  is not appearing.

i tried to put the code in a public function also. Agian i get the same error.

I will double the points, if you can help me to fix this never ending erro i am facing.
0
 
harfangCommented:
Hi aio419,

You are struggling with redundant data. The main form contains the result of a calculation, and you are trying to keep that calculation in synch. This is always quite difficult, and never 100% reliable. The normal way to do that would be:

* Create a control on the subform's footer (even if the footer is not visible) with the definition of "some value from the subform using Dsum function", but using Sum() instead. This value will be tracked correctly by Access and display the desired sum after each change in the detail records. Let's call this control txtMinoritySum.

* On the main form, use another textbox with this expression:
  = Revision_Detail!txtMinoritySum * MinorityContentPercent
This new control, referring to a dynamic sum in the subform, will also be automatically synchronized. You can, in other words, remove the field "MinorityContent" entirely. This gets rid of the problem, you no longer need to manually track changes and keep this value up to date.

Note that the use of a Sum() function in the subform's footer would anyway be a better idea that using the DSum() you are referring to.

The second solution would be to go totally "behind the form's back" and write directly to the table. You can do this with recordsets or with an update query. This will work from the subform, because the main form's record is saved at that time, so that another process can change the data without conflict. On the main form, keep your current code (perhaps just replace the DSum() with the Sum() in the subform, though).

If you can make your database available somewhere, I can take a look and correct the problem, but my advice is to get rid of the redundant field. In a database, no field should contain the result of a calculation based on other fields...

(°v°)
0
 
aio419Author Commented:
That is a very good advice.
If I want to go behind the form's back" with an update query in the sub form, what event is good for that?

Thanks

0
 
harfangCommented:
It would be the subform's AfterUpdate event. At that moment, you know that something has changed (not necessarily affecting the sum, but something), or been added, and that the changes have been successfully written to disk. A query would thus see the latest change.

(°v°)
0
 
aio419Author Commented:
But for some reason AfterUpdate event is not firing for subform.Is that happend to you any time?
to fix it i had to cut  the code and select the event from the drop down down list and paste it.
i do not know why
0
 
aio419Author Commented:
as promissed earlier, i doubled the points.
0
 
harfangCommented:
> Is that happend to you any time?

Yes. For an event procedure to run, you need two things: the string "[Event Procedure]" on the property sheet, and a well-formed Sub (correct name and argument list) in the form's module. Normally, the string [Event procedure] is inserted automatically when you create the Sub in VB, but depending on how you do things, this might fail. If the property is blank, Access simply does not generate the event.

Any luck with the update query / recordset update?
(°v°)
0
 
aio419Author Commented:
Yes. it is working.
Thanks a lot for your help.
I posted the same question so many times in the past.This is the first time i got areply.
I am just wondering, is there any way you can give me your email address.
If you can, please email me  to ai0419@wayne.edu
0
 
harfangCommented:
Hi, aio419

Posting your address here is not a good idea, as these pages are indexed and public. I asked a PE to remove yours from your comment, I hope you don't mind.

My e-mail is in my profile page (click on "harfang" in any comment header). Please use it like this:
* post a new question
* to attract my attention, e-mail me with:
   Please have a look at: http://www.experts-exchange.com/Q_21843445.html

I will not answer direct Access questions through e-mail, at least not for free ;)

Cheers, and good luck with your project!

(°v°)
0
 
aio419Author Commented:
That is fine.I just want to get your attention.But still i could not find your email id in the profile page.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now