How to use a button to calcualte 2 fields then store new value in another table

[pbDudley]

Hello


I have a form that is attached to one query that I used to filter records, then I have subforms on the form as well where I just dragged the tables needed on the form. I found some VB that allows a user to enter a plan ID into a text field and it filters all of the subforms that I have on this one form.

2 of the fields that will be manually entered will be used to calculate another field that I have in a table.


My form was starting to get cluttered so my thought process was to have one smaller form where a user will enter some of the data needed than save, then perhaps a button to calculate the field needed, so it would be stored prior to moving onto the final data entry form.

For an example

Table_1, Field: height. A user will enter the height above ground, so let's say height=50

Table_2, Field: ground_level. A user will fill in a number for ground level, let's say, 500

Table_2 Field: difference. This would be the field that I need the calculation, the calculation is ground_level minus height. 450

I tried using a text box and then using the expression builder, it didn't work but I'm not sure this would technically be saving the value, I think this would just be calculating this on the fly

How would I store this, VB that once the fields are entered it subtracts than stores the value? The calculation is just a simple number minus a number, but instead of someone manually subtracting I thought there would be a way to do this.

I assume I can just do it manually, but this is a simple version that I will have to do on a few other fields as well, so I learn from the simple (math calculation) version hopefully.

thank you and I hope this is clear enough, it is hard for me to upload the database

[orange]

Please tell us in plain, simple English what is the process that you are trying to automate. You are describing HOW you are doing something, and that isn't working. Readers need to understand your issue in business terms in order to offer focused suggestions.
It may be more helpful if you provided a couple of examples showing before values and results expected.

[Micron]

How would I store this

In databases, typically you do not do this. Calculations are done on the fly in queries/forms/reports because inputs can change. If they do, calculations can get out of sync. This should be quite acceptable because users should not be poking around in tables, so calcs are shown by these other means. If you must store the calcs, you are better off doing this via calculated fields in tables, but I don't recommend that.

[pbDudley]

I'm trying to save the value that is calculated on the form. It's entered into 2 tables (sub forms) on my form. then the user saves and moves on to another step.

I can do this in a query where it populates on a report that I need it on:

Vdifference:[ground_level]-[table_1].[height]

And this subtracts the 2 fields, but I'm trying to store this value

Perhaps some sort of select query on the control source where I want to save this

SimpleCSV("SELECT................

I just don't know how to do this.
Some data needs to be stored and this is one, I do as much as possible than hand this over to contractors in a couple years after I test it with others, than they make this more professional.

thank you

[mike60smart]

Hi
Are you able to upload a zipped copy of the database containing the tables and forms together with an
explanation of where the value should be saved?

[Micron]

I know you are trying to save a calculation, and I'm saying you should not. If you insist, the easiest and perhaps most reliable method would be to create a table field that is of type "Calculated" in table design view. These work for simple calculations such as what you are doing. That is far less likely to get out of sync.

[Welshgasman]

Simple CSV is for concatenating values?

[pbDudley]

Well, the value needs to be saved, so if it's not calculated I would just do the subtraction in my head and type in the number. But I understand what you are saying, I could do this on the fly on the report as well.

I'll look into the table calculated field thanks
I thought there might've been some other sort of query option in the control source of the field that might do something like this is as well

I cant upload the database, but I might be able to upload a temp, but I think I have enough now to figure it out, hopefully

[Micron]

Using only an unbound control on a form, you would have to use vba to store the value, or run an update query (e.g. with a button click) that can get the calculated value from the form. A table calculated field can do the simple math and display the calc on your form/report.

You keep saying you have to store the value (and that might be true) but you're not justifying it by providing a business case for it. Even if you do, it's possible that it might still be argued that it's not necessary. Whether or not it's risky is another thing.

[June7]

Since fields needed for expression are in separate tables, a Calculated type table field is not possible.

I agree, need for saving this calculated value is not clear.

Build query joining tables and do calc in query for output on report.

[pbDudley]

You all are saying it's not needed to be saved because one can always just calculate it in the long run with the 2 saved fields?

I know that's possible, but we incorporate this now with power bi, and I know I can do the same thing there, the business argument, is it's nice to have this value stored, it's compared to other values that are currently stored. I understand I can just calculate it on the fly, but we use this data to compare to a much larger database. And not every user is going to be able to do the expression, calculated field every time. If it was just to create a report that's one thing, this will be in a new table structure, and the real contractors that I use will set it up more proficiently. But I'm good at least setting up the table structures so they have that. But I and others will have to use this "temp' database for a while before its given to the actual real database programmers, that's not me, I can usually just figure out just enough, with a lot of help from this site and YouTube. So, I am very appreciative for the guidance and help.

I believe I have an older database where I have a button that does a similar method using VB, than appends to the table, so I can look that up

[Micron]

fields needed for expression are in separate tables,

I missed or forgot that.

[Micron]

So power BI or whatever else you might be using is not capable of getting one value from each of 2 tables and subtracting them? If not, then as long as you understand the risk, you can still run an update query on calculation field, assuming you have that field already. If not, you need to add it.

[orange]

We still have not seen a description of the business issue/process. What exactly are you measuring? What is significant about the difference(calculation) that you must store? You're obviously not flying an airplane, but we have no idea of the business. It may not seem relevant, but some context will help readers.

[pbDudley]

Well, I'm just using an example. I don't particularly know what would help if I told you what I need this done for, that is subtract 2 fields in 2 tables and store the value. If I told you the actual numbers, it would still be the same thing, 2 fields being subtracted to calculate and store the value in a new field; worst case scenario I have everyone manually do it. (until what you're getting at, the actual contractors that do this database work can do some of this for me and guide me).

thank you all for the help