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

x
?
Solved

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

Posted on 2014-08-22
11
Medium Priority
?
137 Views
Last Modified: 2014-09-25
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.
0
Comment
Question by:a52dragon
  • 6
  • 5
11 Comments
 
LVL 85
ID: 40280395
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
 
LVL 1

Author Comment

by:a52dragon
ID: 40280729
"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
 
LVL 85
ID: 40280819
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 1

Author Comment

by:a52dragon
ID: 40281051
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
 
LVL 85
ID: 40281062
You can, but only one fires - either Click or DoubleClick.
0
 
LVL 1

Author Comment

by:a52dragon
ID: 40281198
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
 
LVL 85
ID: 40282690
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
 
LVL 1

Author Comment

by:a52dragon
ID: 40291656
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
 
LVL 1

Author Comment

by:a52dragon
ID: 40291669
"   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
 
LVL 1

Author Comment

by:a52dragon
ID: 40291697
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 40292213
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question