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]
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]
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.
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.
ASKER
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
If you have anything further please comment.
derek
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If you are going to do the calculations in the form itself, I agree that the ControlSource is the most effective method.
@DatabaseDek
<Rapidly approaching 255.>
Just curious, why so many fields?
<Rapidly approaching 255.>
Just curious, why so many fields?
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.
ASKER
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
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
ASKER
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
But I am running out of options!!
Thank you all
<<
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,
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,
ASKER
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.
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.
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.
Me.Refresh and validation checks will beat them over the head if they don't.
ASKER
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
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
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!NameOfCon
= Forms!NameOfForm!NameOfSub
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.ItemNumbe
Me.Parent.ItemNumber.SetFo
Cancel = True
MsgBox "Please enter xxx first.", vbOKOnly
Exit Sub
End If
End Sub
Am I doing something wrong?
Derek
Private Sub Form_Dirty(Cancel As Integer)
If IsNull(Me.Parent.ItemNumbe
Me.Parent.ItemNumber.SetFo
Cancel = True
MsgBox "Please enter xxx first.", vbOKOnly
Exit Sub
End If
End Sub
Am I doing something wrong?
Derek
ASKER
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.
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.
ASKER
Thank you all!!
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?
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]