How To Make Automatic Calculations Without Entering In The Form?

[Douglasrac]

Hi,

I have a form with 3 fields:

- Value to Receive
- Paid Value
- Remaining Amount

When inputting data, I will input Value to Receive, nothing on Paid and Remaining Amount should be a calculation (Value to Receive minus Paid Value).

My question is how can Access automatically calculate the Remaining Amount, if I will input Paid Value in a Query not in the form.
I don't want to come back to the form and search for the registry. I want to modify in the query and when I close the query and open again, Remaining Value should be different, considering the last Paid Value I input.

Is that possible?

[Rainlover]

Remaining Value should not be a Field in the Table, It sould be a Calculated Value.

It will always be correct this way.

[Douglasrac]

Thanks. That worked very well.

My last question is if I can make this calculated field appear in the query also with money format. Instead of just the number, it should be $. I can do that will the other, because they are real field, but this one only appear in the query.

[Rainlover]

The correct way of doing this is by Forms, where you can format it the way you want.

Why do you need to do this in a Query. Usually users do not have access to Tables or Queries.

[Douglasrac]

The correct way of doing this is by Forms, where you can format it the way you want.

Why do you need to do this in a Query. Usually users do not have access to Tables or Queries.

Do you want the short or the long version?

Ok I give you both:

Short: Its much easier to insert it on the query, since only the records I need will be listed, all together.


Long:

Tables/Forms

- Clients
- Insurance details
- Payment Details
- Commission Details

All forms are in a chain. So I always open form Clients that will have an ClientID. Then I press a button inside the form that will open Insurance Details, that will be opened based on the ClientID, bring only the Insurance Details of that Client. Same for Payment Details and Commission Details. Payments are connected to Insurance not Client, since a Client can have more than one Insurance. And Commission connected to Payment, since 1 insurance can have more than 1 payments and so more than 1 commission. So there is no other way (at least not that I know off) to reach Commission Details, but opening the forms one by one, in a chain.

So, what I want is just a list of all commissions that are not paid. I will open this list and input the amount paid.
If I input on forms, as you suggested, I will open Clients form, search for the client name, in Insurance details search for the insurance, in Payment search for the right payment and hit commission and input the value, and then close everything and start again. Doesn't make much sense, does it?

So I just filter in a query all the records that commission is not paid and there I input the values. When I reload the query those records won't be there anymore, only the non-paid ones.

[Rainlover]

So I just filter in a query all the records that commission is not paid and there I input the values. When I reload the query those records won't be there anymore, only the non-paid ones.

If you can do this you can also use that Query as the Record Source of a Form.

You may prefer Datasheet View which looks like a Query but it is really a Form.

[Douglasrac]

Ok thank you! I will work on that.