calculated field in form

Active Member
Need help in solving this problem.
I use a form to populate a table.
The last three fields on the form are:
gross
discount
net

I'm trying to avoid having to type the net amount if there is no discount and still populate the table.

example:
gross = \$100.00
disc = \$0.00
net = \$100.00

I would only type the \$100 amount in "gross" and go on to the next record . The table would record all three fields as shown in "example".
Hope I explained it well
Thanks

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi.

You can do this with a small macro. Create a new macro, add the following lines into the macro:
SetValue : item = [nett], expression = [gross] - [discount] {make sure you use your actual field names that are on the form}
GoToRecord : new
GoToControl : [insert the name of first field on the form indside these square brackets]
StopMacro

Save the macro and close.

Go into the form design, right click the [discount] field -> Data -> Default Value -> 0; Click on the Event tab -> On Exit -> select the macro you created.

Save and close.

HTH, Andrew.

Hi Andrew and thank you for your help.
Your answer took care of the problem and I hope you can further help once more (and for the last time).
If the form has 6 fields in total being:

Cash
Cheque
Credit Card
Gross
Disc
Net

And since the payment can only be eighter Cash,Cheque,or Credit Card
... how can I apply the same logic to "Gross" as in "Net" and end up typing just one amount in again either Cash,Cheque or Credit Card and go on to the next record while having added a completed record in the Table ?

I tried all sort of things includind a second macro but I cannot get it to work.

Thank you Andrew.

What do you want to do? Do you want to be able to enter either the gross or the nett and Access works out the other value?
Andrew

Hi Andrew,
Not sure how this would work but let us say that my customer pays by cheque \$100.
The way this works now thanks to your macro is that I type \$100 in the field "cheque" then I type it again in the field "Gross" and if I leave the "disc" field empty and go on to may next record, Access calculates (on close" the value in the "net" field" and then populates" the table with all the values..... and that's great.
Since the customer pays either by cash , by cheque or by credit card I would like to be able to just type the mode of payment and Access would calculate the "Gross" value then the "Net" value on close.
So to recap and in this example the customer pays by cheque \$100, I would just type once the value \$100 in the "cheque" field and go on to the next record" and Access" would populate the table with all the fields (cheque,\$100 and Gross.\$100 and disc (zero) in this example and "Net"\$100..... Hope this is doable.
Thank you Andrew.

Hi

For the purposes of this example I have assumed your fields are called the following : [cash], [cheque], [credit card], [gross], [discount] and [nett].

Before I start I have to give credit to Denis (SydneyGeek) for enlightening me as to a better way of updating the fields per this thread. Much easier and less clunky.

Let's delete the macro that we created - this can be done much more easily with the expression builder.

If you go into the form design, right click the [cash] field -> Properties -> Event Tab -> After Update -> select [Event Procedure] -> Click the ... button to activate the Expression Builder -> enter this : "[gross] = Nz([cash]) + Nz([cheque]) + Nz([credit card])" (without the quotes), carriage return (i.e. hit the Enter key to create a new line) and then enter this as a second line : "[nett] = Nz([gross]) - Nz([discount])" (Again without the quotes) -> Save and Close the expression builder. Your expression should look like this :
Private Sub cash_AfterUpdate()
[gross] = Nz([chq]) + Nz([cash]) + Nz([credit])
[nett] = Nz([gross]) - Nz([discount])
End Sub
Repeat the above step for both the [cheque] and [credit card] fields.

Right click the [discount] field, delete the macro from the On Exit field, go into the After Update property (like before) and set it to [Event Procedure], click the ... button, and enter this : "[nett] = Nz([gross]) - Nz([discount])" , Save and close.

Go into the properties of the [gross] and [nett] fields and set the "Enabled" property to "No" (you will find it under the Data tab).

Save the form.

HTH, Andrew.

Hi Andrew,
I'm doing something wrong and get this error message:
"The expression you entered contains invalid syntax
You may have entered an operand without an operator"
Any thoughts as to what it is?
Thanks

Hello Andrew,

Figured out what I was doing wrong and that is I was choosing the icon "build" on the toolbar to build the expression instead of the ... on the same line of "after update".

Now all is OK and I thank you again for all your help.

Replies
1
Views
179
Replies
2
Views
146
Replies
10
Views
430
Replies
5
Views
2K
Replies
3
Views
244

1,196,322
Messages
6,014,636
Members
441,832
Latest member
tony tessman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back