asked on 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
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.
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
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
Last Comment
Scott McDaniel (EE MVE )
ASKER
"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.
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.
Just put the same code in both the Click and DoubleClick event. Doesn't matter if it's fired once or twice, after all ...
ASKER
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
thanks again
You can, but only one fires - either Click or DoubleClick.
ASKER
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.
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.
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:
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.
ASKER
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
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
ASKER
" 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 ?
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 ?
ASKER
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
That said, your method should work, assuming that Me![InvoiceTotalCalculated
If Me![InvoiceTotalCalculated
Me![InvoiceTotal] = Me![InvoiceTotalCalculated
If Me.Dirty Then Me.Dirty = False
This forces Access to save the record if it's dirty.