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] " 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.

Scott McDaniel (EE MVE )

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.

a52dragon

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.

Scott McDaniel (EE MVE )

Just put the same code in both the Click and DoubleClick event. Doesn't matter if it's fired once or twice, after all ...

a52dragon

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

Scott McDaniel (EE MVE )

You can, but only one fires - either Click or DoubleClick.

a52dragon

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.

Scott McDaniel (EE MVE )

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.

a52dragon

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

a52dragon

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 ?

a52dragon

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

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial