Hi,

I'm having a bit of an issue with TextBoxes that use a Function as a Control. What happens is that if I want to get the value of the textbox it will run the function, not just give me the value. Which is fine if you don't do a lot of stuff. But my function does a few things and get called quite a few times, and calling it twice seems a bit excessive. Is there a way to not do this but instead just read the value of the bound textbox?


What I'm trying to do is make a table and then sum the columns of the table. Each table is a row, and 8 fields in the row call a function that calculates a number of exception with various criteria, and then returns that number.

To sum I have a couple of variables that add the value of each field of each row as they are created (Detail_Print)

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
MaintenanceSum = MaintenanceSum + Val(Me.MaintenanceNew)
SevereSum = SevereSum + Val(Me.SevereNew)
ImmediateSum = ImmediateSum + Val(Me.ImmediateNew)
TotalSum = TotalSum + Val(Me.TotalNew)

MaintenanceSumOld = MaintenanceSumOld + Val(Me.MaintenanceOld)
SevereSumOld = SevereSumOld + Val(Me.SevereOld)
ImmediateSumOld = ImmediateSumOld + Val(Me.ImmediateOld)
TotalSumOld = TotalSumOld + Val(Me.TotalOld)
End Sub


But each time it does this it runs the Function that's in the SQL query.
The query looks something like this (but a bit longer).

SELECT Exception, MyFunction(aaaa) as A, MyFunction(bbbb) as B From ExceptionsTable

If I only run the query it works fine, just when I try to set a value to be the same value as my bound textbox it runs it twice.

Is ther away around this?

Thanks,
Mattias

this isnt correct

if you set a textbox value, either by making it bound, or by setting its value in code, the textbox then "has" a value.

if you then dereferecne this value

myvariable = mytextbox

or even

if mytextbox = "Check This"

this does not re-execute the underlying code - put a msgbox in the underlying code and you will see when it gets called

That's what I thought it would do as well, but by some reason it runs the function. I stepped through it and could see that it went into the function. I also put a count variable in the function and printed it to the debug window. And when I didn't reference the textbox, it entered the function a lot less, then when I referenced the textbox again it entered the function a lot more.

A bit strange I think

is it a global function called in the forms query record source - that might rerun in some cases

Ah, yes, that it is. I make the query in the OnLoad event, and then set the recordsource = theQuery

Think that is the only way I can do it?

Thanks,
Mattias