Full Version: continuous form needs nested If calcs - do in query or form?
UtterAccess Forums > Microsoft® Access > Access Forms
I need to make an order entry form that calculates the price of an item based on 4 variables. The price calc needs to have several If's governed by a couple of the variables. This needs to be displayed as a continuous form, and have a total in the footer of the form.
'd most like to do the calc in VB because then I can use a Select Case instead of trying to nest some IIf's, which is much easier for me to visualise and check. But I have no idea how that can be done on the fly / in the continuous form.
Also, I think maybe that if I do the calcs in the query, I'll have to requery each time I want to get the total in the form's footer to update. This is not ideal (but not a deal killer) as the person entering the order would like to see the total tally properly each time something is entered / changed. I don't really want to force a requery then as it'll throw the cursor back to the first record.
This will need to be printed, either just straight from the form, or the data will be passed to a report.
Should I do the price calc in a bound query, or in the form?
Just to add my two cents.
I would either do it one of two ways.
. Put your code on the form in an afterupdate event.
2. Or create a module with the necessary arguments. Then use the module in the query.
Keep me posted on how it is going.
R. Hicks
I have a tendency to perform these calculation in the form or report .. as most of the time calculating them in the query will kill the speed of the query and the time it takes to display the form or report ...
Hi each,
Thanks for the feedback. In the interim I had been experimenting with laying it all out. Considering I can visualise the calculations as a spreadsheet (and to check my math) I constructed it with the calcs being done in columns in a query.
This app is only destined to have a short lifespan, and probably won't have more than a couple of hundred rows, so hopefully doing the math in a query won't have an impact on performance.
In the end it wasn't too hard, although I still do have to incorporate a scheme that adds in a price for packaging... That'll need to have some rounding & dlookups I think (price structure varies by quantity).confused.gif
The one thing that really stopped me from doing this in a form was understanding how to place and action the calculations in VBA (and also then trying to pass them to a report!!). Especially without them just working on the active row. Something like this changes with whatever row is a I am interested in understanding how to do that, as a learning experience, as I'll most likely have to make more complex versions of this thing in the future. The dbase is attached if anyone is interested in chipping in.
Pretty much anything you can put in a query as a column expression (with a few exceptions) can be used as the controlsource of an unbound text box. So, using =nz(width,0)*nz(height,0) as the control source will produce the area of a text box named txtArea (or whatever you name it) automatically whenever either width or height change. You can also include IIf's in the control source.
will add that you should not be storing the text field operator type from your lookup table in your main table. You should be storing the autonumber PK and getting the text value associated with that PK via the table relationships.
I had considered doing the column-style calcs in the control sources of unbound text boxes, as this appeared to me to be the same net result as doing them in a query (performance issues aside). But I don't know how to send the info from a form to a report, so figured I'd have to recreate the same types of calcs in the report. More importantly, if I try to make a grand total in the form's footer, I can't just make a text box display txtTotalArea = Sum(txtArea), as it errors out. My understanding is that I'd need something more like txtTotalArea = Sum(width*height). Given that my row-level calcs are full of IIf's I wasn't sure how to make totals in the footer...
Better yet, do you know how I would go about making the calcs using form-level modules? My difficulty here is knowing how to make a value calculate for each row in turn (per my previous post). I'd like to do that so that it's:
a) easier to write the structure of the calc, and so that
b) when I make an mde for the customer they can't see the calculations (and the margin we're charging them!).
Is it recommend practice to use Nz when calculating? So far my fairly basic calcs don't seem to have hiccupped with a null. Yet!
Ostill have some tidyup to do, including breaking the lookups that I originally built directly into the tables (makes it easier to populate form controls!). Storing that "operator type" instead of it's PK is easier for me to see as I'm putting all this together, I try to remove those later, but sometimes miss one!
If you already have text boxes calculating your formula on a form, just copy the text box from the form and paste it onto your report. You are correct on totals on forms. You do have to use Sum(YourFormulaHere), but, if the formula works in the detail section, it should work fine by dropping it into Sum(x), replacing the x.
If there is a chance the value could be null, then you should use Nz.
It may be easier to see the operator type, but, if you miss it when tidying up, you're doing yourself absolutely no good. Text indexes are very, very, very slow. Better to start off right and test it with just a few choices that you can follow easily than creating more work by having to go back and change things around, at least IMO.
Yep, I just tried calculating a sum in the footer, and it worked fine by including all the IIf's. So you'd just copy & paste those controls into the report, instead of trying to pass the data over from the form?
On that case I think I'll re-do this so that my calcs are in the form / report so that there is no access to them from the mde.
Do you have an idea of how the calcs could be done in form-level modules?
Yes, I would set up the same controls on the report. Easier than trying to pass the data, I would think.
It would be tricky because you'd have to have each control recalculated every time a value that affects that control is changed. Not worth it in my opinion when you can just use the formula directly in the unbound controls.
Very good, thanks. I was interested in doing the calcs in VBA more as a learning experience, given that I can manage this current one just with controlsource calcs. It occurred to me that at some point in the future I may run into a situation where VBA is req'd instead. Probably harder with a continuous form as well...
BA calcs anyone? One way: In order to get the result to calculate in VBA, I can make a button to run the calculation,
But this (don't laugh now) stores the result. Which we all know I shouldn't be doing. Can I get this type of thing to run automatically for each row, or is that intrinsically impossible for a continuous form? (Given that it's really the same form repeated endlessly...).
ps: This is my first post since Christmas. Happy Holidays everyone at UA! frown.gif
Is you suggested in your other thread this is a case where a function would fit the bill. If you just want to use it in a form then put the function in the form's module and declare it Private. If you want to expose it throughout the database so you can use it anywhere, e.g. in a report, put it in a standard module and declare it Public.
Functions work by taking values passed into them as arguments and returning a value, so in your simple example you'd pass the width and height values into the function and return the area as the function's return value:
Private Function Area(varWidth as Variant, varHeight As Variant) As Double
    ' note that the arguments are declared as variants
    ' so as to be able to accept Nulls
    ' first check neither argument is Null.
    ' this works with a single IsNull function
    ' call because Nulls propagate,
    ' so Null + anything = Null.
    ' this is because Null is not a
    ' value, but the absence of a
    ' value, an unknown, so
    ' unknown + anything obviously
    ' must also be unknown.
    If  Not IsNull(varWidth + varHeight) Then
        ' do calculation to get return value of function
        Area = varWidth * varHeight
        ' set return value to zero.
        ' this is actually unnecessary because
        ' the default value of a function
        ' declared as any numeric data
        ' type is zero.
        Area = 0
    End If
End Function

You can now call this in an unbound control in the form by entering the following as its ControlSource property:
=Area([Width], [Height])
This will work in continuous or single form view because the function is pulling the value in, rather than have code push it in. The above example is trivial, so a function would not be used in reality of course, just an expression as the ControlSource would be enough. In cases where the expression is a complex one, however, this can be broken down in a function into a logical sequence, which will be a lot easier to write and read than a complex expression, and can be commented. It also enables you to debug the code using the built in debugging facilities if the wrong results are being produced.
Don't think of functions as always returning values; they can also be used to perform actions. You can for instance use a function as the On Click event property of a button, e.g. with this function:
Function HelloWorld()
   Const MY_MESSAGE = "Hello World"
    MsgBox MY_MESSAGE, vbInformation, "Greetings"
End Function

For the On Click event property enter:
in the button's properties sheet. When the button is clicked the message box will pop up.
Hi Ken
started a new dbase to test the above code (I don't know if you were just demonstrating with "air-code", or if it was debugged). Anyway, I ran into a problem getting the function to work. (See attached).
First up I wondered how, if each field is called Width & Height, how the function would know what to do with them as there wasn't a reference to those names in the function (except with varWidth & varHeight).
Also, check out the wacky values calculated by my simple control with =[width]*[height]. I had no idea 5x2=13,680!!
I should have said this before, but the problem is due to the use of Height and Width as control names. These are also names of properties of the form, so its using those values not the 5x2. For this reason one should avoid using the names of built in properties or functions for object names. The two common ones are Date and Name, which people use as field names and then get a situation where every Name on a report is the name of the report itself rather than the values from the Name field. The best thing is to use names like CustomerName, TransactionDate or, in your case, BoxHeight, BoxWidth for example.
The other problem is that you have declared the function as Private in a standard module. That makes it visible only to that module. You should either declare in Public in a standard module, which exposes it to the whole database, or Private in the form's module, which exposes it to the form only. With private functions you can have different functions in different modules, all having the same name, and exposed only to each one's own module.
The way the function knows which value is which is simply the order in which they are passed to it. This follows the order of the arguments in the function declaration. It is possible to identify the arguments by name when you pass values into a function, The := assignment operator is used for this , but usually one relies on the order. Its more common to use the assignment operator where there are optional arguments, e.g. when you open a form at a specific record you can use:
DoCmd.OpenForm, "MyForm", WhereCondition:="MyID = 42"
rather than putting in a load of commas for the missing optional arguments.
The reason for the very high result value is that Access uses the Twip as its unit for values for things like height, width, left, top. A Twip is a twentieth of a point. A point is a typographic unit of 1/72 inch. So there are 1440 twips to an inch, which is easy enough to remember, but for those of us who work in metric units 56.692913385826771653543307086614 twips per mm, doesn't stick in the mind quite so easily. The answer of course is to write a mmToTwips function, so we can enter values in mm.
Awesome, thanks so much! I've read so many posts where people use Reserved Words for their field names, and I've always thought to myself "hmm, better not do that!". Doh! I immediately changed them to BoxWidth, etc.. and everything worked fine. I just did a quick search in UA as I'd seen one thread where someone posted a link to a huge list of reserved Words. Couldn't find it just now, but I must have the bookmark at work..
Is for the Private thing, I pasted your code verbatim, but into a general module, not into the form's module. Doh, again! I should have seen that right off the bat. Once I made it Public, the form found it just fine.
It was interesting to see the difference in refresh speed between the two calculations. The form-level one filled immediately upon opening the form, but the function call took a good half second to present just 3 lines of data.
The absolute best thing in your post was I never knew it was just that, but seems so simple & obvious now! To experienced programmers it's probably so fundamental it doesn't even feature in a how-to, but makes all the difference to a rookie like me.
You have the variables are declared in the name of the function, as in:
instead of separate lines like:Does this make a difference?
Thanks again, and enjoy your New Year's Eve o!
The declaration line of a function is always one line, but you can spread it over several lines for ease of reading by using the underscore continuation character. Here's an example from one of my own which does a Word merge from within Access by means of automation:
Public Function WordMergeAndSave(strQuery As String, _
                        strDataDoc As String, _
                        strMergeFile As String, _
                        strSaveAs As String, _
                        Optional blnSuppressBlankLines As Boolean = True)

I'm signing off for 2005 now. A happy new year to you and any lurkers. tongue.gif
Ken -
xcellent explanation. Just wanted to point out that the following code should also work, and should be a bit more efficient:
Private Function Area(varWidth as Variant, varHeight As Variant) As Double
  Area = NZ(varWidth * varHeight)
End Function

Since anything * a Null will also result in a Null.
Also wanted to point out that your HelloWorld function IS actually returning a value (all functions do) -- in this case, an empty string (""), but it is being discarded by the calling statement.
nother point worth making is that in some cases one would have a function both undertake a task and return a value, usually to indicate whether the task has been successfully executed. This one from Access help is an example, returning True if the property of the object is set successfully, False otherwise:
Function AccessPropertySet(obj As Object, strName As String, _
        intType As Integer, varSetting As Variant) As Boolean
    ' this function is originally from Access 97 Help
    Dim prp As DAO.Property
    Const conPropNotFound As Integer = 3270
    ' attempt to set property's Value
    On Error GoTo ErrorAccessPropertySet
    obj.Properties(strName) = varSetting
    ' refresh object's properties collection
    AccessPropertySet = True
    Exit Function
    If Err = conPropNotFound Then
        ' if property doesn't exist create it and append to
        ' object's Properties collection
        Set prp = obj.CreateProperty(strName, intType, varSetting)
        obj.Properties.Append prp
        AccessPropertySet = True
        Resume ExitAccessPropertySet
        AccessPropertySet = False
        Resume ExitAccessPropertySet
    End If
End Function
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.