Need the help of a code whiz for complex thermodynamic calculations on Access form 1

[MacroScope]

I have built a calculator using unbound controls on a form in Access that functions very much like a dynamic Excel spreadsheet. Changing a value results in an instantaneous recalculation of other values that are dependent on that input. The one calculation that eludes me is the one that outputs the heat capacity of water at a given temperature and pressure. It is a highly complex calculation, and the resultant output is critical to my final calculations.

I was fortunate enough to find online exactly such a calculator in VBA as an add-in for Excel, and it works perfectly there. I have attached an Excel spreadsheet that has the two input fields and the calculated output field so you can see it in operation. The VBA code which controls it is visible by hitting ALT+F11 while viewing the spreadsheet.

I'm trying to migrate the calculation called cpW() from Excel VBA to my Access calculator and I'm having a devil of a time with it. I'm hoping that one of the resident code whizzes here can look at it and help me successfully conclude that migration.

My Access field that is equivalent to Excel temperature B1 (in degrees C) is called [BTemp], and the field that is equivalent to Excel pressure B2 is my [Depth] field /10, since pressure in bar is equivalent to depth in meters /10. If anyone can create a three field Access form with two unbound controls, [BTemp] and [Depth] as inputs and the third, called [HeatCap], as the calculated heat capacity result just the way the Excel sheet does I would be greatly appreciative. The answers are there in front of me but I just don't know how to put it together.

Thanks to all in advance.

 

[PHV]

What is the code of the cpw function in water97_v13.xla ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OK, found the xla on the web, exported the water97_v13 module and imported it in an access db.
Seems to work OK for me.
So, the formula is:
Me![HeatCap] = cpw(Me![BTemp] + 273.15, Me![Depth] / 10) * 1000

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

Thank you.

On the calculator I have built, the ControlSource of each field is either unbound or it is a calculated field, and it's treated just as the cells on the Excel spreadsheet are. So I might have this in the ControlSource in the L/Sec control, as an example.

=([Diameter]/2)^2*Pi()*[Flow]*1000

BTW, I have a Pi() function.

That said, of course Me![HeatCap] = cpw(Me![BTemp] + 273.15, Me![Depth] / 10) * 1000 won't go into the ControlSource property. I tried using the code you offered as a double-click event on the field, and I get an error that says "Ambiguous name detected: cpW". I can't just put it into the HeatCap ControlSource as = cpw([BTemp] + 273.15, [Depth] / 10) * 1000 either. It doesn't understand the cpW function and won't even let me exit the property.

I want the operation to be dynamic, just like all the others, so that if temperature or depth are changed the heat capacity is automatically recalculated, exactly as you see on the Excel spreadsheet cells. If at all possible, I don't want it to result from any event such as a click, or even an after update. I'd like to just have it as a mathematical operation written right into the ControlSource.

Do you have a suggestion as to how to integrate the ControlSource property into this? I can't even get it to work as an event, frankly. Perhaps I'm doing something wrong. I copied the entire Excel VBA, exactly as presented, into an Access module, Module2.

 

[PHV]

"Ambiguous name detected: cpW"
Seems like cpW is defined multiple times ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

You're absolutely right. It was duplicated. I had copied and pasted the VBA earlier and didn't realize it, then did it again into Module2. Thanks for pointing me in the right direction on that. It's amazing what obvious things you can overlook.

OK, it works perfectly as an event, but only the first time.

To explain, a double-click on the field provides the same answer as does Excel. However, if I change the temperature, and then go back to the HeatCap field and double-click it again for a recalculation, it fails with Run Time error 424, Object Required, and the debugger lands on this:

Private Function cpreg1(temperature, pressure)
'
' specific isobaric heat capacity in region 1
' cpreg1 in kJ/(kg K)
' temperature in K
' pressure in bar
'
tau = 1386# / temperature
[highlight #FCE94F]pi = 0.1 * pressure / 16.53[/highlight]
cpreg1 = -0.001 * rgas_water * tau ^ 2 * gammatautaureg1(tau, pi)
End Function

The really odd thing is that if I change the depth it doesn't miss a beat. I can change it and double-click again and it recalculates as often as I do it. It only

I can see it's almost there, but the main issue is that I don't want it event related. I want to somehow have it in the ControlSource, just as you see in the Excel spreadsheet, so that all results and recalculations are dynamic.

I really appreciate your help up to this point, and I know it's within reach now.

 

[Andrzejek]

So you have this function cpreg1 to which you pass temperature and pressure.
Where do you call this function from?

I assume you have 2 text boxes: txtTemperature and txtPressure and in their _Change events you call your function cpreg1. So every time you change either temperature and/or pressure, your function would fire and recalculate.

BTW, I hope you have [tt]Option Explicit[/tt] at the top of your code...



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PHV]

BTW, I have a Pi() function
So, you cant't use a variable named pi.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

BTW, retry the ControlSource way instead of event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroScope]

Thanks, gents. I got it working.

It was very strange. I kept getting the errors I mentioned, Runtime 424, along with the code failure I highlighted. I put the code into the After Update events of the two fields in question, and it still didn't work.

I decided (for no particular reason) to create a new database with only one form that had only those three fields on it. I did the same thing, attaching the AfterUpdate of the two active fields to the code, and it worked perfectly. Then I started importing items from my original database to the test database one by one to see if I could identify why it was failing. I had several times back and forth with it working one minute then not, but I couldn't identify what was causing the failure.

Then all of a sudden it started working in the test db, with all elements there. Don't know what happened, frankly, but all I did at that point was to rename the test db as my real db and leave it at that. It's now fully functional, and that's all that matters.

Thanks to all. PHV, your code worked right the first time. There was something on my side that was interfering with it and causing the failures I was reporting.

I wish I could buy you a beer!

The result varies wildly with changes in input, and having this as a dynamic calculation is going to be a huge help in the work I'm doing.

Thanks again.

 

[MacroScope]

I'm very happy to have this going, and now have a follow-up question.

If you look at the code for the function we just got working, it starts with this:

Public Function cpW(temperature, pressure)

Now the density function starts this way:

[highlight #FCE94F]Public Function densW(temperature, pressure)[/highlight]

If I put the identical expression into the immediate window as ? densW([BTemp] + 273.15, [Depth] / 10) * 1000 and it errors with "Compile error: Function or Sub not defined", highlighted as above.

While it's not as critical to me, I'd like to have full use of all of the functions this amazing tool provides, as needed, and it appears that all I have to do is change the function I'm calling, yet for some reason it doesn't work. Since you already have the code can you see whether it works for you?

It appears that energyW(temperature, pressure), entropyW(temperature, pressure), enthalpyW(temperature, pressure), and others work exactly as I expected, but the densW won't respond for me.

As always, help would be appreciated.

 

[MacroScope]

Two other things, PHV. My Pi() function does not seem to interfere at all with the code. Also, I did retry the ControlSource way, and it works perfectly. That is much cleaner. Thanks.

 

[MacroScope]

Never mind about the densW question. A line that was supposed to be commented out was not. All is well.

 

[MacroScope]

BTW, I have a Pi() function
So, you can't use a variable named pi.

Right you are, PHV. It didn't fail until I ran into a function that had the variable named pi included within it.

I just renamed my Pi() function to Pii(). Problem solved, but you pointing it out helped to identify what was going on.

Thanks again.

 

[SkipVought]

Hmmmmmm???

Errors, lost time, inefficient code...

Sounds to me like increasing entropy in the universe!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MacroScope]

Well, this is an awesome thermodynamic calculator. Being able to integrate it into an Access form will save me tons and tons of time. I've already invested a whole lot in the other calculations it does, and I don't mind investing more to get exactly what I need. A year or two from now this will be forgotten, but I'll still have a great tool.

 

[MacroScope]

Hey, PHV, you don't live in the Southern California area, do you? I really feel like I owe you a beer!

I know that oftentimes it doesn't seem like much to you, but the help you provided makes a very big difference to me.