To Code or to Query that is the question

On a simple form (mine are never simple) describing a picture frame and calculating and displaying it's cost, is the convention too:

Multiply the length of material required x the cost per meter in a form's on change event and have the value entered into a bound total control or make the forms underlying query do it.

I.e.
Private Sub Form_AfterUpdate()
Total = ((2* Height) + (2 * Width)) * Cost
End Sub

Or would you put the same thing in the underlying query?
Total:((2* [Height]) + (2 * [Width])) * [Cost]
Derek BrownMDAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
You generally should avoid storing calculations in your tables.

I would use an *unbound* textbox to calculate/display this on the fly as needed.

The control source would be, including the = sign:

= ((2* [Height]) + (2 * [Width])) * [Cost]
0
PatHartmanCommented:
I prefer to do simple calculations like this in the query.  I can then bind the result field to a control.  Since the control is not updateable, I suggest you set its tab stop to No so people tab over it when tabbing through the controls.

I like this method because I don't need to put the calculation in three places (or call it from three places).  Also when you have to put the calculation in the "a" control and the "b" control, you have to account for null values since you can't depend on "a" being entered before "b" in 100% of the cases since you are not controlling how the user moves through the form fields, nor should you.  What's the third place you say?  Why it is the form's Current Event.  If you don't call the calc from there, you won't see it on currently existing records.
0
Derek BrownMDAuthor Commented:
I love the query, personally. The results are instantaneous and always correct. The reason I ask the question really is that as I am describing something remarkably complicated in the form I am running out of query columns. Rapidly approaching 255. So either I split some off into sub forms (which always gives me grief) or I code it. Just looking for the easiest option I guess. Your comments have been very useful in simplifying a muddled head. If there are no other comments I will end the question here.

If you have anything further please comment.

derek
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mbizupCommented:
That's actually one of the reasons I prefer using control sources they respond immediately to changes in data entry, and they don't add 'width' to your queries.  As with the query approach, the control source expression is only needed in that one place.

Regarding null handling - a very good point made by PatHarman -  this is one method:


= ((2* NZ([Height],0)) + (2 * NZ([Width], 0))) * NZ([Cost],0)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
I like the query method because I reuse queries and so I also get to reuse calculations.  Another advantage of the query method is you can sort on the result so if you wanted to see items descending by total, you can do that and control the report sequence.  

If you are going to do the calculations in the form itself, I agree that the ControlSource is the most effective method.
0
Jeffrey CoachmanMIS LiasonCommented:
@DatabaseDek
<Rapidly approaching 255.>
Just curious, why so many fields?
0
IrogSintaCommented:
Just curious, why so many fields?
I'm curious about that too.  If you're creating a large number of calculated fields in your query just to end up with a final calculated field that makes use of  all the others, then an alternative may be to create a function (to replace all those calculations) to be used within the query.
0
Derek BrownMDAuthor Commented:
The form creates the report in Drawing.

The section headed Vision Panels is actually a subform split off to allow me to have more fields available on the main form.

But immediately that I do that because I have the Main project form at the top then the details subform underneath then the Vision Panel section as a sub sub form designed to just look like it's part of the main form, already I have problems! If a user tries to enter info into the Vision panel sub sub form before entering data in the details subform I get an error caused by no record available to create a join. So now I have to find a way of saying if subform is not dirty or subform has no data don't let the idiot specify a vision panel in the vision panel sub sub form until there is a door to put it in. You don't get that sort of problem when it's one form.

Does that sound like I'm having a bit of a rant?
Capture.PNG
Capture2.PNG
0
Derek BrownMDAuthor Commented:
I guess the query also has the advantage of being used in reports. With so much going on in the form, using the same query for the report as the forms you can be sure of the result. Having to do it again to get a report would be a risk.

But I am running out of options!!

Thank you all
0
mbizupCommented:
<<
 So now I have to find a way of saying if subform is not dirty or subform has no data don't let the idiot specify a vision panel in the vision panel sub sub form until there is a door to put it in.
>>

Me.Refresh

In the Enter Event of the sub-subform control will force a save in the subform, triggering the subform's Before update event, where you hopefully have some validation checks to ensure that the user has entered enough data to save a record,
0
Derek BrownMDAuthor Commented:
No I have not used that. Sounds like a good idea though.

I simply put a message in the on enter event of the sub subform "Please specify a door before entering Vision Panel Data" and set the focus to the door height control on the subform.

I guess that will do the trick? what do you think.
0
mbizupCommented:
A message box will indicate that they should enter the data...

Me.Refresh and validation checks will beat them over the head if they don't.
0
Derek BrownMDAuthor Commented:
As all we are trying to do is see if a record has been created yet (in the main form) so that a corresponding record can be created in the subform. I actually see no reason why a user shouldn't be able to enter information in any order that they wish. For example it may be that during a telephone conversation with a client who is describing an order to him verbally in the way that the customer wants to describe it. So if the clients wants to specify the subform bit first he should be able to cope with this.

The perfect answer as far as I can see would be,:

On subform enter, check if the main form has a saved record. If not, bloody well make one. Then go back to the subform without anyone being the wiser
0
IrogSintaCommented:
One more idea to consider is to have some calculations done in the form's controls and just refer to these controls from the report.  For instance, in the ControlSource of a textbox in a report you could refer to a calculated control in a form with these:
= Forms!NameOfForm!NameOfControl
= Forms!NameOfForm!NameOfSubformControl.Form!NameOfControl
That way you still only have the calculation in one place so you don't have to go to different places to make a change.
0
PatHartmanCommented:
Your form is very busy and the only repeating group I see is for the door panels.

I would use subforms on tabs to reduce the density and I would keep the various parts of the project in different tables.  So, Frame, Doors (may be multiple), Hinges, Panels (may be multiple).  Panels is a child of Doors since it is describing holes in a door.  I don't know whether hinges belong to the frame or the door.  

To determine if a mainform record exists, use the on Dirty event of the subform.

If IsNull(Me.Parent.MyPK) then
    Me.Parent.MyPK.SetFocus
    Cancel = True
    Msgbox "Please enter xxx first.",vbOKOnly
    Exit Sub
End If

Open in new window


I know it would sometimes be nice to be accommodating and allow the user to enter data in random subforms first and you can do it but it will take coding and you will probably need to adjust your required fields definitions.  All-in-all, unless there is a strong reason, I wouldn't do it.  I do a lot of on-line ordering and the clerks control the process.
0
Derek BrownMDAuthor Commented:
Thanks Pat but I get error on entering the subform "Field cannot be updated" with this?

Private Sub Form_Dirty(Cancel As Integer)
If IsNull(Me.Parent.ItemNumber) Then
    Me.Parent.ItemNumber.SetFocus
    Cancel = True
    MsgBox "Please enter xxx first.", vbOKOnly
    Exit Sub
End If
End Sub

Am I doing something wrong?

Derek
0
Derek BrownMDAuthor Commented:
I think I have enough from you all to sort this now.

On Pat's comment about less clutter with tabs: I do understand that developers want clean looking applications. But when a user, as in this case, gets a query from a customer, they want to see as much info as possible without having to check 3 or 4 tabs or popups to see what the product is. I could have a tab for glass panels in the door, a tab for the frame, a tab for the doors, a tab for ironmongery but I would need to check all of them before I knew what I was discussing. Your eyes move much quicker and with less effort than the mouse. Whilst the uninitiated see this mass of controls as busy, users don't. Well, after the initial shock and horror that is.

I have been developing now since access 97 came out, I would just say don't restrict yourself to a standard view that users cannot handle busy forms. They can and they prefer it. After all we are basically lazy and easy generally works best in a productive environment.

I will admit that my product goes to well paid and relatively intelligent users, so I cannot comment on less trained or inexperienced users.
0
Derek BrownMDAuthor Commented:
Thank you all!!
0
PatHartmanCommented:
Thanks Pat but I get error on entering the subform "Field cannot be updated" with this?
Do you have code that is populating fields on the subform?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.