How do I get a form calculated value into a table.

Access2003 running in Access2010


History this db was started in Access97 was not intend to grow into and office management app. It was to write specifically structured manufacturer mandate warranty forms.
It now maintains inventory, invoices, work history, man hours, client info, and history of maintenance done on each piece of equipment that the client has some up to 500.

It is intended to be completely rewritten in Access 2010/2013 after the first of the year.

Have this code  that when you click the refresh button(in the footer of the form) it updates the field "InvoiceTotal" from a calculated field on a form
It works fine unless you have click happy users. If you double click refresh , it not only does not update the table but it blanks out any previous data.

So if I rem out this line "   Me![InvoiceTotal] = Me![InvoiceTotalCalculated]    " it will not update table and it does not erase existing data in the table

These are the fields "[Total] + [Text144] + [Text150] + [Text152]"( all calculations from other sub-forms) used to calculated the field   InvoiceTotalCalculated

"   InvoiceTotal  " is the field in the table to be updated.



*****************************
Private Sub Command191_Click()
On Error GoTo Err_Command191_Click

  '  InvoiceTotal = [Total] + [Text144] + [Text150] + [Text152]
    Me![InvoiceTotal] = Me![InvoiceTotalCalculated]
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
   
 
Exit_Command191_Click:
    Exit Sub

Err_Command191_Click:
    MsgBox Err.Description
    Resume Exit_Command191_Click
   
End Sub

Have been working for many weeks off and on trying a multitude of things to no success any assistance would be greatly appreciated. I am not a full time programmer, I live in a rural area and have a full service computer business servicing many small business.
LVL 2
a52dragonAsked:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would suggest that you NOT store calculated values in the database, since you have the root values available to make the calculation "on the fly". In general this is a bad practice, and can cause your "Total" values to be off if someone changes a root value, but does not do so from the right place.

That said, your method should work, assuming that Me![InvoiceTotalCalculated] contains the correct value. If it does not, then you'd have to trace that back to find out what bit of data is invalid.

If Me![InvoiceTotalCalculated] is correct, but is not writing to the database, then you may have other issues. The code you're using is very basic VBA, and about the only thing you might consider changing is to use a different method to force a save:

Me![InvoiceTotal] = Me![InvoiceTotalCalculated]
If Me.Dirty Then Me.Dirty = False

This forces Access to save the record if it's dirty.
0
a52dragonAuthor Commented:
"I would suggest that you NOT store calculated values in the database, since you have the root values available to make the calculation "on the fly". In general this is a bad practice, and can cause your "Total" values to be off if someone changes a root value, but does not do so from the right place."

I totally get this and that will all change in the complete rewrite after the New Year.

The code works fine UNLESS you double-click quickly. Several of the users are older and somewhat computer challenged, it is hard for them to remember where to double or single click.

If I put this little piece of code in "  MsgBox ("Refresh Complete")  " It eliminates the opportunity to double-click the button

I could probably live with this except I can not find/figure out how to relocate from center of page to right over the " Refresh Button" in the footer. Do not want to punish the rest of the users because of a few.

Thank you Scott for your response.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just put the same code in both the Click and DoubleClick event. Doesn't matter if it's fired once or twice, after all ...
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.

a52dragonAuthor Commented:
you know that is what I thought Tried it and the double click just does not seem to work right wonder if you can have code in both at same time.

thanks again
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can, but only one fires - either Click or DoubleClick.
0
a52dragonAuthor Commented:
Is there somewhere  a listing of order of processing?    ie  if you have code in "after update" or "on exit"

Say you type in a value and hit enter which will trigger those events and setfocus on the next tab stop.
I have noticed that the flow is not what I would have expected.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes: http://office.microsoft.com/en-us/access-help/order-of-events-for-database-objects-HP005186761.aspx

And my comment "but only one fires - either Click or DoubleClick." is incorrect. Both events fire if you doubleclick a Command button. Also, a SECOND click event occurs when you doubleclick a Command button:

Double-clicking a control causes both DblClick and Click events to occur. For example, when you double-click a control other than a command button, the following sequence of events occurs for the control:

MouseDown → MouseUp → Click → DblClick → MouseUp

When you double-click a command button, these events occur, followed by a second Click event.
0
a52dragonAuthor Commented:
Thank you for the link, it is somewhat helpful.
So the below code is part of a "REFRESH button in the footer of the form and when clicked once it updates the " InvoiceTotal" field. when I create the exact same button directly on the form it does not update the "InvoiceTotal" field.
So reading thru the "Order of Events.." I did not see a direct reference to "Refresh" can I assume it is the same as exiting the form?  

FYI My temporary fix was to put a message box on the double-click that says "You Double Clicked"  


*****************************
Private Sub Command191_Click()
On Error GoTo Err_Command191_Click

  '  InvoiceTotal = [Total] + [Text144] + [Text150] + [Text152]
    Me![InvoiceTotal] = Me![InvoiceTotalCalculated]
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
   
 
Exit_Command191_Click:
    Exit Sub

Err_Command191_Click:
    MsgBox Err.Description
    Resume Exit_Command191_Click
   
End Sub
0
a52dragonAuthor Commented:
"   Double-clicking a control causes both DblClick and Click events to occur. For example, when you double-click a control other than a command button, the following sequence of events occurs for the control:

MouseDown → MouseUp → Click → DblClick → MouseUp

When you double-click a command button, these events occur, followed by a second Click event.  "


MouseDown → MouseUp → Click → DblClick → MouseUp followed by MouseDown → MouseUp → Click →  MouseUp ?
0
a52dragonAuthor Commented:
One of my good friends here in Napa is a big Gamecocks fan, like when the game is on you can here him at the other end of the street. Thanks Again for your support.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
I'm not sure what that does, but if it refreshes then that may be your issue. Try removing that line and see what happens.
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
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.